Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

#0131 - SQL Server – SSMS – Tutorial – Part 06 – Introduction to the Properties Window (L100)

Feb 2 2012 12:00AM by Nakul Vachhrajani   

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:


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
    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

Viewing SET options

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


SELECT e.LoginID, e.JobTitle, e.HireDate, e.CurrentFlag
FROM HumanResources.Employee e


Modifying SET options

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

--Number of sales done in April, 2008
FROM Sales.SalesOrderHeader sh
WHERE MONTH(sh.OrderDate) = 4 AND YEAR(sh.OrderDate) = 2008

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.

Missing Index Hints

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…

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

Tags: Development, Administration, Performance Tuning, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, Guidance, DBA, SSMS, Tools and Utilities, Community, Series,

Nakul Vachhrajani
4 · 36% · 11648


Your Comment

Sign Up or Login to post a comment.

"#0131 - SQL Server – SSMS – Tutorial – Part 06 – Introduction to the Properties Window (L100)" rated 5 out of 5 by 2 readers
#0131 - SQL Server – SSMS – Tutorial – Part 06 – Introduction to the Properties Window (L100) , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]