SQL Server跟踪自动统计信息更新实战指南

SQL Server 跟踪自动统计信息更新实战指南

一、自动统计信息更新机制

-- 查看统计信息更新设置
SELECT 
    name AS 数据库名,
    is_auto_update_stats_on AS 自动更新统计信息,
    is_auto_update_stats_async_on AS 异步更新统计信息
FROM sys.databases
WHERE name = DB_NAME();

-- 修改设置（如果需要）
ALTER DATABASE [YourDB] 
SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE [YourDB] 
SET AUTO_UPDATE_STATISTICS_ASYNC OFF; -- 通常建议关闭异步

SQL Server使用以下阈值决定是否更新统计信息：

• 空表 → 插入任何数据时更新
• < 500行 → 每500次修改后更新

• 500行 → 500 + (20% × 行数) 次修改后更新

• 临时表 → 6次修改后更新

二、监控自动统计信息更新

-- 查看最近统计信息更新情况
SELECT 
    OBJECT_NAME(s.object_id) AS 表名,
    s.name AS 统计信息名称,
    STATS_DATE(s.object_id, s.stats_id) AS 最后更新日期,
    s.auto_created AS 是否自动创建,
    s.user_created AS 是否用户创建,
    s.no_recompute AS 是否禁止自动更新
FROM sys.stats s
WHERE OBJECT_NAME(s.object_id) IN ('YourTable1', 'YourTable2')
ORDER BY STATS_DATE(s.object_id, s.stats_id) DESC;

-- 查看统计信息修改计数器
SELECT 
    OBJECT_NAME(object_id) AS 表名,
    rowmodctr AS 修改计数器,
    STATS_DATE(object_id, stats_id) AS 最后更新
FROM sys.sysindexes
WHERE id = OBJECT_ID('YourTable')
AND indid < 2; -- 只查看表级统计信息

-- 创建扩展事件会话跟踪统计信息更新
CREATE EVENT SESSION [Track_Stats_Updates] 
ON SERVER 
ADD EVENT sqlserver.auto_stats (
    ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE ([database_id] = DB_ID('YourDB'))
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\StatsUpdates.xel'
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = ON
);

-- 启动事件会话
ALTER EVENT SESSION [Track_Stats_Updates] 
ON SERVER STATE = START;

-- 查看收集的数据
SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/data[@name="database_name"]/value)[1]', 'varchar(100)') AS database_name,
    event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') AS object_id,
    OBJECT_NAME(event_data.value('(event/data[@name="object_id"]/value)[1]', 'int'),
                event_data.value('(event/data[@name="database_id"]/value)[1]', 'int')) AS table_name,
    event_data.value('(event/data[@name="statistics_list"]/value)[1]', 'varchar(max)') AS statistics_updated,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text
FROM (
    SELECT 
        CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(
        'D:\XEvents\StatsUpdates*.xel', 
        NULL, NULL, NULL)
) AS EventData
ORDER BY event_time DESC;

• Performance → Auto Stats
• Stored Procedures → SP:Completed
• TSQL → SQL:BatchCompleted

三、性能影响分析

-- 查看当前统计信息更新相关的等待
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE '%STATS%'
ORDER BY wait_time_ms DESC;

-- 实时监控统计信息更新活动
SELECT 
    er.session_id,
    er.status,
    er.command,
    er.wait_type,
    er.wait_time,
    er.last_wait_type,
    er.blocking_session_id,
    OBJECT_NAME(st.objectid, st.dbid) AS 对象名,
    st.text AS SQL语句
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE er.command LIKE '%UPDATE STATS%' 
   OR er.wait_type LIKE '%STATS%';

-- 使用查询存储分析（SQL Server 2016+）
SELECT 
    qsrs.start_time,
    qsrs.end_time,
    qsrs.avg_duration / 1000 AS avg_duration_sec,
    qsqt.query_sql_text
FROM sys.query_store_runtime_stats qsrs
JOIN sys.query_store_runtime_stats_interval qsrsi 
    ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
