Getting a query plan

2

February 19, 2014 by Kenneth Fisher

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 QueryPlan1
  • 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 QueryPlan2
  • 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!

2 thoughts on “Getting a query plan

  1. […] First I run the view without any changes, displaying the execution plan. […]

  2. […] This also runs SET SHOWPLAN XML on and has to be run in it’s own batch, so put a GO after […]

Leave a Reply

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

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: