如何批量查询所有stored procs的missing indexes 无需逐个核验？

方案说明 #

你可以通过SQL Server原生的动态管理视图（DMV）组合查询，批量获取所有已运行过的存储过程对应的缺失索引建议，无需逐个核验存储过程逻辑。

注：缺失索引建议基于数据库历史运行统计生成，仅作为核查参考，无需直接落地创建。

前置注意事项 #

• 仅已经被实际执行过的存储过程会生成相关索引建议，从未执行的存储过程不会出现在查询结果中
• 建议结果按照性能提升预估占比、成本下降幅度排序，可优先关注高优先级的缺失索引项

批量查询缺失索引脚本 #

SELECT 
    OBJECT_NAME(p.object_id) AS 存储过程名称,
    d.statement AS 关联业务表名,
    d.equality_columns AS 等值查询推荐索引列,
    d.inequality_columns AS 范围查询推荐索引列,
    d.included_columns AS 建议包含列,
    s.avg_total_user_cost AS 新增索引后平均可降低的查询成本,
    s.avg_user_impact AS 新增索引后平均性能提升预估占比
FROM 
    sys.dm_exec_procedure_stats p
CROSS APPLY 
    sys.dm_exec_query_plan(p.plan_handle) qp
CROSS APPLY 
    sys.dm_exec_missing_index_groups g
INNER JOIN 
    sys.dm_exec_missing_index_group_stats s 
    ON s.group_handle = g.index_group_handle
INNER JOIN 
    sys.dm_exec_missing_index_details d 
    ON d.index_handle = g.index_handle
WHERE 
    p.database_id = DB_ID()
    AND d.database_id = DB_ID()
ORDER BY 
    s.avg_user_impact DESC, 
    s.avg_total_user_cost DESC

兼容SQL Server 2016语法检查补充 #

如果需要同步排查存储过程是否残留未适配2016标准的旧语法，可使用以下批量扫描脚本：

SELECT 
    name AS 存储过程名称,
    create_date AS 创建时间,
    modify_date AS 最后修改时间
FROM 
    sys.procedures
WHERE 
    OBJECT_DEFINITION(object_id) LIKE '%COMPUTE%'
    OR OBJECT_DEFINITION(object_id) LIKE '%*=%'
    OR OBJECT_DEFINITION(object_id) LIKE '%=*%'

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