Изменчивая статистика использования. Часть 1

Недавно я получил от читателя вопрос, относящийся к одной из моих излюбленных тем по Microsoft SQL Server — объектам динамического управления, Dynamic Management Objects (DMO). Вопрос был простой: «Какие рекомендации по настройке можно извлечь из sys.dm_db_index_usage_stats?»

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

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

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

Индексирующие объекты динамического управления

Sys.dm_db_index_physical_stats — лишь один из семи (на сегодня) объектов динамического управления:

dm_db_index_operational_stats;
dm_db_index_physical_stats;
dm_db_index_usage_stats;
dm_db_missing_index_columns;
dm_db_missing_index_details;
dm_db_missing_index_group_stats;
dm_db_missing_index_groups.

Существуют дополнительные «индексирующие» объекты DMO, но они связаны с полнотекстовым индексированием и выполняющейся в памяти OLTP (Hekaton) и выходят далеко за рамки данной статьи. В дальнейшем мы рассмотрим каждый из этих объектов — последние четыре, ориентированные на отсутствующие метаданные индекса, будут представлены вместе, так как по отдельности они бесполезны.

Изменчивая природа результатов, получаемых из dm_db_index_usage_stats

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

1. Использование tempdb create_date:

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

2. Использование session_id в login_time:

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

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

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

Сохранение результатов из sys.dm_db_index_usage_stats

Это одно из тех динамических административных представлений, которые я обычно сохраняю в физических таблицах, когда приходится иметь дело со средой с периодическими циклами активности, отличными от повседневных действий. В таких случаях полезно периодически сохранять статистику использования в физической таблице с помощью простой команды SELECT INTO с меткой даты. Я рекомендую задействовать необработанные данные из sys.dm_db_index_usage_stats, а не любые другие варианты, перечисленные в этой статье, потому что вы будете изменять базовый запрос в соответствии с конкретными требованиями к настройкам (как будет показано во второй части).

Структура sys.dm_db_index_usage_stats

Результаты из sys.dm_db_index_usage_stats разделяются следующим образом:

пользовательская или системная активность;
операции чтения или записи.

Операции чтения делятся на различные типы, доступные для индексации: поиски (seek), просмотры (scan) и уточняющие запросы (lookup). Операции записи представлены для индексации как обновления.

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

Transact-SQL для вызова sys.dm_db_index_usage_stats

Первый шаг к оптимизации нашего запроса с помощью sys.dm_db_index_usage_stats — проверить первоначальный запрос и возвращаемые им данные в форме столбцов, идентифицирующих индивидуальные индексы.

SELECT *
FROM sys.dm_db_index_usage_stats;

Результат показан на экране 1.

Экран 1. Первоначальный запрос

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

sys.objects;
sys.indexes.

Дополнительно используется системная функция DB_NAME () для преобразования столбца database_id в распознаваемое имя, а системная функция DB_ID () — для обратной операции преобразования имени базы данных в соответствующий database_id. Кроме того, я применяю фильтрацию с использованием предиката поиска по database_id. Ведь лишь изредка требуется анализировать использование индекса на всех базах данных экземпляра. Для целей этого экземпляра я заменил на тестовую базу данных, именуемую lifeboat (см. листинг 1).

Обратите внимание на кое-какие особенности нескольких первых столбцов при выполнении этого программного кода t-sql (см. экран 2).

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

Мы ограничили диапазон одной базой данных через предикат поиска ixUS.database_id и полное определение объектов для sys.objects и sys.indexes. Кроме того, теперь у нас есть распознаваемые имена для этих объектов. В тексте запроса вы увидите, что я твердо придерживаюсь полностью определенных имен. Абсолютно необходимо использовать полностью определенные имена, иначе вы получите недействительные результаты, поскольку object_id и index_id не уникальны в экземпляре, только в базе данных.

Другой вариант — сначала назначить контекст базы данных lifeboat с помощью синтаксиса USE , но я всегда предпочитал полностью определенные имена, поскольку, как и всем администраторам, мне пару раз приходилось направлять запросы к базе данных с неправильной областью.

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

database_id;
object_id;
index_id;
user_seeks;
user_scans;
user_lookups;
user_updates;
last_user_seek;
last_user_scan;
last_user_lookup;
last_user_update;
system_seeks;
system_scans;
system_lookups;
system_updates;
last_system_seek;
last_system_scan;
last_system_lookup;
last_system_update.

После начального анализа меня интересовали только столбцы, относящиеся к last_user|system_action, при наблюдении за малоиспользуемыми индексами. Поэтому теперь мы исключим эти столбцы и вернемся к ним в следующей статье. Аналогично, меня не волнует, как SQL Server обращается к этим индексам, поэтому я исключил все столбцы, основанные на действиях системы (system_seeks, system_scans и т. д.). Теперь, когда для идентификации используются удобные имена, можно исключить и столбцы database_id и object_id.

Столбец index_id полезен для идентификации типа индекса (0 = куча | 1 = кластеризованный | > 1 = некластеризованный).

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

Результат представлен на экране 3.

Экран 3. Проверка операций чтения

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

Добавления статистики чтения-записи и соответствующая сортировка

Мне совсем не нравится заниматься лишними математическими выкладками, хотя я и специализировался на прикладной математике в университете. Поэтому мне бы хотелось избежать переноса этих результатов в Microsoft Excel, где я выполняю основные вычисления, и получить значение отношения чтения-записи для индекса. Еще я хочу взглянуть на индексы, требующие трудоемкого обслуживания (результат — операции записи и затраты на перестроение и реорганизацию индексов), в сравнении с соответствующими преимуществами индексов при чтении (см. листинг 3).

