啟用 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
- 啟用 DBCC TRACEON(8666)
- 使用 Include Actual Execution Plan / Display Estimated Execution Plan
- 執行 SQL Query ，僅查詢 單一 資料表
- 關閉 DBCC TRACEOFF(8666)
-- figure 21_Actual_Execution_Plan_DBCC TRACEON(8666)
02. 檢視 Execution Plan
- 點選左邊的 SELECT operator，滑鼠右鍵，選擇 Properties。
- 在右邊 Properties 窗格，展開 Internal Debugging Information。
- 展開 與 觀察 Recompile 與 ModTrackingInfo。
可以看到 1 份 Statistics 資訊，檢視 ModTrackingInfo 元素
- StatName: _WA_Sys_00000003_014935CB
- ColName: ContactName
- ModCtr: 91
- RowCount: 91
- Threshold: 500
- Reason: small table
-- figure 22_Execution_Plan_SELECT_Properties_Internal Debugging Information
03. 執行複雜 SQL Query
- 此 SQL Query，使用 Inner Join，查詢 2 張資料表，並使用 Where 條件式。
- 觀察 Recompile 與 ModTrackingInfo。
- 可以看到 當時 2 張資料表上的 全部 Statistics 資訊，約：15 個。
在此案例 (Inner Join) 中，啟用 DBCC TRACEON(8666) 後，會取得 當時 參與資料表上的全部 Statistics 資訊。但仍可分析像是 條件式 Where、On 等 SQL statement，得知 當時 所使用的 Statistics。
04. 以 XML 格式 來檢視 Execution Plan
- 觀察 Orders 資料表的 Statistics。
-- figure 32_XML_Orders
05. 以 XML 格式 來檢視 Execution Plan
- 觀察 Orders 資料表的 Statistics。
-- figure 33_XML
06. 以 XML 格式 來檢視 Execution Plan
- 觀察 Customers 資料表的 Statistics。
-- figure 34_XML_Customers
Sample Code
20181029_Find out Statistics_TRACEON_8666
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing
Reference
Use Autostat (AUTO_UPDATE_STATISTICS), 認識 自動更新統計資料 (1)
http://sharedderrick.blogspot.com/2018/10/statistics-use-autostat.html
Use Autostat (AUTO_UPDATE_STATISTICS), 認識 自動更新統計資料 (2)
http://sharedderrick.blogspot.com/2018/10/use-autostat-autoupdatestatistics-2.html
Find out Statistics used to Execution Plan in SQL Server 2017, 找出 執行計畫 所使用的 統計值
http://sharedderrick.blogspot.com/2018/10/find-out-statistics-used-to-execution.html
