譯：為什麼會從sys.dm_exec_query_plan中得到query_plan為NULL的結果

背景：在面對生產環境的數據的異常問題診斷的時候，比如接到異常告警郵件，通常是CPU居高不下、長時間連續大批量發生物理IO（導致系統響應緩慢）、亦或是大量Session被阻塞或者大量session執行超出預期等等類似情況，筆者習慣性地利用sys.dm_exec_requests 和 sys.dm_e ...

背景：

在面對生產環境的數據的異常問題診斷的時候，比如接到異常告警郵件，
通常是CPU居高不下、長時間連續大批量發生物理IO（導致系統響應緩慢）、亦或是大量Session被阻塞或者大量session執行超出預期等等類似情況，
筆者習慣性地利用sys.dm_exec_requests 和 sys.dm_exec_sql_text(sql_handle) t 以及sys.dm_exec_query_plan(plan_handle) 三個系統表去觀察當前資料庫正在運行哪些Session，
活動的Session正在執行的批處理或者存儲過程是哪個？
正在執行批處理或者存儲過程中的哪一句sql？
活動的Session是否遇到到了阻塞？
活動的session的運行狀態（runnable或者是suspended）？
等待的資源是什麼？
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查詢的時候，只要批處理中有一句SQL未被編譯，都是無法獲取當前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_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'

在執行調試併進入源代碼之後，發現了與這個問題更多的話題，為了弄清楚這個問題，我們需要瞭解幾個關鍵的概念

延遲編譯（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_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

譯註：存儲過程中因為代碼分支邏輯，導致一部分代碼無法執行，無法執行的代碼分支也就沒有編譯，試圖用sys.dm_exec_query_plan返回執行計劃的時候，因為缺少一部分代碼的執行計劃，返回的執行計劃欄位為空

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'

因為試圖返回整個批處理的執行計劃，將會返回空的執行計劃

用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 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;