JOIN sys.query_store_plan qsp 
    ON qsrs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq 
    ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qsqt 
    ON qsq.query_text_id = qsqt.query_text_id
WHERE qsqt.query_sql_text LIKE '%UPDATE STATISTICS%'
   OR qsqt.query_sql_text LIKE '%sp_updatestats%'
ORDER BY qsrs.start_time DESC;

四、最佳实践配置

-- 1. 为关键大表调整异步更新
ALTER DATABASE [YourDB] 
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- 2. 禁用特定统计信息的自动更新
UPDATE STATISTICS [Schema].[TableName] [StatsName] 
WITH NORECOMPUTE;

-- 3. 重新启用自动更新
UPDATE STATISTICS [Schema].[TableName] [StatsName] 
WITH RECOMPUTE;

-- 4. 设置采样率（SQL Server 2016+）
UPDATE STATISTICS [Schema].[TableName] [StatsName] 
WITH SAMPLE 50 PERCENT, PERSIST_SAMPLE_PERCENT = ON;

-- 创建自定义统计信息维护计划
USE [msdb];
GO

-- 创建作业
EXEC dbo.sp_add_job
    @job_name = N'Custom_Stats_Maintenance',
    @enabled = 1;

-- 创建作业步骤：更新过期的统计信息
EXEC sp_add_jobstep
    @job_name = N'Custom_Stats_Maintenance',
    @step_name = N'Update_Outdated_Stats',
    @subsystem = N'TSQL',
    @command = N'
DECLARE @UpdateStatsSQL NVARCHAR(MAX) = N'''';
SELECT @UpdateStatsSQL = @UpdateStatsSQL + 
    N''UPDATE STATISTICS ['' + s.name + N''].['' + t.name + N''] ['' + st.name + N''] 
    WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;'' + CHAR(13)
FROM sys.stats st
JOIN sys.tables t ON st.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
WHERE 
    t.is_ms_shipped = 0
    AND DATEDIFF(HOUR, sp.last_updated, GETDATE()) > 24  -- 超过24小时未更新
    AND sp.modification_counter > 
        CASE 
            WHEN sp.rows < 500 THEN 500
            ELSE 500 + (0.20 * sp.rows)
        END
ORDER BY sp.modification_counter DESC;

-- 限制每次最多更新10个统计信息
SET @UpdateStatsSQL = (
    SELECT TOP 10 
        REPLACE(REPLACE(sql, N''WITH FULLSCAN'', N''WITH SAMPLE 50 PERCENT''), 
                N''PERSIST_SAMPLE_PERCENT = ON'', N'''')
    FROM (VALUES (1, @UpdateStatsSQL)) AS t(id, sql)
    ORDER BY id
);

IF @UpdateStatsSQL IS NOT NULL
    EXEC sp_executesql @UpdateStatsSQL;',
    @database_name = N'YourDB';

五、故障排除

-- 问题1：统计信息更新过于频繁
-- 解决方案：增加修改阈值
DBCC TRACEON (2371, -1); -- 启用可变的统计信息更新阈值

-- 问题2：统计信息更新导致性能下降
-- 监控并手动安排在非高峰时段更新
SELECT 
    t.name AS 表名,
    s.name AS 统计信息名,
    sp.last_updated,
    sp.rows,
    sp.modification_counter,
    sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) AS 修改百分比
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE 
    t.is_ms_shipped = 0
    AND sp.modification_counter > 
        CASE 
            WHEN sp.rows < 500 THEN 500
            ELSE 500 + (0.20 * sp.rows)
        END
ORDER BY 修改百分比 DESC;

-- 问题3：统计信息过期但未自动更新
-- 强制更新特定表的统计信息
EXEC sp_updatestats 
    @resample = 'RESAMPLE';  -- 保持现有采样率

-- 或者手动更新
UPDATE STATISTICS [Schema].[TableName] 
WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

