Как я могу отслеживать и находить неиспользуемые индексы в базе данных SQL

Question

Я хотел бы отслеживать использование индексов для базы данных SQL, чтобы найти неиспользуемые индексы, а затем отбросить их. Как наиболее эффективно отслеживать использование индекса? И какие сценарии могут быть полезны?

(Мне известен этот вопрос об идентификации неиспользуемых объектов, но это относится только к текущему запуску сервера sql. Я хотел бы отслеживать использование индекса в течение определенного периода времени...)

Answer 1

В настоящее время (по состоянию на SQL Server 2005 - 2008) информация статистики индекса SQL хранится только в памяти, поэтому вам придется выполнять часть работы самостоятельно, если вы хотите, чтобы она сохранялась при перезапусках и отключениях базы данных.

Обычно я создаю задание, которое запускается каждый день и делает снимок информации, найденной в sys.dm_db_index_usage_stats таблицу, в пользовательскую таблицу, которую я создаю для рассматриваемой базы данных.

Похоже, что это работает очень хорошо до будущей версии SQL, которая будет поддерживать постоянную статистику использования индекса.

Answer 2

Это интересный вопрос. Я работал над этим же вопросом на прошлой неделе. Существует системная таблица dm_db_index_usage_stats, которая содержит статистику использования по индексам.

Индексы, которые никогда не появляются в таблице статистики использования

Однако многие индексы никогда не появляются в этой таблице вообще. В запросе, опубликованном Дэвидом Андресом, перечислены все индексы для этого случая. Я немного обновил его, чтобы игнорировать первичные ключи, которые, вероятно, не следует удалять, даже если они никогда не используются. Я также присоединился к таблице 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".

Answer 3

Я подправил запросы Джона Паске здесь: определение неиспользуемых индексов в базе данных SQL Server для возврата индексов, использованных 10 или менее раз, объединение результатов, которых нет в таблицах статистики использования, исключение индексов кучи и уникальных ограничений или индексов первичного ключа и, наконец, исключить индексы с нуля страниц.

Будьте осторожны с результатами этого запроса - лучше всего использовать в производстве, где индексы фактически используются так, как вы ожидаете. Если вы выполняете запрос к базе данных с перестроенными или удаленными / воссозданными индексами или с недавней резервной копией базы данных, вы можете получить ложные срабатывания (индексы, которые обычно используются, но не из-за особых обстоятельств). Не безопасно использовать в тестовой среде или среде разработки, чтобы решить, следует ли удалять индексы. Как говорит Нарниан, этот запрос просто определяет кандидатов на удаление для вашего тщательного рассмотрения.

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

Answer 4

Вытащил этого щенка из http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/. Обратите внимание, что это работает на 2005 и выше. Ключ является JOIN к SYS.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

Answer 5

Вы должны взглянуть на Brent Ozars sp_BlitzIndex. Эта хранимая процедура перечисляет среди прочих неподдерживаемые индексы. В нем перечислены беспорядки в отчете. Для каждой записи дается URL, который объясняет, что искать и как решить проблему.