如何监控和查找 SQL 数据库中未使用的索引

我想监视 SQL 数据库的索引使用情况，以便找到未使用的索引，然后删除它们。如何最有效地监控索引使用情况？哪些脚本可能有用？

我知道这个关于识别未使用的对象的问题 https://stackoverflow.com/questions/290773/identifying-unused-objects-in-microsoft-sql-server-2005，但这仅适用于 SQL Server 的当前运行。我想监控一段时间内的索引使用情况。

这是个有趣的问题。过去一周我一直在研究同样的问题。有一个名为 dm_db_index_usage_stats 的系统表，其中包含索引的使用情况统计信息。

从未出现在使用统计表中的索引

然而，许多索引根本不会出现在该表中。 David Andres 发布的查询列出了此案例的所有索引。我对其进行了一些更新以忽略主键，即使它们从未使用过，也可能不应该删除它们。我还加入了 dm_db_index_physical_stats 表以获取其他信息，包括页计数、总索引大小和碎片百分比。有趣的是，此查询返回的索引似乎没有显示在索引使用情况统计的 SQL 报告中。

DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

SELECT  Databases.Name AS [Database],
        Objects.NAME AS [Table],
        Indexes.NAME AS [Index],
        Indexes.INDEX_ID,
        PhysicalStats.page_count as [Page Count],
        CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
        CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
FROM SYS.INDEXES Indexes
    INNER JOIN SYS.OBJECTS Objects ON Indexes.OBJECT_ID = Objects.OBJECT_ID
    LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats
        on PhysicalStats.object_id = Indexes.object_id and PhysicalStats.index_id = indexes.index_id
    INNER JOIN sys.databases Databases
        ON Databases.database_id = PhysicalStats.database_id
WHERE OBJECTPROPERTY(Objects.OBJECT_ID,'IsUserTable') = 1
    AND Indexes.type = 2    -- Nonclustered indexes
    AND   Indexes.INDEX_ID NOT IN (
            SELECT UsageStats.INDEX_ID
            FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats
            WHERE UsageStats.OBJECT_ID = Indexes.OBJECT_ID
                AND   Indexes.INDEX_ID = UsageStats.INDEX_ID
                AND   DATABASE_ID = @dbid)
ORDER BY PhysicalStats.page_count DESC,
         Objects.NAME,
         Indexes.INDEX_ID,
         Indexes.NAME ASC

确实出现在使用情况统计表中但从未使用过的索引

还有其他索引确实出现在 dm_db_index_usage_stats 表中，但从未用于用户查找、扫描或查找。该查询将识别属于该类别的索引。顺便说一句，与从其他查询返回的索引不同，此查询中返回的索引可以通过索引使用统计信息在 SQL 报告上进行验证。

我添加了“最小页数”，使我能够首先关注并删除占用大量存储空间的未使用索引。

DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500

SELECT  Databases.name AS [Database], 
        Indexes.name AS [Index],
        Objects.Name AS [Table],                    
        PhysicalStats.page_count as [Page Count],
        CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)],
        CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)],
        ParititionStats.row_count AS [Row Count],
        CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size/Row (Bytes)]
FROM sys.dm_db_index_usage_stats UsageStats
    INNER JOIN sys.indexes Indexes
        ON Indexes.index_id = UsageStats.index_id
            AND Indexes.object_id = UsageStats.object_id
    INNER JOIN sys.objects Objects
        ON Objects.object_id = UsageStats.object_id
    INNER JOIN SYS.databases Databases
        ON Databases.database_id = UsageStats.database_id       
    INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS PhysicalStats
        ON PhysicalStats.index_id = UsageStats.Index_id 
            and PhysicalStats.object_id = UsageStats.object_id
    INNER JOIN SYS.dm_db_partition_stats ParititionStats
        ON ParititionStats.index_id = UsageStats.index_id
            and ParititionStats.object_id = UsageStats.object_id        
WHERE UsageStats.user_scans = 0
    AND UsageStats.user_seeks = 0
    AND UsageStats.user_lookups = 0
    AND PhysicalStats.page_count > @MinimumPageCount    -- ignore indexes with less than 500 pages of memory
    AND Indexes.type_desc != 'CLUSTERED'                -- Exclude primary keys, which should not be removed    
ORDER BY [Page Count] DESC

我希望这有帮助。

最后的想法

当然，一旦索引被确定为候选人对于移除，仍应仔细考虑，以确保这样做是一个好的决定。

有关更多信息，请参阅识别 SQL Server 数据库中未使用的索引 http://www.foliotek.com/devblog/identifying-unused-indexes-in-a-sql-server-database/