The SQL Server Management Studio (SSMS) continues to fascinate me with it’s immense wealth of features geared towards developer & administrator usability. What’s more amazing is that some features perform multiple useful functions depending upon the context. Today, we will have a look at one such feature – the “Properties” window, which performs the following functions:
- Developer friendly features
- Knowing your query options – ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT and other SET options
- Administrator friendly features
- Performance tuning
The “Properties” window is available in SQL Server 2005 and up.
Developer friendly features – knowing query options
Often, we multi-task and forget the exact set of SET options that a particular connection uses. The most frequently used SET options that one is worried about are:
- ANSI_NULLS
- QUOTED_IDENTIFIER
- ARTIHABORT
- NUMERIC_ROUNDABORT
- ANSI_WARNINGS
The properties window provides us a way to check the various SET options used for a particular connection. Here’s a step-by-step guide:
- Let’s run the following query in SQL Server Management Studio. Include the “Actual Execution Plan” by pressing Ctrl+M before running the query.
USE AdventureWorks2008R2 GO SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag FROM HumanResources.Employee e
- Switch to the Actual Execution plan in the Results pane
- Hit F4 to invoke the Properties Window (you may also use View->Properties Window menu option)
- Select the top-most SELECT operator
- Observe the Properties Window
As a cross-check, change the SET options on the query, or use the features of SSMS to change the SET options for just this connection.
USE AdventureWorks2008R2 GO SET ANSI_NULLS OFF GO SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag FROM HumanResources.Employee e SET ANSI_NULLS ON GO
Administrator friendly features – Performance tuning guidance
Please note that performance tuning itself is out of scope for this tutorial. What is covered though, is how the Properties window can help us when performing a performance tuning exercise.
Generally, a performance bottleneck is because of issues with the application code – this includes sub-standard T-SQL code.
If the SQL optimizer feels that an index would help it generate a better execution plan for any given query, SQL Server Management Studio provides us with an indication of it’s requirement when viewing the graphical execution plan.
If we run a query in SQL Server Management Studio with the graphical plan enabled, the missing index hint would show up in green-text as high-lighted in the screen-shot below.
USE AdventureWorks2008R2 GO --Number of sales done in April, 2008 SELECT COUNT(*) FROM Sales.SalesOrderHeader sh WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008 GO SELECT COUNT(*) FROM Sales.SalesOrderHeader sh WHERE sh.OrderDate >= '2008-04-01' AND sh.OrderDate <= '2008-04-30'
Notice that the Properties page also displays the missing index information. In fact, the Properties page was the only place to get this information in SQL Server 2005.
Practice Exercises…
Explore the “Properties” window that comes up in the following cases:
- Query editor window
- Query results window
- Table designer window
Further reading…
- Property Pages in SQL Server Management Studio
- SSMS – Performance Tuning using graphical execution plans – Missing indexes hints
Until we meet next time,
Be courteous. Drive responsibly.
Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql