德瑞克：SQL Server 學習筆記: Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666), 由 Plan Cache 取得執行計畫所使用的統計值

啟用 TRACEON(8666) 後，在該 Session 就可以:

由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics
由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
在 Execution Plan 上會額外提供 Internal Debugging Information(內部偵錯用訊息) ，這包含了 Statistics 細節資訊。

例如，可以取得:
StatName、ColName、ModCtr、RowCount、Threshold、Reason 等資訊。

此為 Undocumented Trace Flags: DBCC TRACEON(8666)
適用版本: SQL Server 2008 以上的環境

檢視 ModTrackingInfo 元素

StatName: _WA_Sys_00000003_014935CB
ColName: ContactName
ModCtr: 91
RowCount: 91
Threshold: 500
Reason: small table

Find out Statistics used to Execution Plan from Plan Cache - DBCC TRACEON(8666),

由 Plan Cache 找出執行計畫所使用的統計值 - - DBCC TRACEON(8666)

本文以下介紹 2 種用法:

(1) 由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics -
啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：

啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。

功能：

將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

若沒有啟用 DBCC TRACEON(8666) ，則 sys.dm_exec_query_plan 顯示為 NULL，沒有資料。

(2) 由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
- 啟用 TRACEON(8666)

用法：

若搭配指定的 SQL Query，使用 Include Actual Execution Plan / Display Estimated Execution Plan，可以取得 Query Optimizer 在 Execution Plan 所使用的 Statistics。

功能：

將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

(1) 由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics
啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。
若沒有啟用 DBCC TRACEON(8666) ，sys.dm_exec_query_plan 的 [Statistics] 顯示 NULL，沒有資料。

01. 使用 dm_db_stats_properties: 傳回資料表的所有 Statistics 屬性

Table: Customers, Orders
已經存在 15 個 Statistics

-- figure 01_dm_db_stats_properties Returns properties of statistics for the specified database object

02. 執行 SQL Query。

僅查詢單一資料表。
DBCC FREEPROCCACHE: 清除 Plan Cache，此為非必要，在本範例中，也僅是用於重置環境參數。

-- figure 02_Removes plan cache and Query Table with Auto Create Statistics

03. 啟用 TRACEON(8666) ，並執行 sys.dm_exec_query_plan

用法：啟用 DBCC TRACEON(8666) 後，可以使用 sys.dm_exec_query_plan 由 Plan Cache ，取得 Query Optimizer 在 Execution Plan 所使用的 Statisitics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。
若沒有啟用 DBCC TRACEON(8666) ，sys.dm_exec_query_plan 的 [Statistics] 顯示 NULL，沒有資料。

SQL Script 說明

啟用 DBCC TRACEON(8666)
執行 sys.dm_exec_query_plan，取得 Execution Plan 所使用的 Statistics
關閉 DBCC TRACEOFF(8666)

-- DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan from Plan Cache.
DBCC TRACEON(8666)

SELECT TOP 100 st.text [TSQL], DB_NAME(st.dbid) [DB], 
 qs.last_elapsed_time/1000000.0 [LastElapsedTime(sec)],
 qs.last_worker_time/1000000.0 [LastCPUTime(sec)],
 qs.last_logical_reads LastLogicalReads,
 qs.last_physical_reads LastPhysicalRead,
 qs.last_logical_writes LastlogicalWrites,
 qs.last_rows LastRows,
 cp.usecounts [ExecutionCount], cp.size_in_bytes/1024.0 [PlanSize(KB)],
 cp.cacheobjtype [CacheObject], cp.objtype [ObjType], qs.plan_generation_num [PlanRecompile],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:ParameterList/ns:ColumnReference[@Column])') [ParameterList],
 qp.query_plan.query('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
 (/ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/ns:InternalInfo/ns:EnvColl/ns:Recompile)') [Statistics], 
 qp.query_plan [QueryPlan], cp.plan_handle, qs.last_execution_time, qs.creation_time
FROM sys.dm_exec_cached_plans cp INNER JOIN sys.dm_exec_query_stats qs
 ON cp.plan_handle = qs.plan_handle
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE st.dbid = DB_ID('Northwind')
-- CHARINDEX ('Products', st.text) >0
-- st.text LIKE '%xxx%'
ORDER BY qs.last_execution_time DESC;

DBCC TRACEOFF(8666)
GO

-- figure 03_ DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan from Plan Cache.

04. 檢視回傳的結果集。

QueryPlan: 存放 Execution Plan。
Statistics: 存放 XML 格式的 Statistics。

-- figure 04_Get_Statistics_XML

05. 檢視 QueryPlan 內的 Execution Plan

點選左邊的 SELECT operator，滑鼠右鍵，選擇 Properties。
在右邊 Properties 窗格，展開 Internal Debugging Information。
展開與觀察 Recompile 與 ModTrackingInfo。

-- figure 05_SELECT_Properties_Internal Debugging Information

06. 在 ModTractkingInfo，展開顯示更多資訊關於 Statistics 資訊。

-- figure 06_ModTrackingInfo

07. 檢視由 Plan Cache 取得的 Statistics 資訊

以 XML 格式呈現的 Statistics。

-- figure 07_Execution_Plan_Statistics

08. 檢視 ModTrackingInfo 元素

StatName: _WA_Sys_00000003_014935CB
ColName: ContactName
ModCtr: 91
RowCount: 91
Threshold: 500
Reason: small table

-- figure 08_Statistics_ModTrackingInfo

09. 以 XML 格式來檢視 Execution Plan

InternalInfo 元素

-- figure 09_XML

10. 以 XML 格式來檢視 Execution Plan

Recompile 元素

-- figure 10_XML

11. 以 XML 格式來檢視 Execution Plan

ModTrackingInfo 元素

-- figure 11_XML_ModTrackingInfo

(2) 由 Include Actual Execution Plan / Display Estimated Execution Plan ，取得 Execution Plan 所使用的 Statistics
-- 啟用 TRACEON(8666)

先前介紹的是由 Plan Cache (Memory) 取得 Execution Plan 所使用的 Statistics。

以下方式，則是應用於 Include Actual Execution Plan / Display Estimated Execution Plan ，取得在 Execution Plan 所使用的 Statistics。

DBCC TRACEON(8666) -- Find out Statistics used to Execution Plan

用法：若搭配指定的 SQL Query，使用 Include Actual Execution Plan / Display Estimated Execution Plan，可以取得 Query Optimizer 在 Execution Plan 所使用的 Statistics。
功能：將內部偵錯用訊息存放於 Execution Plan 內，這包含了 Statistics 細節資訊。

01. 執行以下 SQL Query