One of the most fundamental DMF’s a SQL Server DBA should know is sys.dm_exec_query_plan.

This Dynamic Management Function allows us to see the Execution Plan for queries that are either currently running or have been cached.

For example, the following query will return the plan for queries that are currently running, along with the statement text, the entire batch the statement belongs to, and some other stuff:

select
substring(text, statement_start_offset/2+1,
((case when statement_end_offset = -1
   then datalength(text)
   else statement_end_offset end - statement_start_offset)/2) + 1)
as running_statement,
text as current_batch,
p.query_plan,
r.*
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
cross apply sys.dm_exec_query_plan(plan_handle) p

Changing the query to use sys.dm_exec_query_stats instead of sys.dm_exec_requests will bring queries from cache. The returned query_plan field is the XML representation of the plan (SHOWPLAN XML), and clicking on it will open a new SSMS window with the graphical view of the Execution Plan.

The Problem

sys.dm_exec_query_plan returns information for the whole batch. This is not an issue if you deal with short batches or Stored Procedures, but if, for example, you try to optimize a query in a Stored Procedure that is built from 20 statements, it can be frustrating to find and examine the right plan.

The Solution

sys.dm_exec_query_plan has a “little sister” called sys.dm_exec_text_query_plan.

In addition to the plan_handle, it accepts a start and end offset parameters, which allow it to return an Execution Plan at the statement level instead of the batch level. The plan is returned as nvarchar(max), so you will need to cast it to XML in order to view the graphical plan in SSMS. The reason for this is that in rare cases, a plan can exceed the nest level limit of the XML data type, and the only way to view it is textual. So as long as your plan doesn’t exceed that limit, the casting should work.

Switching to use the “little sister”, the above query will look like this:

select
substring(text, statement_start_offset/2+1,
((case when statement_end_offset = -1
 then datalength(text)
 else statement_end_offset end - statement_start_offset)/2) + 1)
as running_statement,
text as current_batch,
cast(p.query_plan as xml) as query_plan,
r.*
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
cross apply sys.dm_exec_text_query_plan
      (r.plan_handle, r.statement_start_offset, r.statement_end_offset) p

Wait, There’s More

Now that we know the new function, it’s easier to do the following:

Plan Forcing With The Use Plan Query Hint

While I’m generally against using hints, there are rare occasions where we don’t have another option. One way to do it is using the Use Plan query hint. You can grab the textual representation of the XML plan and use it to tell the optimizer how to execute the query. You can also do it with a Plan Guide.

Plan Sharing

Got a query or procedure you are struggling with? Don’t understand why the optimizer is choosing a certain plan? You can save the plan to a file and send it to a friend or consultant by mail, post a screenshot on Twitter with the #sqlhelp tag, or upload it to your favorite SQL Server forum and let the community help you.

If you don’t want to reveal your object names and database structure, you can use Jonathan Kehayias’ Execution Plan Sanitizer for masking.