搜尋本站文章

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


沒有留言:

張貼留言