Результаты исполнения кода представлены на экране 4.

Экран 4. Вычисление значения отношения чтение-запись

При малых масштабах трудно принимать какие-то решения. Но взглянем на результаты из производственной среды, на основании которых можно сделать выводы немедленно (имена базы данных, объектов и индексов изменены или удалены) (см. экран 5).

Экран 5. Пример результата выполнения в производственной среде

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

В первую очередь нужно взглянуть на верхние записи без операций чтения. Вероятно, я выполню вторичный запрос для последней операции записи в этих таблицах, чтобы определить их активность. Однако я не намерен принимать поспешных решений, отбрасывая что-либо на данном этапе.
Я оценил активность в кучах (таблицах без кластеризованных индексов). Вероятно, второй запрос будет выполнен, чтобы посмотреть на все действия с некластеризованными индексами и кучами, чтобы определить, есть ли потребность в кластеризованном индексе, а также исследовать кандидатов для ключа кластеризации. Кучи можно определить по значению index__name, равному NULL, а также значению index_id, равному 0.
Затем я начинаю анализировать любые таблицы со значительным числом индексов, чтобы выяснить, можно ли их сократить. Иногда полезно запустить этот запрос во второй раз, изменив предложение ORDER BY для сортировки сначала по object__name, а затем по index_id, чтобы исследовать результаты для этой метрики.
Если необходимо улучшить ключ кластеризации, я исследую таблицы, где активность чтения по ключу кластеризации выглядит низкой по сравнению с обновлениями. Этот случай (и как настроить запрос, чтобы упростить данный процесс) будет рассмотрен в следующей статье.

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

Листинг 1. Использование предиката поиска по database_id

SELECT DB_NAME(ixUS.database_id) AS database__name
* * , SO.name AS object__name
* * , SI.name AS index__name
* * , ixUS.*
FROM sys.dm_db_index_usage_stats AS ixUS
* * INNER JOIN lifeboat.sys.objects AS SO
* * * * ON SO.object_id = ixUS.object_id
* * INNER JOIN lifeboat.sys.indexes AS SI
* * * * ON SI.object_id = ixUS.object_id
* * * * * * AND SI.index_id = ixUS.index_id
WHERE ixUS.database_id = DB_ID('')
ORDER BY OBJECT_NAME(ixUS.object_id), ixUS.index_id;

Листинг 2. Проверка операций чтения

SELECT DB_NAME(ixUS.database_id) AS database__name
        , SO.name AS object__name
        , SI.name AS index__name
        , ixUS.index_id
        , ixUS.user_seeks
        , ixUS.user_scans
        , ixUS.user_lookups
        , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) AS total_reads
        , ixUS.user_updates AS total_writes
FROM sys.dm_db_index_usage_stats AS ixUS
        INNER JOIN lifeboat.sys.objects AS SO
                ON SO.object_id = ixUS.object_id
        INNER JOIN lifeboat.sys.indexes AS SI
                ON SI.object_id = ixUS.object_id
                        AND SI.index_id = ixUS.index_id
WHERE ixUS.database_id = DB_ID('')
ORDER BY OBJECT_NAME(ixUS.object_id), ixUS.index_id;

Листинг 3. Вычисление значения отношения чтение-запись (два варианта исполнения)

/*
Первоначальный вариант кода,
он заключен в комментарии
SELECT DB_NAME(ixUS.database_id) AS database__name
        , SO.name AS object__name
        , SI.name AS index__name
        , ixUS.index_id
        , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups)
                / IIF(ixUS.user_updates = 0, 1, ixUS.user_updates) AS [r_per_w]
        , ixUS.user_seeks
        , ixUS.user_scans
        , ixUS.user_lookups
        , (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) AS total_reads
        , ixUS.user_updates AS total_writes
FROM sys.dm_db_index_usage_stats AS ixUS
        INNER JOIN lifeboat.sys.objects AS SO
                ON SO.object_id = ixUS.object_id
        INNER JOIN lifeboat.sys.indexes AS SI
                ON SI.object_id = ixUS.object_id
                        AND SI.index_id = ixUS.index_id
WHERE ixUS.database_id = DB_ID(‘’)
ORDER BY 5
        , OBJECT_NAME(ixUS.object_id), ixUS.index_id;
*/

/*
--------------------------------------
Обновленный вариант кода, без JOINS и MAKE,
для совместимости с версиями SQL SERVER 2005- 2016
--------------------------------------
*/

SELECT
DB_NAME(ixUS.database_id) AS database__name
, OBJECT_SCHEMA_NAME(SI.object_id, ixUS.database_id) AS schema__Name
, OBJECT_NAME(SI.object_id, ixUS.database_id) AS object__name
, SI.name AS index__name
, ixUS.index_id
, CASE ixUS.user_updates
        WHEN NULL THEN (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups)
        WHEN 0 THEN (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups)
        ELSE
                (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) / ixUS.user_updates
        END AS [r_per_w]
, ixUS.user_seeks
, ixUS.user_scans
, ixUS.user_lookups
, (ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) AS total_reads
, ixUS.user_updates AS total_writes
FROM
sys.dm_db_index_usage_stats AS ixUS
INNER JOIN sys.indexes AS SI
ON SI.object_id = ixUS.object_id
AND SI.index_id = ixUS.index_id
WHERE ixUS.database_id = DB_ID()
ORDER BY [r_per_w]
        , OBJECT_NAME(ixUS.object_id, IxUS.database_id)
        , ixUS.index_id;