SQL Server中如何识别、查找未使用的索引(unused indexes）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server中如何识别、查找未使用的索引(unused indexes），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4627字，纯文字阅读大概需要7分钟。
内容图文
在SQL Server中，索引是优化SQL性能的一大法宝。但是由于各种原因，索引会被当做 “ 银弹 ” 滥用，一方面有些开发人员（甚至是部分数据库管理员）有一些陋习，不管三七二十一，总是根据所谓的"感觉"或 “ 经验 ” 先增加一些索引，而不管这些索引是否未被使用或是否合理。另外一方面在数据库的生命周期中，需求总是在变化，业务也在变化，有些当初创建的有效索引可能已经变成了unused index了。变成了数据库性能的累赘； 另外，部分数据库管理员其实很少清理索引（冗余索引，重复索引，未使用索引）。其实不管是出于性能考虑，还是数据库维护管理的需要，数据库中的未使用索引（unused index）都需要定期清理，因为这些未使用索引(unused index)不但不会提高查询性能，还会影响DML操作的性能、浪费存储空间等等。本文主要总结一下，如何找到识别、查找哪些未使用的索引（unused index)
如何找到未使用索引呢？ 在ORACLE数据库中提供了监控索引使用情况的功能。虽然在SQL Server中没有提供此类功能，但是提供了DMV视图 sys . dm_db_index_usage_stats ，关于这个视图，详细信息可以参考官方文档，下面仅仅介绍需要用到的几个字段
user_scans 用户查询执行的扫描次数。
user_seeks 用户查询执行的搜索次数。
user_lookups 用户查询执行的书签查找次数。
user_updates 通过用户查询执行的更新次数。这表示插入、 删除，更新的次数，而不是受影响的实际行数。
例如，如果你删除在一个语句中的 1000行，此计数递增 1
Number of updates by user queries. This includes Insert, Delete, and Updates representing
number of operations done not the actual rows affected. For example, if you delete 1000
rows in one statement, this count increments by 1
我们可以使用下面SQL语句查找当前数据库中的未使用索引(unused index)：
SELECT ‘SQL Server Instance Start with ‘ + CONVERT(VARCHAR(16),create_date,120) FROM sys.databases
WHERE database_id =2;
SELECT DB_NAME(diu.database_id) AS DatabaseName ,
s.name +‘.‘ +QUOTENAME(o.name) AS TableName ,
i.index_id AS IndexID ,
i.name AS IndexName ,
CASE WHEN i.is_unique =1 THEN ‘UNIQUE INDEX‘
ELSE ‘NOT UNIQUE INDEX‘ END AS IS_UNIQUE,
CASE WHEN i.is_disabled=1 THEN ‘DISABLE‘
ELSE ‘ENABLE‘ END AS IndexStatus,
o.create_date AS IndexCreated,
STATS_DATE(o.object_id,i.index_id) AS StatisticsUpdateDate,
diu.user_seeks AS UserSeek ,
diu.user_scans AS UserScans ,
diu.user_lookups AS UserLookups ,
diu.user_updates AS UserUpdates ,
p.TableRows ,
‘DROP INDEX ‘ + QUOTENAME(i.name)
+ ‘ ON ‘ + QUOTENAME(s.name) + ‘.‘
+ QUOTENAME(OBJECT_NAME(diu.object_id)) +‘;‘ AS ‘Drop Index Statement‘
FROM sys.dm_db_index_usage_stats diu
INNER JOIN sys.indexes i ON i.index_id = diu.index_id
AND diu.object_id = i.object_id
INNER JOIN sys.objects o ON diu.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN ( SELECT SUM(p.rows) TableRows ,
p.index_id ,
p.object_id
FROM sys.partitions p
GROUP BY p.index_id ,
p.object_id
) p ON p.index_id = diu.index_id
AND diu.object_id = p.object_id
WHERE OBJECTPROPERTY(diu.object_id, ‘IsUserTable‘) = 1
AND diu.database_id = DB_ID()
AND i.is_primary_key = 0 --排除主键索引
AND i.is_unique_constraint = 0 --排除唯一索引
AND diu.user_updates <> 0 --排除没有数据变化的索引
AND diu.user_lookups = 0
AND diu.user_seeks = 0
AND diu.user_scans = 0
AND i.name IS NOT NULL --排除那些没有任何索引的堆表
ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;
GO
需要注意的几点：
1：sys.dm_db_index_usage_stats返回索引的被使用的信息，但是这个DMV视图中的数据是自数据库服务启动以来累计收集的数据（只要重启SQL Server服务，该视图的计数器就初始化为空。 而且，当分离或关闭数据库时（例如，由于 AUTO_CLOSE 设置为 ON），便会删除与该数据库关联的所有记录。），所以，如果数据库只运行了几天，那么这个视图的数据有可能不是特别准确（例如，有些OLAP的批处理或作业，一个月才运行一次）。所以在判断分析前，一定要查看数据库服务已经运行多长时间了。一般合适的时间是一个月以上，最好是两个月以上。
2：sys.dm_db_index_usage_stats不返回有关内存列存储索引的信息
3：注意字段IndexCreated，如果索引是最近几天创建的，也要谨慎分析，不要急于删除。
4：注意条件里面有些字段过滤条件，其实都是包含一定业务意义的。
另外，上面脚本只能查询当前数据库的未使用索引，如果需要查询当前实例下的所有数据库，那么可以使用下面脚本
EXEC sp_MSforeachdb ‘USE [?] ;
SELECT DB_NAME(diu.database_id) AS DatabaseName ,
s.name +‘
i.index_id AS IndexID ,
i.name AS IndexName ,
CASE WHEN i.is_unique =1 THEN ‘‘UNIQUE INDEX‘ ‘
ELSE ‘‘NOT UNIQUE INDEX‘ ‘ END AS IS_UNIQUE,
CASE WHEN i.is_disabled=1 THEN ‘‘DISABLE‘ ‘
ELSE ‘‘ENABLE‘ ‘ END AS IndexStatus,
o.create_date AS IndexCreated,
STATS_DATE(o.object_id,i.index_id) AS StatisticsUpdateDate,
diu.user_seeks AS UserSeek ,
diu.user_scans AS UserScans ,
diu.user_lookups AS UserLookups ,
diu.user_updates AS UserUpdates ,
p.TableRows ,
‘‘DROP INDEX ‘ ‘ + QUOTENAME(i.name)
+ ‘‘ ON ‘ ‘ + QUOTENAME(s.name) + ‘ ‘.‘ ‘
+ QUOTENAME(OBJECT_NAME(diu.object_id)) +‘‘;‘ ‘ AS ‘ ‘Drop Index Statement‘ ‘
FROM sys.dm_db_index_usage_stats diu
INNER JOIN sys.indexes i ON i.index_id = diu.index_id
AND diu.object_id = i.object_id
INNER JOIN sys.objects o ON diu.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN ( SELECT SUM(p.rows) TableRows ,
p.index_id ,
p.object_id
FROM sys.partitions p
GROUP BY p.index_id ,
p.object_id
) p ON p.index_id = diu.index_id
AND diu.object_id = p.object_id
WHERE OBJECTPROPERTY(diu.object_id, ‘‘IsUserTable‘ ‘) = 1
AND diu.database_id = DB_ID()
AND i.is_primary_key = 0 --排除主键索引
AND i.is_unique_constraint = 0 --排除唯一索引
AND diu.user_updates <> 0 --排除没有数据变化的索引
AND diu.user_lookups = 0
AND diu.user_seeks = 0
AND diu.user_scans = 0
AND i.name is not null
ORDER BY ( diu.user_seeks + diu.user_scans + diu.user_lookups ) ASC,diu.user_updates DESC;
‘
内容总结
以上是互联网集市为您收集整理的SQL Server中如何识别、查找未使用的索引(unused indexes）全部内容，希望文章能够帮你解决SQL Server中如何识别、查找未使用的索引(unused indexes）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。