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.
- Mode Selector – see the list of properties & their values in a categorized form, or as a alphabetical list
- Property Name & Property Values columns
- 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).
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:
- CPU cost
- I/O cost
- Number of executions
- Number of rows
- Operator cost
- Row Size
- Sub-tree cost
- Forced Index usage
- Logical Operation
- Node Id
- Index Kind
- Output column
- Parallel execution
- 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.