Query plans are an essential tool when doing performance tuning. When looking at a query plan you should be aware that there are two different types of query plans. There are Estimated and Actual query plans (also called Execution Plans). Estimated and Actual query plans have the following differences:

An Estimated Query (Execution) Plan comes from a batch that has not actually been executed and an Actual Query (Execution) Plan comes from a batch that has been executed.

An Estimated Query (Execution) Plan contains only estimated counts (from the statistics) and an Actual Query (Execution) Plan contains both the estimated counts and the actual counts (from the execution itself).

Estimated Execution Plan

There are of course several ways to retrieve an estimated query plan. In SSMS we can use the “Display Estimated Execution Plan” option to display the estimated execution plan of our currently selected query or batch by doing one of the following.

• Selecting it in the toolbar
• The menu option Query-> Display Estimated Execution Plan
• Ctrl+L

Any of these options will immediately cause the estimated plan to be generated and displayed. Some other methods of getting an estimated plan worth looking at include:

SET SHOWPLAN_ALL
SET SHOWPLAN_XML
SET SHOWPLAN_TEXT

Once one of these options is turned on then any T-SQL run on the connection will not actually be executed but the query plan in various formats will be displayed. An interesting effect of this is that you can only turn one on at a time. When you try to execute the second SET SHOWPLAN command it just gives you the execution plan for it. SHOWPLAN_XML causes the XML for the graphical plan to be displayed. SHOWPLAN_TEXT and SHOWPLAN_ALL cause a text version of the plan to be displayed. This text version can be more useful than the XML format when using a text only interface such as SQLCMD. In fact some of my co-workers with a lot of DB2 for zOS experience find the output of SHOWPLAN_TEXT and SHOWPLAN_ALL to be easier to read than the graphical output.

Actual Execution Plan

Of course all of the above options only display the estimated execution plan and frequently we want the actual execution plan. If we are using SSMS we can turn on the “Include Actual Execution Plan” option by doing one of the following:

• Selecting it in the toolbar
• The menu option Query-> Include Actual Execution Plan
• Ctrl+M

Once the “Include Actual Execution Plan” option is turned on the query will have to be executed in order to get the plan. Now if we want to show an actual execution plan for a batch that has already been executed or is currently being executed (estimated only since actual numbers aren’t available yet) we can turn to DMOs. The DMO sys.dm_exec_query_plan takes the plan_handle from one of the following DMOs and returns the xml plan.

sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_requests
sys.dm_exec_procedure_stats

This does of course require that the plan be still in the cache.

Everything so far displays the query plan for a batch. With a large batch (say a particularly large stored procedure) sometimes it’s handy to get the plan for an individual query from a batch. This brings us to one of my favorite DMO’s sys.dm_exec_text_query_plan. This particular DMO has several differences from sys.dm_exec_query_plan (list in BOL) but the one in particular that I want to discuss here is the fact that it has 2 extra parameters. When statement_start_offset and statement_end_offset are passed in along with the plan_handle they return back just the portion of the plan for that section of the batch. This is particularly helpful if you are using sys.dm_exec_query_stats when performance tuning. The combination will let you look at the individual plans for each of the queries listed along with a number of helpful performance statistics (CPU time, execution time, reads, writes and CLR time). One important note is that you should convert the column query_plan to XML so that you can click on it in the results pane to open the graphical view of the plan.

SELECT CAST(query_plan AS XML) AS XML_Plan, *
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, statement_start_offset, statement_end_offset)

Not all entries display a plan and you can see the possible reasons in BOL under the remarks section for sys.dm_exec_text_query_plan.

Once you have found the query plan it helps to understand what you are looking at. That is a big study and one I’m only really beginning at. I highly recommend getting a copy of the book SQL Server Execution Plans, Section Edition by Grant Fritchey. It even has a pdf download for free!

Filed under: DMV, Microsoft SQL Server, Query Plans, SQLServerPedia Syndication, System Functions and Stored Procedures Tagged: DMV, execution plan, Grant Fritchey, microsoft sql server, query plan