Индексы под всеми углами: Как определить, используется ли индекс?

Автор: Paul Randal, Indexes From Every Angle: How can you tell if an index is being used?

Когда бы я ни обсуждал обслуживание индексов, и в частности фрагментацию, я всегда подчёркиваю: «Прежде чем что-либо делать с фрагментацией, убедитесь, что индекс используется».

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

Можно даже зайти так далеко, что сказать: если некластерный индекс не используется, зачем он вообще нужен? Лишние некластерные индексы снижают производительность по ряду причин. Рассмотрим некластерный индекс (не фильтрованный) с именем IX_MyNCIndex на таблице MyTable.

Всякий раз, когда запись вставляется в MyTable, соответствующая запись вставляется и в IX_MyNCIndex. Это множество дополнительных операций ввода-вывода, дополнительных записей журнала, плюс, возможно, даже расщепление страницы.

Всякий раз, когда запись удаляется из MyTable, соответствующая запись в IX_MyNCIndex должна быть удалена. Снова дополнительные операции ввода-вывода и записи журнала.
Всякий раз, когда запись в MyTable обновляется:

• Если в MyTable есть кластерный индекс и значение ключа кластерного индекса изменяется, то соответствующая запись в IX_MyNCIndex должна быть обновлена. Снова дополнительные операции ввода-вывода и записи журнала.
• Если изменяется какое-либо из значений ключа некластерного индекса или изменяется значение любого из столбцов INCLUDE, то соответствующая запись в IX_MyNCIndex должна быть обновлена. Снова дополнительные операции ввода-вывода и записи журнала.
• Если на MyTable создаётся кластерный индекс, то IX_MyNCIndex должен быть перестроен, чтобы включить логические RID вместо физических RID кучи. Снова множество дополнительных операций ввода-вывода и записей журнала.

Это значительный объём дополнительных операций ввода-вывода и записей журнала для обслуживания каждого лишнего некластерного индекса.

Итак, как можно определить, используется ли индекс?

Начиная с SQL Server 2005 существует несколько различных способов, и тот, который я хочу обсудить в этой статье — это представление sys.dm_db_index_usage_stats.
Это динамическое административное представление (DMV) предоставляет информацию, отслеживаемую об использовании индексов (как следует из названия). Оно само по себе не генерирует никакой информации; оно просто возвращает данные из кэша внутри SQL Server. Этот кэш пуст при запуске экземпляра сервера и не сохраняется при перезапуске экземпляра. Все записи кэша для индексов в базе данных удаляются, когда эта база данных закрывается. Таким образом, кэш отслеживает информацию об использовании индексов с момента последнего открытия базы данных, в которую они входят (либо вручную, либо при запуске экземпляра).

Это продолжает сбивать людей с толку, поэтому я поясню: если в выводе DMV нет записи для интересующего вас индекса, значит, он не использовался с момента последнего запуска базы данных.

Кэш отслеживает следующую информацию для каждого индекса (для пользовательских запросов и системных запросов):

• Количество использований в операциях поиска (seek) (либо поиск одной строки, либо сканирование диапазона) вместе со временем последнего поиска.
• Количество использований в операциях просмотра (scan) (например, SELECT *) вместе со временем последнего просмотра.
• Количество использований в операциях поиска lookup (это означает переход по закладке — когда некластерный индекс не полностью покрывает запрос и дополнительные столбцы должны быть извлечены из строки базовой таблицы) вместе со временем последнего такого поиска.
• Количество использований в операциях обновления (update) (сюда входят вставки, обновления и удаления) вместе со временем последнего обновления.

Давайте посмотрим на его использование.

SELECT * FROM sys.dm_db_index_usage_stats;
GO

Вывод слишком широк для одного изображения, поэтому я разделил его на две части (больше я не буду публиковать вывод из этого DMV — я буду просто о нём рассказывать):

Если вы не только что перезапустили экземпляр, вы увидите множество строк вывода, отражающих всю активность индексов с момента запуска экземпляра/баз данных. Если вас интересует, используется ли конкретный индекс, вы можете отфильтровать вывод. Давайте сосредоточимся на определённой таблице: AdventureWorks.Person.Address

