关于MSSQL的统计信息

已知MySQL、PgSQL之类的数据库都有【统计信息】，它是执行计划所依赖的。而且数据库会定期更新统计信息，以便执行计划通过它确定SQL应该怎么执行。

之前没有发现MSSQL的统计信息，最近发现它果然是有的。

针对MSSQL2016版本以下的数据库，统计信息的触发阈值是写死的20%的变更量，MSSQL2016及其以上版本可以调整数据库的执行触发阈值。针对20%的变更量，在更新统计信息时会对表以及磁盘、CPU产生一定的性能影响。

那么，我们可以通过数据库作业来小批量更新统计信息：

-- 创建定时作业（每分钟检查变更量）
DECLARE @ModCount INT, @TotalRows INT, @Threshold INT;

-- 获取表修改计数
SELECT TOP 1 
     @ModCount = sp.modification_counter
    --,s.name AS stats_name, sp.last_updated, sp.rows ,sp.rows_sampled
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(OBJECT_ID('dbo.Queue'), s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.Queue')
ORDER BY sp.modification_counter DESC;

-- 计算当前表总行数的5%作为动态触发阈值
SELECT @TotalRows = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.Queue') 
AND index_id IN (0,1);

SET @Threshold = CEILING(@TotalRows * 0.05);

-- 当变更超过动态阈值时触发更新
IF @ModCount > @Threshold
BEGIN
    -- 5秒超时
    SET LOCK_TIMEOUT 5000; 

    -- 全表更新
    --UPDATE STATISTICS dbo.Queue WITH FULLSCAN;
    
    -- 分批更新统计信息（减少单次锁持有时间）；改为NOLOCK，避免与其他事务的锁冲突‌
    UPDATE STATISTICS dbo.Queue WITH SAMPLE 3 PERCENT, NOLOCK;
    WAITFOR DELAY '00:00:02';  -- 间隔 2 秒
    UPDATE STATISTICS dbo.Queue WITH SAMPLE 3 PERCENT, NOLOCK;
    WAITFOR DELAY '00:00:02';  -- 间隔 2 秒
    UPDATE STATISTICS dbo.Queue WITH SAMPLE 3 PERCENT, NOLOCK;
    WAITFOR DELAY '00:00:02';  -- 间隔 2 秒
    UPDATE STATISTICS dbo.Queue WITH SAMPLE 3 PERCENT, NOLOCK;
    WAITFOR DELAY '00:00:02';  -- 间隔 2 秒
    UPDATE STATISTICS dbo.Queue WITH SAMPLE 3 PERCENT, NOLOCK;
    PRINT '统计信息已更新，变更量: ' + CAST(@ModCount AS VARCHAR) + 
          ' 超过阈值: ' + CAST(@Threshold AS VARCHAR);
END

大概的意思就是这样，其中的细节大家可以自行调整，部分作废的方案（比如FullScan）也只是注释，放在那里给大家做个参考。