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

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

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

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

?? 核心跟蹤方法

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

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

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

USE YourDatabaseName; -- 替換為你的數(shù)據(jù)庫(kù)名
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; -- 按最后更新時(shí)間倒序排列，最近更新的在最前面

解讀:

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

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

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

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

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

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

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

-- 假設(shè)會(huì)話名為 'Track_Auto_Stats'，目標(biāo)為事件文件
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);
-- 提取關(guān)鍵信息
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, -- 轉(zhuǎn)換為毫秒
    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)點(diǎn):

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

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

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

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

配置步驟 (Profiler):

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

缺點(diǎn):

• 已被棄用: Microsoft 明確表示 SQL Server Profiler 將在未來(lái)版本中移除。
• 高開銷: 對(duì)服務(wù)器性能影響遠(yuǎn)大于擴(kuò)展事件。
• 信息量較少: 相比 XEvents 的 auto_stats 事件，提供的信息不夠豐富和結(jié)構(gòu)化。

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

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

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

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

語(yǔ)法:

STATS_DATE ( table_id, stats_id )

示例:

-- 先找到表 'YourTable' 上統(tǒng)計(jì)信息 '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)計(jì)信息

局限性:

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

?? 總結(jié)與最佳實(shí)踐建議

最佳實(shí)踐:

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

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

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

最新評(píng)論