SELECT * FROM sys.dm_db_index_usage_stats
WHERE
    [database_id] = DB_ID (N'AdventureWorks')
    AND [OBJECT_ID] = OBJECT_ID (N'AdventureWorks.Person.Address');
GO

Скорее всего, вы ничего не увидите в выводе, если только не работали с этой таблицей. Давайте заставим использовать кластерный индекс в этой таблице и снова посмотрим на вывод DMV.

SELECT * FROM [AdventureWorks].[Person].[Address];
GO

SELECT * FROM sys.dm_db_index_usage_stats
WHERE
    [database_id] = DB_ID (N'AdventureWorks')
    AND [OBJECT_ID] = OBJECT_ID (N'AdventureWorks.Person.Address');
GO

Теперь появилась одна строка, показывающая просмотр кластерного индекса. Давайте сделаем кое-что ещё.

SELECT [StateProvinceID] FROM [AdventureWorks].[Person].[Address]
WHERE
    [StateProvinceID] > 4
    AND [StateProvinceId] < 15;
GO

SELECT * FROM sys.dm_db_index_usage_stats
WHERE
    [database_id] = DB_ID (N'AdventureWorks')
    AND [OBJECT_ID] = OBJECT_ID (N'AdventureWorks.Person.Address');
GO

И появилась ещё одна строка, показывающая поиск в одном из некластерных индексов таблицы.

Итак, легко посмотреть использование индексов для конкретных таблиц и индексов. Но как отслеживать это с течением времени? Это тоже просто — давайте посмотрим, как.

Сначала нам нужно создать собственную таблицу для хранения снимков состояния (snapshots) вывода DMV.

IF OBJECTPROPERTY (OBJECT_ID (N'master.dbo.MyIndexUsageStats'),'IsUserTable') = 1
    DROP TABLE [master].[dbo].[MyIndexUsageStats];
GO

SELECT GETDATE () AS [ExecutionTime], *
INTO [master].[dbo].[MyIndexUsageStats]
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = 0;
GO

Затем нам нужно сделать базовый снимок вывода DMV.

INSERT [master].[dbo].[MyIndexUsageStats]
SELECT GETDATE (), *
FROM sys.dm_db_index_usage_stats;
GO

А теперь смоделируем несколько операций и сделаем ещё один снимок DMV:

SELECT * FROM AdventureWorks].[Person].[Address];
GO

SELECT * FROM [AdventureWorks].[Person].[Address];
GO

SELECT [StateProvinceID] FROM [AdventureWorks].[Person].[Address]
WHERE
    [StateProvinceID] > 4
    AND [StateProvinceId] < 15;
GO

INSERT [master].[dbo].[MyIndexUsageStats]
SELECT GETDATE (), *
FROM sys.dm_db_index_usage_stats;
GO

И посмотрим на отфильтрованное содержимое нашей таблицы снимков:

SELECT * FROM [master].[dbo].[MyIndexUsageStats]
WHERE
    [database_id] = DB_ID (N'AdventureWorks')
    AND [OBJECT_ID] = OBJECT_ID (N'AdventureWorks.Person.Address');
GO

Вы должны увидеть четыре строки — две из базового снимка и две из итогового снимка. Если вы выполнили только приведённые выше инструкции, вы увидите, что счётчик user_scans для кластерного индекса увеличился на два, а счётчик user_seeks для некластерного индекса увеличился на один.

Это довольно простой пример того, как можно отслеживать использование индексов.

И вот в чём загвоздка: прежде чем принимать решение, можно ли удалить индекс, необходимо учесть полный бизнес-цикл. Индекс может использоваться только раз в месяц для формирования отчётов или для «исполнительского» запроса, поэтому убедитесь, что вы охватили все возможные моменты, когда индекс может быть использован. И даже если индекс не используется, убедитесь, что он не обеспечивает ограничение уникальности, поскольку оптимизатору запросов может потребоваться существование этого индекса.

Не удаляйте вслепую все индексы, которые не появляются в выводе — убедитесь, что они действительно не использовались.