-- 创建监控存储过程
CREATE PROCEDURE dbo.Monitor_Stats_Health
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建监控表（如果不存在）
    IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Stats_Monitoring_History')
    BEGIN
        CREATE TABLE dbo.Stats_Monitoring_History (
            monitor_date DATETIME DEFAULT GETDATE(),
            database_name NVARCHAR(128),
            table_name NVARCHAR(128),
            stats_name NVARCHAR(128),
            last_updated DATETIME,
            modification_counter BIGINT,
            rows BIGINT,
            is_auto_updated BIT,
            modification_percentage DECIMAL(5,2)
        );
        CREATE INDEX IX_StatsHistory ON dbo.Stats_Monitoring_History(monitor_date, database_name);
    END

    -- 收集统计信息健康状态
    DECLARE @SQL NVARCHAR(MAX) = N'';

    SELECT @SQL = @SQL + 
        N'INSERT INTO dbo.Stats_Monitoring_History 
        (database_name, table_name, stats_name, last_updated, 
         modification_counter, rows, is_auto_updated, modification_percentage)
        SELECT 
            DB_NAME() AS database_name,
            OBJECT_SCHEMA_NAME(st.object_id) + ''.'' + OBJECT_NAME(st.object_id),
            st.name,
            sp.last_updated,
            sp.modification_counter,
            sp.rows,
            st.no_recompute AS is_auto_updated,
            CASE 
                WHEN sp.rows = 0 THEN 0
                ELSE (sp.modification_counter * 100.0 / sp.rows)
            END
        FROM sys.stats st
        CROSS APPLY sys.dm_db_stats_properties(st.object_id, st.stats_id) sp
        WHERE sp.modification_counter > 0
        AND sp.last_updated < DATEADD(HOUR, -1, GETDATE());' + CHAR(13);

    -- 在所有用户数据库执行
    EXEC sp_MSforeachdb '
        IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
        BEGIN
            USE [?];
            DECLARE @DBSQL NVARCHAR(MAX) = REPLACE(''?' + @SQL + ''', ''?'', DB_NAME());
            EXEC sp_executesql @DBSQL;
        END';

    -- 生成报告
    SELECT 
        CONVERT(VARCHAR(16), monitor_date, 120) AS 监控时间,
        database_name AS 数据库,
        table_name AS 表名,
        stats_name AS 统计信息,
        DATEDIFF(HOUR, last_updated, GETDATE()) AS 未更新小时数,
        modification_percentage AS 修改百分比,
        CASE 
            WHEN modification_percentage > 50 THEN '需要立即更新'
            WHEN modification_percentage > 30 THEN '建议更新'
            ELSE '正常'
        END AS 状态
    FROM dbo.Stats_Monitoring_History
    WHERE monitor_date >= DATEADD(DAY, -1, GETDATE())
    ORDER BY modification_percentage DESC;
END
GO

-- 创建作业每天运行
EXEC msdb.dbo.sp_add_job
    @job_name = N'Daily_Stats_Health_Monitor';

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Daily_Stats_Health_Monitor',
    @step_name = N'Run_Monitoring',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.Monitor_Stats_Health;',
    @database_name = N'YourDB';

EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Daily_8AM',
    @freq_type = 4, -- 每天
    @freq_interval = 1,
    @active_start_time = 80000; -- 08:00:00

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'Daily_Stats_Health_Monitor',
    @schedule_name = N'Daily_8AM';

六、关键建议

监控策略：

• 使用扩展事件持续监控
• 定期检查统计信息健康状态
• 关注高修改率的表

优化建议：

• 对大型表使用采样更新（如30-50%）
• 在非高峰时段手动更新关键统计信息
• 对静态表禁用自动更新

故障响应：

• 频繁更新：调整跟踪标志2371
• 更新阻塞：考虑异步更新或优化维护窗口
• 过期统计信息：设置更激进的维护计划

这个指南提供了从监控到优化的完整方案。建议先实施监控部分，了解当前系统的统计信息更新模式，再根据实际情况调整优化策略。