SQL Server 如何跟蹤自動統(tǒng)計信息更新：深入解析與實戰(zhàn)指南

在 SQL Server 中，統(tǒng)計信息是查詢優(yōu)化器生成高效執(zhí)行計劃的核心依據(jù)。為了保持其有效性，SQL Server 默認會在數(shù)據(jù)發(fā)生顯著變化（達到內(nèi)部修改計數(shù)器閾值）時自動更新統(tǒng)計信息。然而，數(shù)據(jù)庫管理員和開發(fā)人員經(jīng)常需要了解：

• 何時發(fā)生了自動更新？
• 哪些統(tǒng)計信息對象被更新了？
• 更新是否成功？
• 更新的采樣率是多少？

掌握這些信息對于性能調優(yōu)、排查執(zhí)行計劃突變、驗證維護策略至關重要。本文將詳細介紹幾種有效跟蹤 SQL Server 自動統(tǒng)計信息更新的方法。

?? 核心跟蹤方法

1?? 利用系統(tǒng)目錄視圖和動態(tài)管理視圖 (DMV) - 最常用、最直接

• sys.stats: 包含數(shù)據(jù)庫中所有統(tǒng)計信息對象的基本信息（object_id, stats_id, name, auto_created, user_created, no_recompute）。
• sys.dm_db_stats_properties: 這是關鍵視圖！它返回指定統(tǒng)計信息對象（或所有對象）的屬性，其中最重要的列是：
  • last_updated (datetime2): 統(tǒng)計信息最后更新的日期和時間。這是跟蹤自動更新發(fā)生時間的核心依據(jù)。
  • rows (bigint): 統(tǒng)計信息更新時的總行數(shù)。
  • rows_sampled (bigint): 用于生成直方圖和密度信息的采樣行數(shù)。
  • steps (int): 直方圖中的步數(shù)。
  • unfiltered_rows (bigint): 如果統(tǒng)計信息是過濾統(tǒng)計信息，則表示應用篩選器前的總行數(shù)。
  • modification_counter (bigint): 自上次更新后，統(tǒng)計信息對象引用的前導列發(fā)生修改的總次數(shù)。這是觸發(fā)自動更新的依據(jù)。

示例查詢 - 查看所有統(tǒng)計信息的最后更新時間 (包括自動更新)：

USE YourDatabaseName; -- 替換為你的數(shù)據(jù)庫名
GO
SELECT 
    OBJECT_NAME(sp.[object_id]) AS [Table Name],
    s.[name] AS [Statistic Name],
    sp.[last_updated],
    sp.[rows],
    sp.[rows_sampled],
    sp.[steps],
    sp.[modification_counter],
    s.[auto_created] AS [IsAutoCreated],
    s.[user_created] AS [IsUserCreated],
    s.[no_recompute] AS [NoRecompute]
FROM 
    sys.[stats] AS s
CROSS APPLY 
    sys.[dm_db_stats_properties](s.[object_id], s.[stats_id]) AS sp
ORDER BY 
    sp.[last_updated] DESC; -- 按最后更新時間倒序排列，最近更新的在最前面

解讀:

• 觀察 last_updated 列，即可知道該統(tǒng)計信息對象最后一次更新（無論是自動還是手動）的具體時間。
• 結合 auto_created = 1，可以識別出這是由 SQL Server 自動創(chuàng)建的統(tǒng)計信息。
• 比較 rows 和 rows_sampled 可以了解采樣率（rows_sampled / rows * 100%）。
• modification_counter 顯示自上次更新后的修改量，當其超過內(nèi)部閾值時，SQL Server 會觸發(fā)自動更新。
• 如果 no_recompute = 1，則該統(tǒng)計信息不會自動更新。

2?? 使用 SQL Server 擴展事件 (Extended Events, XEvents) - 實時、低開銷、最靈活

擴展事件是 SQL Server 推薦的輕量級、高性能診斷和監(jiān)控工具，非常適合實時捕獲 auto_stats 事件。

• 關鍵事件： auto_stats
• 此事件在自動統(tǒng)計信息更新操作開始和完成時都會觸發(fā)。
  • operation 字段: 標識操作類型：
    • 1: 開始更新統(tǒng)計信息
    • 2: 統(tǒng)計信息更新成功
    • 3: 統(tǒng)計信息更新失敗
