THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

July, the 31 Days of SQL Server DMO’s – Day 5 (sys.dm_exec_query_plan)

 

The sys.dm_exec_query_plan Dynamic Management Function is very similar to the sys.dm_exec_sql_text function that we reviewed on yesterday, except this function is used to return execution plans. To be exact, it returns either the cached or currently executing plan in Showplan XML format. Instead of passing in a ‘sql_handle’ as we did for sys.dm_exec_sql_text function, we will pass in a ‘plan_handle’ which can also be obtained by using the sys.dm_exec_requests DMV that we reviewed on Day 1.

To illustrate how this function works, I will first need to capture a plan_handle. **NOTE: I have a SQL Statement running in a loop in a separate window against the AdventureWorks2012 database.

SELECT session_id, command, sql_handle, plan_handle FROM sys.dm_exec_requests
WHERE session_id >= 50 AND session_id != @@SPID

image

Now that I have a plan_handle to work with, I can either call the sys.dm_exec_query_plan function directly and pass in the plan_handle, or I can run a query against my sys.dm_exec_requests DMV and use a CROSS APPLY with the sys.dm_exec_query_plan function.

SELECT * FROM
sys.dm_exec_query_plan(0x06000500F96E970BB00FB9FC0200000001000000000000000000000000000000000000000000000000000000)

image

From running the above query, you will see that we get similar results as we did when using sys.dm_exec_sql_text, so I will not go into detail about what these columns represent. But the last column in our result set (query_plan) provides us with an XML link.  (Note: The query I had executing was not the most efficient query, but I wanted to provide an execution plan with several different operators listed)

If we click on the link, we will see the following:

image

As I mentioned earlier, you can also use the CROSS APPLY to use this sys.dm_exec_query_plan function against every row returned from your call to sys.dm_exec_requests:

SELECT er.session_id, er.status, er.command, er.plan_handle, qp.query_plan
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

image

This is a very fast and efficient way of finding execution plans for queries that are currently executing on your server. In a future post, we will review how to pull execution plans that may not be currently running, but exist in the cache.

For more information on this Dynamic Management Function, please see the below Books Online link:

http://msdn.microsoft.com/en-us/library/ms189747.aspx

Published Friday, July 05, 2013 10:35 AM by Tamarick Hill

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement