Problem
I use the SQL Server dynamic management function sys.dm_exec_query_plan to retrieve the query plan from batch and stored procedure execution. However, at times the query plan returns NULL even though I am checking an executing query.
Why does this happen and how do I get the query plan from an executing query in this scenario?
Solution
The dynamic management function (DMF) sys.dm_exec_query_plan returns the query plan for a batch or stored procedure execution either in entirely or nothing. This means if there are any individual statements in the batch or stored procedure that is not compiled by SQL Server, then sys.dm_exec_query_plan will be unable to return the query plan for the entire batch and hence NULL for the query_plan column.
One of the few workarounds is to utilize sys.dm_exec_text_query_plan to retrieve the query plan of the executing query.
Demonstrating NULL Query Plan Using sys.dm_exec_query_plan
To demonstrate a NULL query plan, we need to make sure that SQL Server skips the compilation of at least one statement when executing a batch script or stored procedure which has multiple statements.
To simulate this condition, we will write a conditional expression to skip execution of one statement and append an OPTION RECOMPILE to the statement as shown below. The loop in the script is to ensure we can check the query plan at any time after its execution is started. Otherwise a single execution of the script would be too quick to catch.
SET NOCOUNT ON;
CREATE TABLE #temp1 (col1 int, col2 varchar(10));
INSERT INTO #temp1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
DECLARE @bit BIT = 1, @count1 INT, @count2 INT;
WHILE 1 = 1
BEGIN
SELECT @count1 = COUNT(*) FROM #temp1;
IF @bit = 0
BEGIN
/* -- SQL Server will always skip compilation of the statement below --*/
SELECT @count2 = COUNT(*) FROM #temp1 OPTION (RECOMPILE);
END
END
GO
There are two key things to note in the batch script above
- As a general fact, obviously statements with OPTION RECOMPILE will always get recompiled by SQL Server when executed.
- Reflecting on point 1 above, SQL Server will not compile such statements when it is not executed. The crux is, why would SQL Server waste CPU resource to compile a query which is sure to be compiled (if it does get executed), but does not get executed? Hence, the second SELECT statement in the batch script will never be compiled by SQL Server.
Retrieving query plan using sys.dm_exec_query_plan
Now we will execute the batch script with the conditional expression. We will launch another query window and use sys.dm_exec_query_plan to retrieve the query plan. As stated, sys.dm_exec_query_plan will either return the query plan entirely, or it will return NULL if there is any statement not compiled in the batch.
In this scenario, as expected the query plan shows NULL as the second SELECT statement is not compiled by SQL Server.
SELECT
s.program_name, r.session_id, t.text, q.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) q
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
s.is_user_process = 1
AND r.session_id <> @@SPID
Retrieving query plan using sys.dm_exec_text_query_plan
SQL Server has another DMF sys.dm_exec_text_query_plan which returns the query plan in text format for a T-SQL batch or for an individual statement within the batch. In this scenario, we will want to retrieve the query plan of the executing individual statement.
Since the output of the query plan is in text format, we will convert it to XML data type in order to view the query plan in graphical format using SSMS.
SELECT
s.program_name, r.session_id, t.text, q.query_plan, CONVERT(XML, q.query_plan) xml_query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) q
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
s.is_user_process = 1
AND r.session_id <> @@SPID
The query plan returned here is only the first SELECT statement shown below. Alternatively, using Activity Monitor in SSMS you can also retrieve the query plan of the executing statement.
Display Full Query Plan Using sys.dm_exec_query_plan
In case you are wondering how the full query plan looks when all statements are compiled, we will make a slight tweak to the existing batch script and comment out the temp table creation, the insert statement and OPTION RECOMPILE from the second SELECT statement.
Then stop the existing batch script execution and re-execute the script in the same query window. When the second SELECT statement is encountered the first time, it will not be compiled. But because we are re-executing the script the second time, the second SELECT statement will be compiled now without the OPTION RECOMPILE query hint.
/*
SET NOCOUNT ON;
CREATE TABLE #temp1 (col1 int, col2 varchar(10));
INSERT INTO #temp1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
*/
DECLARE @bit BIT = 1, @count1 INT, @count2 INT;
WHILE 1 = 1
BEGIN
SELECT @count1 = COUNT(*) FROM #temp1;
IF @bit = 0
BEGIN
/* -- The statement below will be compiled the second time it is executed --*/
SELECT @count2 = COUNT(*) FROM #temp1 -- OPTION (RECOMPILE);
END
END
GO
Now when we execute this script which retrieves the query plan using sys.dm_exec_query_plan.
SELECT
s.program_name, r.session_id, t.text, q.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) q
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
s.is_user_process = 1
AND r.session_id <> @@SPID
This time, the DMF will return the query plan since all statements are compiled by SQL Server.
Below is how the full query plan output looks which contains the two SELECT statements.
Summary
In this tip, we learn the reason why a NULL query plan is returned from sys.dm_exec_query_plan. A workaround demonstrated in this tip is to use sys.dm_exec_text_query_plan. Both DMFs can be used to retrieve query plans for a plan residing in the plan cache or currently executing.
Next Steps
Check out these related items:
