SSMS Properties Window – Get connection and query information with a mouse click


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

image

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:

image

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:

image

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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s