当前位置: 首页 >>数据库 >>MSSQL >>如何知道缺失哪些SQL索引?让我们来告诉您

如何知道缺失哪些SQL索引?让我们来告诉您

时间:2020/8/4 8:51:00 【admin】

如何知道缺失哪些SQL索引?让我们来告诉您…


开启或关闭SQL的IO数(页读/写)

Set Statistics io on

Set Statistics io off


缺失索引的SQL查询语句

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;


扩充阅读

CopyRight:2007-2018 语言吧 备案ICP:湘ICP备09009000号-15 http://www.yuyanba.com

分享按钮