• 其他重要字段:
  • database_id: 發(fā)生更新的數(shù)據(jù)庫 ID。
  • object_id: 統(tǒng)計信息所屬的表或索引視圖的 ID。
  • index_id: 如果統(tǒng)計信息綁定到索引，則為索引 ID (0 表示堆)。
  • statistics_id: 統(tǒng)計信息對象的 ID (在 sys.stats 中對應 stats_id)。
  • retry_count: 如果更新失敗，嘗試重試的次數(shù)。
  • duration: 更新操作的總耗時（微秒）。
  • sample_type: 采樣類型（例如，基于行數(shù)或百分比）。
  • sample_pages: 用于更新的采樣頁數(shù)。
  • rows: 表中的總行數(shù)。
  • rows_sampled: 實際采樣的行數(shù)。
  • steps: 生成的直方圖步數(shù)。
  • retention: 統(tǒng)計信息保留選項（通常為 NULL）。
  • completion_time: 操作完成的時間戳（僅在完成事件中有效）。

創(chuàng)建擴展事件會話示例 (SSMS):

• 打開 "Management" -> "Extended Events" -> "New Session Wizard..."。
• 輸入會話名稱 (例如 Track_Auto_Stats)。
• 在 "Events" 頁面，搜索并添加 auto_stats 事件。
• 在 "Global Fields (Actions)" 頁面，添加常用的全局字段如 sql_text, client_app_name, client_hostname, username。
• 在 "Filter (Predicate)" 頁面 (可選)，可以添加過濾條件，例如只監(jiān)控特定數(shù)據(jù)庫 ([sqlserver].[database_id] = YourDBID) 或只監(jiān)控失敗事件 ([operation] = 3)。
• 在 "Data Storage" 頁面，選擇目標。event_file 最常用，指定文件位置和大小上限。ring_buffer 適合短期內(nèi)存監(jiān)控。
• 完成向導并啟動會話。

查詢擴展事件數(shù)據(jù) (示例):

-- 假設會話名為 'Track_Auto_Stats'，目標為事件文件
SELECT 
    event_data = CAST(event_data AS XML) 
INTO 
    #TempEventData 
FROM 
    sys.fn_xe_file_target_read_file('C:\YourPath\Track_Auto_Stats*.xel', null, null, null);
-- 提取關鍵信息
SELECT 
    ed.event_data.value('(event/@name)[1]', 'varchar(50)') AS EventName,
    ed.event_data.value('(event/@timestamp)[1]', 'datetime2') AS EventTime,
    ed.event_data.value('(event/data[@name="database_id"]/value)[1]', 'int') AS DatabaseID,
    ed.event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS ObjectID,
    ed.event_data.value('(event/data[@name="statistics_id"]/value)[1]', 'int') AS StatsID,
    ed.event_data.value('(event/data[@name="operation"]/text)[1]', 'varchar(20)') AS Operation, -- 'Started', 'StatsUpdated', 'StatsUpdateFailed'
    ed.event_data.value('(event/data[@name="retry_count"]/value)[1]', 'int') AS RetryCount,
    ed.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS Duration_ms, -- 轉換為毫秒
    ed.event_data.value('(event/data[@name="sample_type"]/text)[1]', 'varchar(50)') AS SampleType,
    ed.event_data.value('(event/data[@name="rows"]/value)[1]', 'bigint') AS Rows,
    ed.event_data.value('(event/data[@name="rows_sampled"]/value)[1]', 'bigint') AS RowsSampled,
    ed.event_data.value('(event/data[@name="steps"]/value)[1]', 'int') AS Steps,
    ed.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS SQLText -- 觸發(fā)更新的查詢（如果有）
FROM 
    #TempEventData AS ed;
DROP TABLE #TempEventData;

優(yōu)點:

• 捕獲操作開始和結束（成功/失?。┦录?。
• 提供極其豐富的上下文信息（耗時、采樣詳情、觸發(fā)查詢 SQL 文本等）。
• 開銷非常低，適合生產(chǎn)環(huán)境。
• 可精細過濾。

3?? SQL Trace / SQL Server Profiler (傳統(tǒng)方法，不推薦用于新開發(fā))

