Анализ статистики по индексам в базе данных SQL Server

Словно указатель в книге, индекс помогает быстро получить доступ к требуемым данным в таблице, соответствующим запросу. Таким образом, их использование позволяет обеспечить быстрый поиск, сбор и сортировку информации из базы данных без необходимости снова и снова просматривать все объекты.

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

Основные недостатки индексов:

требуется много места на дисковом пространстве и в оперативной памяти. Чем длиннее ключ, тем большего размера индекс и место для его хранения;
замедляется производительность системы (медленнее выполняются операции вставок, обновления либо удаления записей) из-за необходимости вносить изменения не только в саму таблицу, но и в созданные для нее индексы.

Поэтому при создании индексов рекомендуется придерживаться следующих принципов:

А чтобы предотвратить неожиданный рост необходимых индексов и переполнение хранилища базы данных, необходимо проводить регулярный мониторинг, выявление плохо используемых или неиспользуемых индексов и удаление их или замену на более оптимальные, если это необходимо. Кроме того, необходимо регулярно проводить обслуживание индексов, в частности реорганизация индекса и перестроение индекса для повышения производительности и снижения уровня потребления ресурсов.

К индексам, которые плохо используются, относятся те, которые имеют большое количество операций записи с небольшим количеством операций чтения и большое количество сканирований с небольшим количеством операций поиска. Также сюда можно отнести индексы, использующие больше пространства, чем сама таблица. Реорганизации или перестроения требуют индексы с высоким процентом фрагментации и низкой плотностью страниц.

На нашей платформе можно изучить базовую статистику по индексам на странице /diag во вкладке index stat.

Здесь можно найти информацию обо всех существующих индексах: название, таблица, размеры индекса и его таблицы, плотность страниц и процент фрагментации, а также поля индекса, его тип и данные о страницах и записях индекса. Дополнительно можно запустить процедуры add indexes и delete indexes, которые помогают определить, какие индексы лучше создать, а какие не нужны вовсе. Выводится информация об использовании индекса - seek, scan, lookup, update - остановимся на них чуть подробнее.

MS SQL SERVER использует три метода доступа к данным:

scan - оператор сканирования - ресурсоемкий, медленный процесс. Оператор сканирования пробегается по всем строкам источника. Этот оператор не может выбрать одну конкретную строку – ему всегда приходится сканировать весь набор данных.
seek - оператор поиска - он может работать только по кластерным и некластерным индексам. Ищет информацию по ключам индекса.
lookup - поиск по закладкам - ищет информацию по специальным меткам, которые SQL SERVER создает самостоятельно. Обращается к ключам, которые до этого уже были прочитаны либо оператором сканирования, либо оператором поиска.

Соответственно, колонки seek, scan, lookup выводят количество запросов, использующих эти операторы поиска. Колонка update выводит подсчет запросов на изменение данных командами insert, update, delete, merge.

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

Самый простой способ изучить индекс - воспользоваться возможностями SQL Server Management Studio:

Здесь можно узнать основную информацию об индексе, входящих в него столбцах и свойствах индекса.

Однако этот метод удобен только если заранее известно, какой индекс нас интересует. Разумеется, просматривать так каждый индекс для поиска “плохих” индексов будет крайне неудобно.

Второй способ сбора информации об индексах — это системная хранимая процедура sp_helpindex, отображающая все индексы интересующей таблицы:

USE AdventureWorks2012;  
GO  
EXEC sp_helpindex N'Sales.Customer';  
GO

Однако этот вариант тоже удобен лишь для изучения конкретной таблицы.

Чтобы оценить размеры индексов, удобно использовать функцию sp_spaceused. Например, такой скрипт выведет список индексов и занимаемое ими место:

CREATE TABLE #IxSizes(TableName nvarchar(128),
NumberOfRows varchar(50),ReservedSpace varchar(50),
TableDataSpace varchar(50),IndexSize varchar(50),
unused varchar(50))

EXEC sp_msforeachtable
'insert into #IxSizes exec sp_spaceused [?]'

SELECT TableName AS [Table], TableDataSpace AS [Table size],
IndexSize AS [Total indexes size]
FROM #IxSizes
ORDER BY TableName DESC

А для того, чтобы оценить общий размер всех индексов, можно воспользоваться таким кодом:

CREATE TABLE #IxSizes(database_name nvarchar(128),
database_size varchar(50),[unallocated space] varchar(50),
reserved varchar(50), data varchar(50), index_size varchar(50),
unused varchar(50))

EXEC sp_msforeachDB
'USE [?]; INSERT INTO #IxSizes EXEC sp_spaceused @oneresultset = 1'

GO
SELECT database_name AS [Database], index_size AS [Total indexes size]
FROM #IxSizes

GO
DROP TABLE #IxSizes
GO

Для более глубокого изучения работы индексов в базе данных требуется тяжелая артиллерия. И SQL Server готов нам ее предоставить!

Удобный вариант - запросить представление динамического управления системой sys.indexes. Объединив его с другими представлениями, можно получить детальную информацию об индексах базы данных. Например, можно использовать такой запрос:

SELECT  Tab.name  Table_Name
,IX.name  Index_Name
,IX.type_desc Index_Type
,Col.name  Index_Column_Name
,IXC.is_included_column Is_Included_Column
        FROM  sys.indexes IX
        INNER JOIN sys.index_columns IXC  ON  IX.object_id   =   IXC.object_id AND  IX.index_id  =  IXC.index_id  
        INNER JOIN sys.columns Col   ON  IX.object_id   =   Col.object_id  AND IXC.column_id  =   Col.column_id
        INNER JOIN sys.tables Tab      ON  IX.object_id = Tab.object_id

или такой:

SELECT  Tab.name  Table_Name
,IX.name  Index_Name
,IX.type_desc Index_Type
,Col.name  Index_Column_Name
,IXC.is_included_column Is_Included_Column
,IX.fill_factor
,IX.is_disabled
,IX.is_primary_key
,IX.is_unique
 FROM  sys.indexes IX
        INNER JOIN sys.index_columns IXC  ON  IX.object_id   =   IXC.object_id AND  IX.index_id  =  IXC.index_id  
        INNER JOIN sys.columns Col   ON  IX.object_id   =   Col.object_id  AND IXC.column_id  =   Col.column_id
        INNER JOIN sys.tables Tab      ON  IX.object_id = Tab.object_id

Предыдущие чтения дают вам хорошее представление об использовании индексов базы данных, но нам нужно копнуть глубже, прежде чем принимать решение об удалении или замене индекса. Можно использовать предыдущие результаты в сочетании с результатом функции динамического управления sys.dm_db_index_physical_stats, чтобы иметь полное представление об использовании индекса. Динамическое представление sys.dm_db_index_physical_stats возвращает информацию об операциях ввода-вывода более низкого уровня, таких как операции INSERT, UPDATE и DELETE, выполненные для этого индекса для каждой секции таблицы с момента последнего перезапуска службы SQL Server.

SELECT OBJECT_NAME(IXOS.OBJECT_ID)  Table_Name
    ,IX.name  Index_Name
  ,IX.type_desc Index_Type
  ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
    ,IXOS.LEAF_INSERT_COUNT NumOfInserts
    ,IXOS.LEAF_UPDATE_COUNT NumOfupdates
    ,IXOS.LEAF_DELETE_COUNT NumOfDeletes
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) IXOS
INNER JOIN SYS.INDEXES AS IX ON IX.OBJECT_ID = IXOS.OBJECT_ID AND IX.INDEX_ID =    IXOS.INDEX_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE  OBJECTPROPERTY(IX.[OBJECT_ID],'IsUserTable') = 1
GROUP BY OBJECT_NAME(IXOS.OBJECT_ID), IX.name, IX.type_desc,IXOS.LEAF_INSERT_COUNT, IXOS.LEAF_UPDATE_COUNT,IXOS.LEAF_DELETE_COUNT

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

SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name
  ,IX.name AS Index_Name
  ,IX.type_desc Index_Type
  ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
  ,IXUS.user_seeks AS NumOfSeeks
  ,IXUS.user_scans AS NumOfScans
  ,IXUS.user_lookups AS NumOfLookups
  ,IXUS.user_updates AS NumOfUpdates
  ,IXUS.last_user_seek AS LastSeek
  ,IXUS.last_user_scan AS LastScan
  ,IXUS.last_user_lookup AS LastLookup
  ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1
GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

Ну и в заключение, SQL Server предоставляет нам два встроенных отчета, которые помогают нам отслеживать фрагментацию индексов базы данных, статистику использования индекса и физическую статистику индекса. Эти стандартные отчеты используют ранее описанные DMO, а данные отчетов также будут обновляться при перезапуске службы SQL Server. На скрине ниже показано как можно запросить эти отчеты.

Первый отчет — это отчет «Статистика использования индекса», который показывает статистику о количестве сканирований, поисковых запросов, обновлений и поисковых запросов с последней датой для каждой операции, полученной путем запроса sys.dm_db_index_usage_stats.

Второй отчет — это отчет «Физическая статистика индекса», который возвращает статистику о разделах индекса, проценте фрагментации и количестве страниц в каждом разделе индекса. В этом отчете также содержатся рекомендации по перестройке или реорганизации индекса в зависимости от процента фрагментации индекса. Рекомендация, содержащаяся в этом отчете, не учитывает размер таблицы. Если вы попытаетесь перестроить индекс для небольшой таблицы, вы все равно можете получить ту же рекомендацию из этого отчета.

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

SELECT create_date
* *, DATEDIFF (dd, create_date,
   GETDATE ()) AS days_metadata
FROM sys.databases
WHERE name = 'tempdb'.
--или

SELECT login_time
* *, DATEDIFF (dd, login_time,
   GETDATE ()) AS days_metadata
FROM sys.sysprocesses
WHERE spid = 1

Любой вариант должен дать один и тот же результат (в минутах).

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

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