logo标签列表

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

sql-serverindexingmaintenance-plan
11

我希望监控SQL数据库中的索引使用情况，以找到未使用的索引并将其删除。如何最有效地监控索引使用情况？哪些脚本可能有用？

我知道这个关于识别未使用对象的问题，但它仅适用于 SQL 服务器的当前运行。我希望能够长时间监控索引使用情况。

5个回答
9
这是一个有趣的问题。我在过去的一周里一直在研究这个问题。有一个系统表叫做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数据库中识别未使用的索引。

7

目前（截至SQL Server 2005-2008），SQL索引统计信息仅保留在内存中，因此如果您希望在重新启动和数据库分离时保留这些信息，则需要自己执行部分工作。

我通常会创建一个作业，每天运行一次，并将sys.dm_db_index_usage_stats表中找到的信息快照，存储到为相关数据库创建的自定义表中。

直到将来某个支持持久化索引使用统计信息的SQL版本出现，这种方法似乎是有效的。

3
从http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/中获取了这个代码。请注意，此代码适用于2005及以上版本。关键在于将SYS.DM_DB_INDEX_USAGE_STATS系统表连接到JOIN上。
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
3
我在这里修改了John Pasquet的查询：在SQL Server数据库中识别未使用的索引，返回使用10次或更少的索引，联合不在使用统计表中的结果，排除堆索引和唯一约束或主键索引，最后排除零页的索引。
请注意此查询结果 - 最好在生产环境中使用，因为索引实际上是按照您的期望使用的。如果您在已重建或删除/重新创建索引的数据库上查询或在最近的数据库备份上查询，则可能会出现虚假阳性（通常会使用但由于特殊情况而未使用的索引）。不适用于测试或开发环境以决定是否删除索引。正如Narnian所说，此查询仅鉴定需要仔细考虑删除的候选索引。
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
1
你应该看一下Brent Ozars的sp_BlitzIndex。这个存储过程列出了未使用的索引等内容。它会在报告中列出这些问题。对于每个条目，都提供了一个URL，其中解释了需要查找和如何处理该问题的内容。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接