雖然 SQL Server Profiler 和 SQL Trace 已被擴展事件取代，但在一些舊環(huán)境中仍可能使用。

• 關鍵事件類:
  • Performance: Auto Stats
  • Errors and Warnings: Attention (有時更新失敗會關聯(lián) Attention 事件)

配置步驟 (Profiler):

• 啟動 Profiler (SQL Server Profiler)，連接到目標實例。
• 創(chuàng)建新跟蹤。
  • 在 "Events Selection" 選項卡:
  • 展開 Performance 事件類別，勾選 Auto Stats。
  • (可選) 展開 Errors and Warnings，勾選 Attention。
  • 根據(jù)需要添加其他列（如 DatabaseID, ObjectID, TextData）。
• 運行跟蹤。

缺點:

• 已被棄用: Microsoft 明確表示 SQL Server Profiler 將在未來版本中移除。
• 高開銷: 對服務器性能影響遠大于擴展事件。
• 信息量較少: 相比 XEvents 的 auto_stats 事件，提供的信息不夠豐富和結構化。

4?? 服務器端跟蹤 (Server-Side Trace)

這是 Profiler GUI 的后臺機制。你可以使用系統(tǒng)存儲過程 (sp_trace_create, sp_trace_setevent, sp_trace_setstatus) 創(chuàng)建更輕量級、持久的跟蹤，并將結果寫入文件。跟蹤的事件與 Profiler 相同 (Auto Stats)。管理比 XEvents 復雜。

5?? 使用STATS_DATE()函數(shù) (特定對象檢查)

這是一個標量函數(shù)，用于查詢單個特定統(tǒng)計信息對象的最后更新日期。

語法:

STATS_DATE ( table_id, stats_id )

示例:

-- 先找到表 'YourTable' 上統(tǒng)計信息 'YourStatName' 的 object_id 和 stats_id
USE YourDatabaseName;
GO
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS StatName,
    stats_id,
    STATS_DATE(object_id, stats_id) AS LastUpdated 
FROM 
    sys.stats 
WHERE 
    object_id = OBJECT_ID('YourTable') 
    AND name = 'YourStatName'; -- 或者省略 name 查看表上所有統(tǒng)計信息

局限性:

• 只能查詢單個已知的統(tǒng)計信息對象。
• 不如 sys.dm_db_stats_properties 查詢整個數(shù)據(jù)庫方便。
• 不區(qū)分自動更新還是手動更新。

?? 總結與最佳實踐建議

最佳實踐:

• 日常檢查/快速查看: 首選 sys.dm_db_stats_properties 和 sys.stats 視圖查詢。
• 深入監(jiān)控/故障診斷/性能分析: 強烈推薦使用擴展事件。配置一個長期運行的會話來捕獲 auto_stats 事件，尤其是在性能敏感或需要調查執(zhí)行計劃不穩(wěn)定問題的環(huán)境中。
• 驗證維護計劃: 結合使用視圖（檢查 last_updated）和擴展事件（確認更新成功完成），驗證你的統(tǒng)計信息維護任務（無論是自動更新還是你自定義的作業(yè)）是否按預期運行。
• 關注 modification_counter: 這個計數(shù)器是理解為什么自動更新可能被觸發(fā)的關鍵。將其與 last_updated 結合可以判斷數(shù)據(jù)變化的活躍程度。
• 注意異步更新: 如果啟用了 AUTO_UPDATE_STATISTICS_ASYNC，查詢可能在統(tǒng)計信息完成更新前就使用了舊版本編譯計劃。擴展事件是跟蹤異步更新狀態(tài)的最佳方式。
• 定期審查: 將統(tǒng)計信息更新監(jiān)控納入常規(guī)的數(shù)據(jù)庫健康檢查中。

通過有效利用這些跟蹤方法，你可以清晰掌握 SQL Server 自動統(tǒng)計信息更新的動態(tài)，為數(shù)據(jù)庫性能優(yōu)化和穩(wěn)定性保障提供堅實的基礎數(shù)據(jù)支撐！????

到此這篇關于SQL Server跟蹤自動統(tǒng)計信息更新實戰(zhàn)指南的文章就介紹到這了,更多相關sqlserver自動更新統(tǒng)計信息內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論