当前位置: 首页 >>数据库 >>MSSQL >>MsSql Server 使用sql脚本查询缺失和无用的索引的方法分享

MsSql Server 使用sql脚本查询缺失和无用的索引的方法分享

时间:2020/8/28 10:12:00 【admin】

MsSql Server 使用sql脚本查询缺失和无用的索引的方法分享…

下文讲述sqlserver中使用sql脚本查询缺失和无用的索引的方法分享,如下所示:

实验环境:sql server 2008 R2

实现思路:

通过查询系统函数 dm_db_missing_index_details dm_db_index_usage_stats 等系统动态函数检索出未使用的索引和使用异常的索引

--缺失的索引

SELECT avg_total_user_cost * avg_user_impact * ( user_scans + user_seeks ) AS a ,

last_user_seek ,

last_user_scan ,

[statement] AS [对象名称] ,

'CREATE INDEX [IDX_' + CONVERT(VARCHAR(32), GS.group_handle) + '_'

+ CONVERT(VARCHAR(32), D.index_handle) + '_'

+ REPLACE(REPLACE(REPLACE([STATEMENT], ']', ''), '[', ''), '.', '')

+ ']' + ' ON ' + [statement] + ' (' + ISNULL(equality_columns, '')

+ CASE WHEN equality_columns IS NOT NULL

AND inequality_columns IS NOT NULL THEN ','

ELSE ''

END + ISNULL(inequality_columns, '') + ')' + ISNULL(' INCLUDE ('

+ included_columns

+ ')', '') AS [生成创建索引脚本]

FROM sys.dm_db_missing_index_groups AS G

INNER JOIN sys.dm_db_missing_index_group_stats AS GS ON G.index_group_handle = GS.group_handle

INNER JOIN sys.dm_db_missing_index_details AS D ON G.index_handle = D.index_handle

ORDER BY statement,a desc



--无用的索引

SELECT ind.index_id ,

obj.name AS [表名] ,

ind.name AS [索引名称] ,

ind.type_desc ,

indUsage.user_seeks ,

indUsage.user_scans ,

indUsage.user_lookups ,

indUsage.user_updates ,

indUsage.last_system_seek ,

indUsage.last_user_scan ,

'drop index [' + ind.name + '] ON [' + obj.name + ']' AS DropIndexCommand

FROM sys.indexes AS ind

INNER JOIN sys.objects AS obj ON ind.object_id = obj.object_id

LEFT JOIN sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id

AND ind.index_id = indUsage.index_id

WHERE ind.type_desc <> 'HEAP'

AND obj.type <> 'S'

AND OBJECTPROPERTY(obj.object_id, 'isusertable') = 1

AND ( ISNULL(indUsage.user_seeks, 0) = 0

AND ISNULL(indUsage.user_scans, 0) = 0

AND ISNULL(indUsage.user_lookups, 0) = 0

)

ORDER BY obj.name ,

ind.name


相关文章

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

分享按钮