如何在sql数据库中监视和查找未使用的索引

I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful?

我希望监视sql数据库的索引使用情况，以便找到未使用的索引，然后删除它们。如何才能最有效地监控索引的使用?哪些脚本是有用的?

(I'm aware of this question about identifying unused objects, but this applies only to the current run of the sql server. I would like to monitor index usage over a period of time...)

(我知道关于标识未使用对象的问题，但这只适用于sql服务器的当前运行。我想在一段时间内监控索引的使用情况……

5 个解决方案

#1

Currently (as of SQL Server 2005 - 2008) the SQL index stats information is only kept in memory and so you have to do some of the work yourself if you would like to have that persisted across restarts and database detaches.

目前(从SQL Server 2005 - 2008年开始)SQL索引统计信息只保存在内存中，所以如果您希望在重新启动和数据库分离过程中持久化，您必须自己完成一些工作。

What I usually do, is I create a job that runs every day and takes a snapshot of the information found in the sys.dm_db_index_usage_stats table, into a custom table that I create for the database in question.

我通常做的是，创建一个每天运行的作业，并对sys中找到的信息进行快照。将dm_db_index_usage_stats表放在我为该数据库创建的自定义表中。

This seems to work pretty well until a future version of SQL which will support persistent index usage stats.

在将来的SQL版本中，它将支持持久的索引使用统计信息之前，这似乎是非常有效的。

#2

This is an interesting question. I've been working on this same question over the past week. There is a system table called dm_db_index_usage_stats that contains usage statistics on indexes.

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

Indexes That Never Appear in the Usage Statistics Table

从未出现在Usage Statistics表中的索引

However, many indexes never appear in this table at all. The query David Andres posted lists all indexes for this case. I've updated it a little bit to ignore primary keys, which probably shouldn't be deleted, even if they aren't ever used. I also joined on the dm_db_index_physical_stats table to get other information, including Page Count, Total Index Size, and the Fragmentation Percentage. An interesting note is that indexes that are returned by this query don't seem to show up in the SQL Report for Index Usage Statistics.

但是，许多索引根本没有出现在这个表中。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

Indexes That Do Appear in the Usage Statistics Table, But Are Never Used

在Usage Statistics表中出现但从未使用的索引

There are other indexes that do appear in the dm_db_index_usage_stats table, but which have never been used for user seeks, scans, or lookups. This query will identify indexes that fall into this category. Incidentally, unlike the indexes returned from the other query, the indexes returned in this query can be verified on the SQL Report by Index Usage Statistics.

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

I added a Minimum Page Count that allows me to initially focus on and remove unused indexes that are taking up a lot of storage.

我添加了一个最小的页面计数，它允许我最初关注并删除占用大量存储空间的未使用索引。

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

I hope this helps.

我希望这可以帮助。

Final Thought

最后认为

Of course, once indexes are identified as candidates for removal, careful consideration should still be employed to make sure it's a good decision to do so.

当然，一旦索引被确定为删除的候选者，那么仍然需要仔细考虑，以确保这是一个很好的决定。

For more information, see Identifying Unused Indexes in a SQL Server Database

有关更多信息，请参见在SQL Server数据库中标识未使用的索引

#3

Pulled this puppy off of http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/. Note that this works for 2005 and above. The key is the JOIN to the SYS.DM_DB_INDEX_USAGE_STATS system table.

把这只小狗从http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find unused-indexes of current database/上拉下来。注意，这适用于2005年及以上。关键是系统的连接。DM_DB_INDEX_USAGE_STATS系统表。

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
                    INDEXNAME = I.NAME,
                    I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND   I.INDEX_ID NOT IN (

SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND   I.INDEX_ID = S.INDEX_ID
AND   DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
         I.INDEX_ID,
         INDEXNAME ASC
GO

#4

I tweaked John Pasquet's queries here: Identifying Unused Indexes in a SQL Server Database to return indexes used 10 or less times, unioned the results that aren't in the usage stats tables, exclude heap indexes and unique constraints or primary key indexes, and finally to exclude indexes with zero pages.

我在这里修改了John Pasquet的查询:在SQL Server数据库中识别未使用的索引，以返回使用10次或更少时间的索引，将不存在于使用统计表中的结果，排除堆索引和惟一约束或主索引，最后排除索引为0的索引。

Be careful with the results of this query – it's best to use in production where indexes are actually getting used the way you would expect. If you query on a database with rebuilt or dropped/recreated indexes or on a recent database backup you could get false positives (indexes that normally would get used but aren't because of special circumstances). Not safe to use in test or dev environments to decide whether to drop indexes. As Narnian says, this query just identifies candidates for removal for your careful consideration.

小心这个查询的结果——最好是在使用索引的生产环境中使用。如果您使用重新构建或删除/重新创建的索引查询数据库，或者在最近的数据库备份中查询，那么您可能会得到假阳性结果(通常会使用但不是因为特殊情况而使用的索引)。在测试或开发环境中使用不安全，以决定是否删除索引。正如纳尔年所说，这个查询只是为你仔细考虑的删除候选人。

USE [DatabaseName]

DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500

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

-- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE

SELECT  
    Databases.name AS [Database]
    ,object_name(Indexes.object_id) AS [Table]
    ,Indexes.name AS [Index]
    ,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 Per Row (Bytes)]
    ,1 AS [Appears In Usage Stats Table]

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.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 <= 10
    AND UsageStats.user_seeks <= 10
    AND UsageStats.user_lookups <= 10

    -- exclude heap indexes
    AND Indexes.name IS NOT NULL

    -- ignore indexes with less than a certain number of pages of memory
    AND PhysicalStats.page_count > @MinimumPageCount

    -- Exclude primary keys, which should not be removed
    AND Indexes.is_primary_key = 0

    -- ignore unique constraints - those shouldn't be removed 
    AND Indexes.is_unique_constraint = 0 
    AND Indexes.is_unique = 0

UNION ALL 
(
    -- GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE

    SELECT  
        Databases.Name AS [Database]
        ,Objects.NAME AS [Table]
        ,Indexes.NAME AS [Index]
        ,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 (%)]
        ,-1 AS [Row Count]
        ,-1 AS [Index Size Per Row (Bytes)]
        ,0 AS [Appears In Usage Stats Table]

    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 
        Objects.type = 'U' -- Is User Table

        -- exclude heap indexes
        AND Indexes.name IS NOT NULL

        -- exclude empty tables
        AND PhysicalStats.page_count <> 0

        -- Exclude primary keys, which should not be removed
        AND Indexes.is_primary_key = 0

        -- ignore unique constraints - those shouldn't be removed 
        AND Indexes.is_unique_constraint = 0 
        AND Indexes.is_unique = 0

        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 [Table] ASC, [Total Index Size (MB)] DESC

#5

You should take a look at Brent Ozars sp_BlitzIndex. This stored procedure lists among others unsused indexes. It lists the disorders in a report. For each entry an URL is given which explains what to look for and how to handle the issue.

你应该看看布伦特·厄扎斯指数(Brent Ozars sp_BlitzIndex)。这个存储过程列出了其他未使用的索引。它在一份报告中列出了这些疾病。对于每个条目都给出一个URL，该URL解释要查找什么以及如何处理问题。