XML Plans Saga –Twilight

XML plans were a new addition in SQL Server 2005 and above which enabled a DBA to fetch additional information from a SQL Server query plan. In this blog post, I shall list out the useful information that can be fetched out of a query plan which can be pertinent to your performance tuning activities. I am old-skool and hence I still prefer analyzing query plans/comparing them using the text plans. However, that doesn’t mean that I ignore XML plans as they have a plethora of information which can be useful for troubleshooting a SQL Server performance problem as well as for query tuning activities.

As you have already figured out from the post title that this is the first post in the XML Plans Saga. All the information that I need to dole out as part of this series will be covered in four blog posts with a break down as shown in the bulleted list below.

You can generate XML Showplan output by using the following methods:

Selecting Display Estimated Execution Plan or Include Actual Execution Plan from the query editor toolbar in SQL Server Management Studio

Using the Transact-SQL Showplan SET statement options SHOWPLAN_XML (for estimated plan) and STATISTICS XML (actual execution plan)

Selecting the SQL Server Profiler event classes Showplan XML (for estimated plan), Showplan XML for Query Compile, and Showplan XML Statistics Profile (actual execution plan) for tracing

Using the sys.dm_exec_query_plan dynamic management function

XML Showplans are returned in the nvarchar(max) data type for all of these methods, except when you use sys.dm_exec_query_plan. XML Showplans are returned in the xml data type when you use this dynamic management view.

The XML schema for Showplan is available with the SQL Server installation files at the following location:

SQL Server 2008 and 2008 R2: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd
SQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\20047\showplan\showplanxml.xsd

For x64 platforms, the folder would be Program Files (x86) instead of Program Files.

I am going to pick a simple query to demonstrate the use of XML plans and gather information from the graphic XML plans which are obtained from management studio. The query that I will be using is:

select top 100 chardata
from dbo.tblTechEd1
where id = 1

Now the above table doesn’t have a clustered index. The table size is approximately 781MB with 100K rows.

Enter… Estimated Execution Plan from Management Studio

The button (see red square in Screenshot 1) on the toolbar in Management Studio is the “Display Actual Execution Plan” option which allows you to show estimated execution plan. There are three important things that you can ascertain right off the bat using the execution plan tab (which are denoted by the red arrows in Screenshot 1):

1. The query cost which is shown relative to the batch. If there were multiple T-SQL statements in my query batch, then instead of 100% which is shown in Screenshot 1 you would have seen a percentage value relative to the total batch cost.
2. The second line shows the actual SQL query for which the estimated plan is being displayed. If the query is lengthy or epic in nature, then you will only see a substring of the query and a mouse over on the query will provide the query text.
4. This is a bonus! In case a missing index was identified for the query in question, then it will be displayed as green text in the third line. For the example that I am using, a missing index was reported which will have a 99.9546% positive impact on the query if created.

If you right-click on the query plan, you will be able to use the Missing Index Details (see Screenshot 2) option to generate the CREATE INDEX command in a new query window. The text will show up as follows:

/*
The Query Processor estimates that implementing the following index could improve the query cost by 99.9546%.
*/

/*
USE [dbTechEd_1]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblTechEd1] ([id])

GO
*/

By clicking on each of the operators, you can see the properties of the query plan operator which is highlight. If you look into Screenshot 3, you will see that the Table Scan operator properties are being displayed where in you can determine the Estimated Number of Rows that will be returned along with the Estimated I/O and CPU cost of the query.

If you want additional information, then you can use the Properties Window in the Query Window in Management Studio which will give you additional information about each operator which is not available with the mouse over option on each of the operators. (F4 key is the default function key to bring up the properties window.)

If you look at the properties window (Screenshot 4), you will see that most of the information that was reported in the mouse over properties in the Execution Plan tab are shown along with additional information like the Estimated Rebinds and Rewinds, an explicit mention of this being a parallel operation and the Table Cardinality.

Enter Stage… Actual Execution Plan from Management Studio

If you now look at Screenshot 5, you will see that the black square denotes the “Include Actual Execution Plan” option. Once the Query Execution is complete, the execution plan will appear in the Execution Plan tab. The above points mentioned about the Estimated Plan shown in Management Studio in the graphical format still applies to the actual execution plan. However the big difference in the execution plan is the Actuals (Rebinds, Rewinds, Number of Rows) which are reported along with the Estimates. See the properties window and the mouse over operator properties in Screenshot 5. If there is a big mismatch between the actual number of rows & executions as compared to the estimates, then there is a change that the cardinality estimates were wrong due to outdated statistics or lack of the same!