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:

1. Developer friendly features
   • Knowing your query options – ANSI_NULLS, QUOTED_IDENTIFIER, ARITHABORT and other SET options
2. 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:

1. Query editor window
2. Query results window
3. 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