SELECT
sale_date,
SUM(amount) AS total_amount
FROM fgedu.sales
WHERE sale_date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’
GROUP BY sale_date
ORDER BY sale_date;
GO

SET SHOWPLAN_XML OFF;
GO

— 步骤2：识别过期统计信息
— 查看过期统计信息
SELECT
t.name AS table_name,
s.name AS stats_name,
s.last_updated,
s.rows,
s.rows_sampled,
DATEDIFF(DAY, s.last_updated, GETDATE()) AS days_since_update
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE DATEDIFF(DAY, s.last_updated, GETDATE()) > 7
ORDER BY days_since_update DESC;
GO

— 步骤3：使用增量统计信息
— 启用增量统计信息
ALTER DATABASE fgedudb SET AUTO_UPDATE_STATISTICS_INCREMENTAL ON;
GO

— 为表启用增量统计信息
UPDATE STATISTICS fgedu.sales WITH INCREMENTAL = ON;
GO

— 步骤4：优化统计信息采样率
— 使用不同的采样率更新统计信息
UPDATE STATISTICS fgedu.sales WITH SAMPLE 10 PERCENT;
GO

UPDATE STATISTICS fgedu.sales WITH SAMPLE 50 PERCENT;
GO

UPDATE STATISTICS fgedu.sales WITH FULLSCAN;
GO

— 步骤5：监控统计信息相关的等待事件
— 查看统计信息相关的等待事件
SELECT
wait_type,
wait_time_ms,
waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE ‘%STATISTICS%’
ORDER BY wait_time_ms DESC;
GO

— 步骤6：使用统计信息建议
— 查看查询优化器的统计信息建议
SELECT
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
‘CREATE STATISTICS [STATS_’ + OBJECT_NAME(mid.object_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) +
CASE WHEN mid.inequality_columns IS NOT NULL THEN ‘_’ + REPLACE(REPLACE(REPLACE(mid.inequality_columns, ‘, ‘, ‘_’), ‘[‘, ”), ‘]’, ”) ELSE ” END +
‘]’ + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL(mid.equality_columns, ”) +
CASE WHEN mid.inequality_columns IS NOT NULL THEN ‘,’ + mid.inequality_columns ELSE ” END + ‘)’ AS create_statistics_statement
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 1000
ORDER BY improvement_measure DESC;
GO

执行结果：