5.1 查找未使用的索引

5.1.1查找未使用的索引概述

众所周知，索引对性能的提升有巨大作用。但是这是有代价的，索引需要磁盘空间来存放它的B-Tree，并且用于在每次DML执行后更新信息，所以应该在一定时期内检查有无无用索引。

业务周期根据实际情况而定。一些表和查询会每天频繁使用，但是一些类似报表功能可能是一月甚至一年执行一次，所以，除非必要，不然就让所有东西一直运行，知道你的业务周期完毕。这样会得到相对准确的信息。

5.1.2 需要使用的表与函数

 

sys.dm_db_index_usage_stats和sys.Indexes和sys.objects和OBJECTPROPERTY

 

返回不同类型索引操作的计数以及上次执行每种操作的时间。

 

sys.dm_db_usage_stats DMV，它显示出了哪些索引被使用以及它们是在被用户查询所使用还是仅仅共系统操作使用。伴随着查询的每一次执行，这个动态管理试图返回的列会随着查询语句中使用的执行计划的增加而增加。数据是在SQL Server运行的时候被收集的。这个dmv中数据仅被存储在内存中，并且不会被永久保存。所以当SQL Server实例停掉以后，数据也会丢失。你可以间歇性地获取并保存这些信息，便于以后对它们进行分析。

 

在索引上的操作被划分为用户类型及系统类型两种。用户类型倾向于SELECT，INSERT，DELETE以及UPDATE操作，或者更新统计信息。两种类别的语句在列中对比显示如下：

在索引上的查找(seek)操作 (user_seeks ). 我的理解索引查找的次数；官方的解释：通过用户查询执行的搜索次数。

在索引上的查阅(Lookup)操作 (user_lookups ). 我的索引扫描的次数；官方的解释：通过未使用的用户查询的扫描次数查找谓词。

在索引上的遍历操作(Scan) (user_scans ). 我的书签查找的次数；官方的解释：由用户查询执行的书签查找次数。

在索引上的更新(Update)操作 (user_updates ).

5.1.3 查询未时使用的索引

查看数据库启动时间

SELECT sqlserver_start_time  FROM sys.dm_os_sys_info;
SELECT login_time FROM sysprocesses WHERE spid= 1;
select crdate from master.sys.sysdatabases where name='tempdb';

 

执行以下语句

SELECT  ind.index_id ,
        obj.name AS TableName ,
        ind.name AS IndexName ,
        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

 

说明：

OBJECTPROPERTY(obj.object_id, 'isusertable') = 1 检查对象是否为表

obj.type <> 'S' 不为系统表

 

为了获得索引名、索引类型、表名，有必要关联sys.Indexes和sys.objects表。

通常情况下，当一个索引被使用了，那么在sys.dm_db_index_usage_stats上的user_seek、user_scan、或者user_lookup列上会有一些值。如果索引从来未被使用，那么这些值就为0，也就是没必要保留。

 

扩充信息：

在决定索引是否有效时，还需要结合你的专业只是。有些索引之所以没有统计数据是因为还没有到使用的周期，或者周期性地重启了服务器，在删除之前，要考虑：

1是否为主键或者唯一键，因为及时这部分没有数值，但是它们还是可以很好地保证数据的一致性。

2 唯一索引帮助优化器创建更有效的执行计划，及时这些索引未被使用，但是也提供了数据分布的相关信息。