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 299
SQL Server 298
Administration 251
DBA 240
T-SQL 230
#TSQL 228
Development 223
Tips 211
Guidance 144
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:

  • 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

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
GO

SET ANSI_NULLS OFF
GO

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

SET ANSI_NULLS ON
GO

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

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% · 11521
2
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

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]