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
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
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:
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
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: