搜尋本站文章

2018-10-29

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


  • 啟用 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。

-- figure 31_Display all 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

2018-10-19

Find out Statistics used to Execution Plan in SQL Server 2017, 找出 執行計畫 所使用的 統計值


SQL Server 2017 強化 Execution Plan,增加 [OptimizerStatsUsage] 屬性,可提供 Query Optimizer 在此 Execution Plan 所使用的 Statistics。

無論是 Include Actual Execution Plan / Display Estimated Execution Plan,都可以使用 [OptimizerStatsUsage] 屬性。

經過測試: SQL Server 2017 版本,並且 COMPATIBILITY_LEVEL = 120 以上適用。







Find out Statistics used to Execution Plan in SQL Server 2017
找出 Execution Plan (執行計畫) 所使用的 Statistics (統計值)



SQL Server 2017 強化 Execution Plan,增加 [OptimizerStatsUsage] 屬性,可提供 Query Optimizer 在此 Execution Plan 所使用的 Statistics。

無論是 Include Actual Execution Plan / Display Estimated Execution Plan,都可以使用 [OptimizerStatsUsage] 屬性。

初步測試: SQL Server 2017 版本,並且 COMPATIBILITY_LEVEL = 120 以上適用。SQL Server 2014 (x.12)




01. 使用 dm_db_stats_properties: 傳回 資料表 的所有 Statistics 屬性
  • Table: Customers, Orders

-- figure 01_dm_db_stats_properties_Customers_Orders






02. 執行 SQL Query
  • 僅查詢單一資料表。

  • 無論是 Include Actual Execution Plan / Display Estimated Execution Plan,都可以使用 [OptimizerStatsUsage] 屬性。
  • 點選 左邊 最後一個 SELECT Operator,滑鼠 右鍵,選擇 Properties。

  • 自動產生 Statistics。
    • 依據預設值,有啟用資料庫選項:AUTO_CREATE_STATISTICS。
    • WHERE 條件式使用 Column: ContactName,Query Optimizer 將自動為其建立 Statistics。

-- figure 11_SQL_Server 2017_SELECT







03. 在 Properties 視窗,可以看到 [OptimizerStatsUsage] 區塊,使用到 1 個 Statistics,包含以下資訊:
  • Database: [Northwind]
  • LastUpdate: 2018-10-19T21:01:05.14
  • ModificationCount: 0
  • SamplingPercent: 100
  • Schema: dbo
  • Statistics: _WA_Sys_00000003_014935CB
  • Table: [Customers]


-- figure 12_SELECT_Properties_OptimizerStatsUsage





04. 檢視 Show Execution Plan XML
  • 觀察 OptimizerStatsUsage

-- figure 13_Show_Execution_Plan_XML





05. 執行 SQL Query
  • Table Join 2 個

    • 無論是 Include Actual Execution Plan / Display Estimated Execution Plan,都可以使用 [OptimizerStatsUsage] 屬性。
    • 點選 左邊 最後一個 SELECT Operator,滑鼠 右鍵,選擇 Properties。
-- figure 14_SQL_Server 2017_SELECT






06. 在 Properties 視窗,可以看到 [OptimizerStatsUsage] 區塊,使用到 3 個 Statistics,包含以下資訊:

[1]
  • Database: [Northwind]
  • LastUpdate: 2018-10-19T21:01:05.14
  • ModificationCount: 0
  • SamplingPercent: 100
  • Schema: dbo
  • Statistics: _WA_Sys_00000003_014935CB
  • Table: [Customers]

[2]
  • Database: [Northwind]
  • LastUpdate: 2008-09-13T13:06:45.8
  • ModificationCount: 0
  • SamplingPercent: 100
  • Schema: dbo
  • Statistics: [CustomerID]
  • Table: [Orders]

[3]
  • Database: [Northwind]
  • LastUpdate: 2008-09-13T13:06:45.77
  • ModificationCount: 0
  • SamplingPercent: 100
  • Schema: dbo
  • Statistics: [PK_Customers]
  • Table: [Customers]

-- figure 15_SELECT_Properties_OptimizerStatsUsage






07. 檢視 Show Execution Plan XML
  • 觀察 OptimizerStatsUsage

-- figure 16_Show_Execution_Plan_XML





08. 使用 SSMS 檢視 Index 與 Statistics

-- figure 17_SSMS_Statistics







Sample Code

20181019_Find out Statistics used to Execution Plan
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