This week, I will go back to my trusted gateway to Microsoft SQL Server – the SQL Server Management Studio (SSMS). I will present before you two great features of SSMS which all administrators and developers would find useful.

SSMS Property Pages

When working on multi-server queries within the SQL Server Management Studio (SSMS), we often find the need to get a summary of the failed connections, number of rows returned, etc. Similarly, when studying a graphical query plan, many a times it is required to study the internals of a particular operator in greater detail.

One of the options by which we can explore further is to write a query (generally using a DMV) or using a textual execution plan. However, that does not have to be the case. Go to View –> Properties or hit F4 to launch the Properties window/page, which will help us in getting all of this information at the click of a mouse button.

The Properties Page consists of the following basic elements. This list stands true for all properties windows in SSMS.

1. Mode Selector – see the list of properties & their values in a categorized form, or as a alphabetical list
2. Property Name & Property Values columns
3. Description pane, which gives a brief description of the particular property selected by the user

In the screen-shot above, you can see the default property page that comes up for a query in the query editor.

Property Pages when studying Graphical Execution Plans

When studying Graphical Execution plans, the Property Pages are a very powerful feature. Let’s write a sample query against a test SQL Server instance. Next turn choose to display the Estimated Execution Plan (Query –> Display Estimated Execution Plan, or press Ctrl+L).

USE AdventureWorks2008R2
GO
SELECT     Sales.SalesOrderDetail.SalesOrderID, Sales.SalesOrderHeader.RevisionNumber, Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderHeader.DueDate, 
                      Sales.SalesOrderHeader.Status, Sales.SalesOrderHeader.ShipDate, Sales.SalesOrderDetail.CarrierTrackingNumber, Sales.SalesOrderHeader.CustomerID, 
                      Sales.SalesOrderHeader.SalesPersonID, Sales.SalesOrderHeader.TerritoryID, Sales.SalesOrderHeader.AccountNumber, 
                      Sales.SalesOrderHeader.PurchaseOrderNumber, Sales.SalesOrderHeader.TotalDue, Sales.SalesOrderHeader.Comment
FROM         Sales.SalesOrderDetail INNER JOIN
                      Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID INNER JOIN
                      Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND 
                      Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND 
                      Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                      Person.Person ON Sales.SalesPerson.BusinessEntityID = Person.Person.BusinessEntityID

The estimated execution plan for this query on my instance is shown below:

Now, let’s assume we want to understand more about the “Clustered Index Scan” operator applied on the index [SalesPerson].[PK_SalesPerson_BusinessEntityID]. All we need to do is to select the operator, and invoke the Properties page, which would look something like this:

For better search, here’s what you can get from the properties page:

1. CPU cost
2. I/O cost
3. Number of executions
4. Number of rows
5. Operator cost
6. Rebinds
7. Rewinds
8. Row Size
9. Sub-tree cost
10. Forced Index usage
11. Logical Operation
12. Node Id
13. Database
14. Index
15. Index Kind
16. Schema
17. Table
18. Ordered
19. Output column
20. Parallel execution
21. Table cardinality

As you can see, this land-mine of information is just a mouse click away! That too, without a single query due to the use of graphical tools available out-of-the-box with SSMS.

So, the next time you are analyzing a query, or writing one, take a look at the Properties window – you will be surprised with what you will find.

Until we meet next time,

Be courteous. Drive responsibly.