Why am I getting NULL values for query_plan from sys.dm_exec_query_plan?

[アーティクル]
07/13/2016

Recently we got a customer who called in and wanted to know why he received NULL for query_plan when querying sys.dm_exec_query_plan. This customer referenced a blog from https://dzone.com/articles/dmexecqueryplan-returning-null. In that scenario, you will get NULL for query_plan if there are any statements involving temp tables that have not been executed for the first time. For reference, I pasted the query below.

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text,
qp.query_plan, tqp.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
CROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqp
WHERE text LIKE '%ConditionalPlanTest%'
AND objtype = 'Proc'

After putting on debugger and stepping into the source code, I was able to find out more about this topic. In order to understand the issue, we need to know a couple of key concepts.

Deferred compilation

When you first execute a batch or stored procedure which may contain multiple statements. Not every statement will be compiled when you first use the procedure. If there are some code paths that result in the statement not being executed, SQL Server may choose not to compile that statement in a small set of scenarios. In other words, some statements will only be compiled upon first execution. So far, I have found the following two scenarios that a statement will not be compiled (deferred compilation) if code path result in that statement being skipped.

Statement involving temp table. Data could have been changed when the statement is actually executed. So it doesn’t make sense to compile right off the beginning.
Statement that has option (recompile) attached to it We need to recompile that statement every time anyway. If we don’t use, it why compile in advance?

Do you want the plan for whole batch or just that statement?

sys.dm_exec_query_plan gives you the xml plan for the entire batch (or procedure) while sys.dm_exec_text_query_plan allows you to retrieve a query plan for an individual statement by specifying statement_start_offset and statement_end_offset. Even sys.dm_exec_text_query_plan will return plan for the entire batch if you don’t specify proper offsets.

Why NULL values?

When you specify that you want the query plan for entire batch (or procedure), SQL Server will attempt to retrieve every plan for every statement inside that batch or procedure. if query plan is missing for any statement, then NULL will be returned. Otherwise, the plan won’t be a complete. Note that the above query retrieves the plan from sys.dm_exec_query_plan which means the whole plan for the batch needs to be returned. That is why NULL value is returned.

How can you get statement plans from a batch when not every statements has been compiled yet?

You can modify the query under “Providing batch-execution statistics” from sys.dm_exec_sql_text knowing that you may miss some statements involving temp table and option (recompile). Here is an example query. Note that it’s critical you specify the statement statement_start_offset and statement_end_offset.

SELECT s2.dbid,
    s1.sql_handle,
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
         ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
    execution_count,
    plan_generation_num,
    last_execution_time,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes    ,
                cast(s3.query_plan as xml) query_plan
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'ConditionalPlanTest'
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

Demo

use tempdb
go

create table t1 (c1 int)
go
create table t2 (c1 int)
go

create procedure p_test @option int
as
if @option >= 2
select * from t1 option (recompile)
if @option >=1
select * from t2 option (recompile)

go
--this will only execute 2nd statement and skip first statement
p_test 1
go

--NULL plan will be returned because the query wants whole batch plan
SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text,
s3.query_plan AS text_query_plan
FROM sys.dm_exec_cached_plans s1
CROSS APPLY sys.dm_exec_sql_text(plan_handle) s2
CROSS APPLY sys.dm_exec_query_plan(plan_handle) s3
WHERE object_name ( s2.objectid, s2.dbid) = 'p_test'

--this will return the plan for 2nd statement that was executed
--but it will not return plan for 1st statement because the query was never executed and compilation was deferred
SELECT s2.dbid,
    s1.sql_handle,
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
      ( (CASE WHEN statement_end_offset = -1
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
         ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
         cast(s3.query_plan as xml) query_plan,
    execution_count,
    plan_generation_num,
    last_execution_time,
    total_worker_time,
    last_worker_time,
    min_worker_time,
    max_worker_time,
    total_physical_reads,
    last_physical_reads,
    min_physical_reads,
    max_physical_reads,
    total_logical_writes,
    last_logical_writes,
    min_logical_writes,
    max_logical_writes

FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
cross apply sys.dm_exec_text_query_plan (plan_handle, statement_start_offset, statement_end_offset) s3
where object_name ( s2.objectid, s2.dbid) = 'p_test'
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

Anonymous
July 13, 2016
Another cause of a NULL query_plan, one I had to contend with in a previous job, is a proc created using the WITH ENCRYPTION option.
Anonymous
July 18, 2016
Couldn't you do something to replace this monster with something usable? We're living with this for years, but it's a PITA. Not to mention casual users/admins. How can they remember this? Divide by 2, add 1, case, multiply by 2. Gosh...(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) – statement_start_offset) / 2+1)) AS sql_statement,
Anonymous
July 20, 2016
The comment has been removed
Anonymous
December 06, 2017
The comment has been removed
Anonymous
May 14, 2018
(The content was deleted per user request)
Anonymous
May 15, 2018
(The content was deleted per user request)
Anonymous
May 15, 2018
(The content was deleted per user request)
Anonymous
May 15, 2018
(The content was deleted per user request)
Anonymous
May 15, 2018
The comment has been removed

次の方法で共有