如何确定SQL Server大库中需执行full scan统计信息更新的表

核心判断逻辑 #

单靠sys.dm_db_index_usage_stats无法完成全维度判断，需要结合多个系统视图/DMV的数值组合筛选：

• 统计信息陈旧度是核心判断标准，通过sys.stats + sys.dm_db_stats_properties获取数据：
  1. 大表（行数≥1000万）的默认自动更新统计触发阈值为总行数20%+500行，20亿行规模的表需要修改4亿行才会触发自动更新，完全无法满足时效性要求，可以自定义触发阈值，比如行修改量占总行数1%~3%就标记为待更新
  2. 检查上次统计信息更新的采样率，如果采样率低于30%（可根据业务查询敏感度调整），且该表关联大量Join、聚合类查询，就需要执行Full Scan更新
• 可结合sys.dm_db_index_usage_stats的使用数据做优先级排序，减少不必要的Full Scan操作：
  1. 过去24小时内user_seeks/user_scans/user_lookups总和越高的表优先级越高，无任何用户访问的表可以延后更新或仅用默认采样率更新
  2. user_updates数值越高说明写入越频繁，统计信息陈旧速度越快，需要提高检查频率
• 额外可结合执行计划数据筛选：抓取sys.dm_exec_query_stats中高逻辑读、基数估算错误（估算行数与实际行数差10倍以上）的查询，关联其涉及的表，这类表优先执行Full Scan更新

可用查询脚本 #

1. 筛选大表中统计信息陈旧/采样率不足的对象 #

SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    c.name AS ColumnName,
    s.name AS StatsName,
    sp.rows AS TotalRows,
    sp.rows_sampled AS SampledRows,
    sp.modification_counter AS ModifiedRows,
    CAST((sp.modification_counter * 1.0 / sp.rows) * 100 AS DECIMAL(5,2)) AS ModifiedPercent,
    CAST((sp.rows_sampled * 1.0 / sp.rows) * 100 AS DECIMAL(5,2)) AS SamplePercent
FROM sys.stats s
JOIN sys.columns c ON s.object_id = c.object_id AND c.column_id = s.stats_column_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE 
    sp.rows > 10000000 -- 仅筛选千万行以上大表，可按需调整
    AND sp.rows > 0
    AND (
        (sp.modification_counter * 1.0 / sp.rows) > 0.02 -- 修改量超过2%，可按需调整
        OR (sp.rows_sampled * 1.0 / sp.rows) < 0.3 -- 上次采样率低于30%，可按需调整
    )
ORDER BY ModifiedPercent DESC, SamplePercent ASC

2. 结合索引访问数据排更新优先级 #

SELECT 
    OBJECT_NAME(ius.object_id) AS TableName,
    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TotalReadTimes,
    SUM(ius.user_updates) AS TotalWriteTimes
FROM sys.dm_db_index_usage_stats ius
WHERE database_id = DB_ID()
AND last_user_seek >= DATEADD(HOUR, -24, GETDATE()) -- 取过去24小时有访问的表，可按需调整
GROUP BY ius.object_id
ORDER BY TotalReadTimes DESC, TotalWriteTimes DESC

补充优化建议 #

你当前使用的SQL Server 2019即使兼容级别为130，也可以开启自适应统计信息更新功能，降低大表统计自动更新的触发阈值。另外无需全库全表执行Full Scan，仅给符合筛选条件、关联高频复杂查询的表做Full Scan，其余表用默认采样率或30%采样率更新即可，通常可将任务耗时压缩到原有耗时的1/3以内。

内容的提问来源于stack exchange，提问作者kyletme