译:为什么会从sys.dm_exec_query_plan中得到query_plan为NULL的结果

2017-12-03 18:27:56来源:cnblogs.com作者:MSSQL123人点击

分享

背景:

在面对生产环境的数据的异常问题诊断的时候,比如接到异常告警邮件,
通常是CPU居高不下、长时间连续大批量发生物理IO(导致系统响应缓慢)、亦或是大量Session被阻塞或者大量session执行超出预期等等类似情况,
笔者习惯性地利用sys.dm_exec_requests 和 sys.dm_exec_sql_text(sql_handle) t 以及sys.dm_exec_query_plan(plan_handle) 三个系统表去观察当前数据库正在运行哪些Session,
执行的批处理或者存储过程是哪个,正在执行批处理或者存储过程中的哪个sql语句,是否遇到到了阻塞,当前session的运行状态,等待的资源是什么,sql语句是怎么执行的(执行计划),
以及在有参数的情况下,执行计划编译的参数等等信息。
获取实时Session运行的sql语句的执行计划是非常重要的参考信息之一,偶尔会发现一些正在执行的存储过程的执行计划为null(从sys.dm_exec_query_plan中查询的),
这样的话,得到的参考信息有限,不利于问题的诊断和解决。
参考下图:

在网上发现一篇有对此问题分析的文章,觉的写的很好,原文出处:https://blogs.msdn.microsoft.com/psssql/2016/07/13/why-am-i-getting-null-values-for-query_plan-from-sys-dm_exec_query_plan/。

另外:
不仅仅在查询缓存执行计划的时候存在该问题,
sys.dm_exec_query_plan返回的是整个批处理中所有的语句的执行计划,只要任何一个SQL语句的执行计划没有生成,返回的批处理的执行计划就为null
对于实时运行的SQL(批处理或者存储过程),如果是第一次运行或者是类似于作业任务执行频率很低的sql(其执行计划缓存被清理),在真正运行之前的时候,
某些语句中存在临时表的情况下,不会对所有语句进行预编译(也就是完整地编译整个批处理),因此利用sys.dm_exec_query_plan查询的时候,是无法获取当前Session运行的批处理的执行计划的。
对于这种情况,可以利用sys.dm_exec_text_query_plan 这个系统视图,可以得到批处理中语句级的执行计划。

以下为译文:

最近我接到一个用户打来的电话,想知道为什么他会从 sys.dm_exec_query_plan中到NULL值的执行计划,这个客户参考了从 https://dzone.com/articles/dmexecqueryplan-returning-null参考了一篇本文,
在那个场景中,如果语句包含了临时表,并且没有被执行过,你会得到一个NULL之的执行计划。
参考如下代码:

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text,     qp.query_plan, tqp.query_plan AS text_query_planFROM sys.dm_exec_cached_plans cpCROSS APPLY sys.dm_exec_sql_text(plan_handle) tCROSS APPLY sys.dm_exec_query_plan(plan_handle) qpCROSS APPLY sys.dm_exec_text_query_plan(plan_handle, NULL, NULL) tqpWHERE text LIKE '%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,        (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_planFROM 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) s3where object_name ( s2.objectid, s2.dbid) = 'ConditionalPlanTest'ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;  

DEMO

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

use tempdbgocreate table t1 (c1 int)gocreate table t2 (c1 int)gocreate procedure p_test @option intasif @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_planFROM sys.dm_exec_cached_plans s1CROSS APPLY sys.dm_exec_sql_text(plan_handle) s2CROSS APPLY sys.dm_exec_query_plan(plan_handle) s3WHERE object_name ( s2.objectid, s2.dbid) = 'p_test'
因为试图返回整个批处理的执行计划,将会返回空的执行计划
image
用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,        (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) s3where object_name ( s2.objectid, s2.dbid) = 'p_test'ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; 

插曲:

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

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


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台