SQL Server 查询缺失索引并自动生成创建语句

获取缺失索引及创建语句的核心逻辑

在 SQL Server 环境中，当查询计划中出现“Missing Index”警告时，通常意味着数据库引擎认为某个索引能显著提升查询性能。虽然 Management Studio 的图形界面可以提供部分建议，但通过 T-SQL 脚本可以直接获取更量化、可操作的索引创建命令。核心思路是关联以下三个动态管理视图：

• sys.dm_db_missing_index_details：包含每个缺失索引的具体定义，如列名和包含列。
• sys.dm_db_missing_index_groups：将具体的索引细节与索引组进行映射。
• sys.dm_db_missing_index_group_stats：记录每个索引组的统计信息，如用户查找次数、扫描次数以及平均节省的成本。

操作步骤与分析

第1步：执行诊断查询脚本

目的是筛选出对数据库整体负载影响较大且有明确改善空间的缺失索引。以下脚本会计算一个综合收益指标（Improvement Measure），公式结合了平均节省成本、用户影响比例以及查询频率（查找+扫描）。只有当这个值大于 10 时，才认为值得添加索引。

1. 打开 SSMS (SQL Server Management Studio) 或任何支持 T-SQL 的工具。
2. 连接到目标数据库实例。
3. 新建查询窗口，粘贴并执行以下代码：

SELECT 
 migs.avg_total_user_cost*(migs.avg_user_impact/ 100.0) *(migs.user_seeks + migs.user_scans) AS improvement_measure,
 'CREATE INDEX[missing_index_' + CONVERT(varchar, mig.index_group_handle) + '_' + CONVERT(varchar, mid.index_handle)
 + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL(mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL(mid.inequality_columns, '')
 + ')'
 + ISNULL(' INCLUDE (' + mid.included_columns+ ')', '') AS create_index_statement,
 migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle= mig.index_group_handle
INNER 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) > 10
ORDER BY migs.avg_total_user_cost* migs.avg_user_impact*(migs.user_seeks + migs.user_scans) DESC

第2步：解读结果并提取 SQL

查询结果中的第一列为 improvement_measure，数值越大代表该索引带来的性能提升潜力越高。第二列 create_index_statement 则拼接了完整的建表语句。DBA 可以复制这些语句在测试环境中验证效果，确认无误后在生产环境执行。

第3步：注意事项与风险提示

• 权衡利弊：添加索引虽然加速查询，但会增加写入（Insert/Update/Delete）的开销和存储空间消耗。建议在低峰期或非核心业务时间段进行索引变更。
• 验证需求：生成的语句基于历史查询统计，若业务逻辑发生剧烈变化，需重新评估。
• 权限检查：执行该查询需要具有查看服务器状态视图的权限，通常 VIEW SERVER STATE 权限或数据库管理员角色可用。

简要总结

上述查询方法是利用 SQL Server 内部 DMV 机制进行自助式性能优化的有效手段。它不仅能发现潜在的索引缺口，还能自动化生成标准化的 DDL 语句，大幅减少人工排查时间。通过以上步骤，您可以快速定位关键表的索引缺失问题并实施改进。

以上就是 SQL Server 查询并自动创建缺失索引的详细内容，更多关于 SQL Server 性能优化资料请关注本站其它相关文章！