SQL Server统计信息更新方法与最佳实践

在SQL Server中，更新统计信息是优化查询性能的重要维护任务。统计信息帮助查询优化器生成高效的执行计划。以下是更新统计信息的几种方法：

1. 自动更新统计信息

SQL Server默认启用自动统计信息更新：

sql

-- 检查数据库的自动统计信息设置
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on 
FROM sys.databases
WHERE name = 'YourDatabaseName';

2. 手动更新统计信息

更新特定表的统计信息

sql

-- 更新表的所有统计信息
UPDATE STATISTICS Sales.SalesOrderDetail;

-- 更新特定统计信息
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;

-- 使用完整扫描更新
UPDATE STATISTICS Sales.SalesOrderDetail WITH FULLSCAN;

-- 使用样本比例更新
UPDATE STATISTICS Sales.SalesOrderDetail WITH SAMPLE 50 PERCENT;

更新数据库中所有统计信息

sql

-- 更新当前数据库中所有统计信息
EXEC sp_updatestats;

-- 或者使用以下命令
UPDATE STATISTICS ALL;

3. 创建统计信息

sql

-- 创建单列统计信息
CREATE STATISTICS stats_ProductID 
ON Sales.SalesOrderDetail (ProductID);

-- 创建多列统计信息
CREATE STATISTICS stats_ProductID_OrderQty 
ON Sales.SalesOrderDetail (ProductID, OrderQty);

-- 创建带过滤条件的统计信息
CREATE STATISTICS stats_ActiveProducts 
ON Production.Product (ProductID)
WHERE DiscontinuedDate IS NULL;

4. 查看统计信息

sql

-- 查看表的统计信息
SELECT 
    name AS StatisticsName,
    auto_created,
    user_created,
    no_recompute,
    has_filter,
    filter_definition,
    last_updated
FROM sys.stats 
WHERE object_id = OBJECT_ID('Sales.SalesOrderDetail');

-- 查看统计信息详情
DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', 'AK_SalesOrderDetail_rowguid');

5. 维护脚本示例

sql

-- 检查过时的统计信息
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 1000  -- 修改计数阈值
ORDER BY sp.modification_counter DESC;

-- 自动更新过时的统计信息
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 
    'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + 
    '.' + QUOTENAME(OBJECT_NAME(s.object_id)) + 
    ' ' + QUOTENAME(s.name) + ';' + CHAR(13)
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 1000  -- 根据实际情况调整阈值
AND s.name NOT LIKE '_WA%'  -- 排除自动创建的统计信息

EXEC sp_executesql @sql;

6. 最佳实践

1. 更新频率：

   • 高变动表：每天或每周更新

   • 中等变动表：每周或每月更新

   • 低变动表：按需更新

2. 采样策略：

   • 小表：使用 FULLSCAN

   • 大表：使用适当采样比例（如 20-30%）

3. 维护计划：

sql

-- 示例维护任务
UPDATE STATISTICS Sales.SalesOrderHeader WITH FULLSCAN;
UPDATE STATISTICS Sales.SalesOrderDetail WITH SAMPLE 30 PERCENT;
EXEC sp_updatestats;  -- 更新剩余统计信息

注意事项

• 更新统计信息可能导致执行计划重新编译

• 在生产环境高峰期避免大量统计信息更新

• 监控 sys.dm_db_stats_properties 跟踪统计信息修改情况

• 考虑使用 Ola Hallengren 的维护解决方案进行自动化管理

定期更新统计信息可以显著提高查询性能，确保优化器基于准确的数据分布信息做出最佳决策。