SQL Server: Базы данных и индексы

Microsoft логически разделяет объекты управления БД (DMO) на уровне базы данных и файлов на две категории:

• Относящиеся к базам данных Эти объекты содержат административные представления (DMV), которые позволяют исследовать таблицы и страницы индексов и число строк данной БД, а также выделение страниц на уровне файла. Несколько DMV служат для анализа использования базы данных TempDB.
• Относящиеся к индексам Сюда относятся представления DMV, связанные с индексами, их характеристиками, с тем как индексы используются, они также помогают определять, какие индексы подойдут для ваших задач.

Имена всех представлений в этих двух категориях начинаются с "sys.dm_db_". Эти типы представлений DMV помогают определить эффективную стратегию индексирования, так как это один из лучших способов обеспечения, чтобы самые важные и часто исполняемые запросы могли читать нужные им данные упорядоченными образом и не создавать лишнюю нагрузку на подсистему ввода/вывода. Определение правильного баланса между слишком большим и слишком малым количеством индексов и реализация "правильного" набора индексов исключительно важны для обеспечения максимальной производительности SQL Server.

Нужно также следить за базой данных TempDB. TempDB - глобальный ресурс, хранящий временные данные пользователя и внутренних объектов всех пользователей, подключенных к данному экземпляру SQL Server. Сюда, в частности, входят внутренние рабочие таблицы, в которых хранятся результаты курсоров, а также пользовательские объекты, такие как временные таблицы и табличные переменные.

Поиск отсутствующих индексов

Для обнаружения индексов, которых не хватает в базе данных, применяют три тесно связанных представления DMV. Первое, sys.dm_db_missing_index_group_stats, описывается следующим образом:

"Возвращает сводку сведений о группах отсутствующих индексов, за исключением пространственных индексов. Сведения, возвращаемые представлением sys.dm_db_missing_index_group_stats, обновляются при каждом выполнении запроса, а не при каждой компиляции или повторной компиляции запроса. Статистика использования не сохраняется и хранится только до перезапуска SQL Server. Администраторы базы данных должны периодически делать резервные копии сведений об отсутствующих индексах, если необходимо сохранить статистику использования после перезагрузки сервера".

А вот описание второго, sys.dm_db_missing_index_groups:

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

Это в сущности таблица соединения sys.dm_db_missing_index_group_stats и третьего представления DMV, sys.dm_db_missing_index_details, которое описывается так:

"Возвращает подробные сведения об отсутствующих индексах, за исключением пространственных индексов".

Соединяя эти три представления, получаем полезный запрос, позволяющий выяснить, каких индексов не хватает (рис. 1).

Рис. 1. Выявление индексов, которые могут быть полезны в базе данных

-- Missing Indexes in current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
AS [index_advantage] ,
migs.last_user_seek ,
mid.[statement] AS [Database.Schema.Table] ,
mid.equality_columns ,
mid.inequality_columns ,
mid.included_columns , migs.unique_compiles ,
migs.user_seeks ,
migs.avg_total_user_cost ,
migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;

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

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

Нужно иметь в виду, что у этого подхода есть ряд ограничений. Во-первых, этот запрос не всегда указывает на наилучший порядок столбцов в индексе. Если в equality_columns or inequality_columns указывается несколько столбцов, нужно посмотреть на избирательность этих столбцов, чтобы определить оптимальный столбец в предполагаемом индексе. Во-вторых, не учитываются фильтруемые индексы, которые появились в SQL Server 2008.  Наконец, в общем случае запрос стремится предложить включенные столбцы и новые индексы.

Никогда не нужно слепо создавать все предлагаемые запросом индексы, особенно если это OLTP-система. Лучше внимательно проанализировать  результаты запроса и вручную отфильтровать результаты, которые не соответствуют вашей регулярной рабочей нагрузке.

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

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

Не забывайте, что системная хранимая процедура sp_helpindex не показывает информацию включенных столбцов. Это означает, что нужно воспользоваться заменой или просто создать предложение CREATE INDEX для существующих индексов.

Анализ использования индексов

Одно из самых полезных представлений DMV в категории индексации - dm_db_index_usage_stats, которое описывается следующим образом:

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

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

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

Первый из этих трех сценариев (рис. 2) представляет список всех таблиц кучи, кластеризованных и некластеризованных индексов, а также число операций чтения, записи и коэффициент заполнения каждого индекса.

Рис. 2. Эти сценарии позволяют выяснить, как используются индексы

--- Index Read/Write stats (all tables in current DB)
SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
i.name AS [IndexName] , i.index_id ,
user_seeks + user_scans + user_lookups AS [Reads] ,
user_updates AS [Writes] ,
i.type_desc AS [IndexType] ,
i.fill_factor AS [FillFactor]
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE OBJECTPROPERTY(s.[object_id], "IsUserTable") = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
writes DESC ,
reads DESC ;

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

В редакции SQL Server 2008 Enterprise Edition этот запрос позволяет решить, стоит ли включать сжатие данных (на уровне страниц или строк). Индекс с небольшим числом операций записи лучше подходит для сжатия данных, чем более активно изменяющийся индекс.

В следующем сценарии (рис. 3) sys.indexes и sys.objects используются для обнаружения в текущей базе данных таблиц и индексов, которые отсутствуют в результатах sys.dm_db_index_usage_stats.  Это означает, что в этих индексах не было операций чтения или записи с момента последнего запуска SQL Server или с момента закрытия или отключения текущей БД (выбирается самая поздняя дата).

Рис. 3. Обнаружение неиспользуемых индексов

-- List unused indexes
SELECT OBJECT_NAME(i.[object_id]) AS [Table Name] ,
i.name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE i.index_id NOT IN ( SELECT s.index_id
FROM sys.dm_db_index_usage_stats AS s
WHERE s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
AND database_id = DB_ID() )
AND o.[type] = "U"
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

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

В последнем запросе результаты sys.dm_db_index_usage_stats фильтруются по текущей базе данных (рис. 4). Сюда входят некластеризованные индексы, Это позволяет решить, оправдывает ли сохранение индекса затраты на его поддержку.

Рис. 4. Обнаружение редко используемых индексов

-- Possible Bad NC Indexes (writes > reads)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups )
AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK )
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], "IsUserTable") = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY [Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;

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

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

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