SQL Server查询缺失的索引

最新推荐文章于 2025-11-08 15:24:06 发布

原创最新推荐文章于 2025-11-08 15:24:06 发布 · 973 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #dba #后端

日积月累专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种SQL优化策略，通过计算索引优势并针对[bpatientinfo]表进行调整，旨在提高[uat_platform_hisbs].[dbo].[bpatientinfo]表的查询效率。方法包括分析缺失索引和平均用户成本，以实现更高效的数据库操作。

select * from (
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,
dbmigs.last_user_seek ,
dbmid.[statement] AS [Database.Schema.Table] ,
dbmid.equality_columns ,
dbmid.inequality_columns ,
dbmid.included_columns ,
dbmigs.unique_compiles ,
dbmigs.user_seeks ,
dbmigs.avg_total_user_cost ,
dbmigs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK ) ON dbmigs.group_handle = dbmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK ) ON dbmig.index_handle = dbmid.index_handle
WHERE dbmid.[database_id] = DB_ID()
) as temp --where [Database.Schema.Table]='[uat_platform_hisbs].[dbo].[bpatientinfo]'
ORDER BY index_advantage DESC;