译：为什么会从sysdm_exec_query_plan中得到query_plan为NULL的结果(2/2)

本篇文章主要介绍了" 译：为什么会从sysdm_exec_query_plan中得到query_plan为NULL的结果"，主要涉及到方面的内容，对于SqlServer感兴趣的同学可以参考一下： 背景：在面对生产环境的数据的异常问题诊断的时候，比如接到异常告警邮件，通常是CPU居高不下、长时间连续大批量发生物理IO（导致系统响应缓慢）、亦或是大量Ses...

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
WHEREtextLIKE'%ConditionalPlanTest%'AND objtype ='Proc'

在执行调试并进入源代码之后，发现了与这个问题更多的话题，为了弄清楚这个问题，我们需要了解几个关键的概念

延迟编译（Deferred compilation）

当第一次执行包含了多个语句的批处理或者存储过程的时候，不是所有的语句都会被编译。如果一些代码分支导致一些语句不会被执行，SQL Server可能会就不会去编译这些语句。换句话数，一部分语句只有第一次执行的时候才会被编译，到目前为止，我发现，如果代码分支被跳过的情况下，由如下两种情况后导致延迟编译。

1，语句包含临时表。临时表的数据可能会在实际执行的时候发生变化，因此，一开始就编译这些sql语句是没有意义的（译注：临时表的数据量，分布等信息会影响到最终的执行计划）
2，语句附加了option (recompile)选项，这种情况下每次都会重新编译语句，如果不使用它，为什么要提前编译它？

你想要的是整个批处理的还是语句级的执行计划

sys.dm_exec_query_plan提供了整个批处理（或者存储过程）的执行计划，而sys.dm_exec_text_query_plan 允许你根据语句偏移开始位置和结束位置（statement_start_offset and statement_end_offset）获取语句级的执行计划，当然， sys.dm_exec_text_query_plan在没有提供语句偏移量的时候，可以返回整个批处理的执行计划

为什么是NULL值

当指定获取整个批处理（或者存储过程）的执行计划的时候，SQL Server将会检索整个批处理或者存储过程中的所有语句的执行计划，如果任何一个语句的执行计划不存在，那么就是返回NULL值，因为执行计划是不完整的（对于批处理或者存储过程来说）。注意，上述获取执行计划的语句是从sys.dm_exec_query_plan中查询的，意味着需要返回整个批处理的执行计划，这就是为什么返回NULL值的原因。

在一个不是所有语句都被编译的批处理中，如何获取语句级别的执行计划

你可以在“提供批处理统计”的情况下修改上述查询语句，从sys.dm_exec_sql_text中可以得到哪些因为包含了临时表或者 option (recompile)的导致没有提前完整编译的语句级执行计划，这里有一个查询示例，请注意需要提供严格的语句偏移开始和结束位置信息

SELECT s2.dbid,   
    s1.sql_handle,    
    (SELECTTOP1SUBSTRING(s2.text,statement_start_offset /2+1 ,   
      ( (CASEWHEN statement_end_offset =-1THEN (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
whereobject_name ( s2.objectid, s2.dbid) ='ConditionalPlanTest'ORDERBY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;  

DEMO

译注：存储过程中因为代码分支逻辑，导致一部分代码无法执行，无法执行的代码分支也就没有编译，试图用sys.dm_exec_query_plan返回执行计划的时候，因为缺少一部分代码的执行计划，返回的执行计划字段为空

use tempdb
gocreatetable t1 (c1 int)
gocreatetable t2 (c1 int)
gocreateprocedure p_test @optionintasif@option>=2select*from t1 option (recompile)
if@option>=1select*from t2 option (recompile)

go--this will only execute  2nd statement and skip first statementp_test 1go--NULL plan will be returned because the query wants whole batch planSELECT 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
WHEREobject_name ( s2.objectid, s2.dbid) ='p_test'

因为试图返回整个批处理的执行计划，将会返回空的执行计划

用sys.dm_exec_text_query_plan 返回执行计划的时候，可以返回批处理中语句级的执行计划

--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 deferredSELECT s2.dbid,   
    s1.sql_handle,    
    (SELECTTOP1SUBSTRING(s2.text,statement_start_offset /2+1 ,   
      ( (CASEWHEN statement_end_offset =-1THEN (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
whereobject_name ( s2.objectid, s2.dbid) ='p_test'ORDERBY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; 

插曲：

中间遇到一个悲剧的问题：基本上写完的情况下，往文章里面粘代码，按下ctrl+v之后，瞬间就变成这种样子了，因为不是从草稿状态编辑的，整篇文章都没有了，随时可以重现。

再刷新，内容里面毛线都没有了，┗|｀O′|┛ 嗷~~

以上就介绍了 译：为什么会从sysdm_exec_query_plan中得到query_plan为NULL的结果，包括了方面的内容，希望对SqlServer有兴趣的朋友有所帮助。

本文网址链接：http://www.codes51.com/article/detail_4514617_2.html