В первой части статьи, опубликованной в Windows IT Pro/RE № 4 за 2016 год, был описан основной запрос, для которого должна быть возвращена важная информация из динамического административного представления (DMV) sys.dm_db_index_usage_stats. Во второй части мы рассмотрим, как использовать основной запрос для ответа на конкретные вопросы о применении индексов и выводах, которые можно сделать в результате корректировок, вносимых в основной запрос. Для тех, кто хочет перейти сразу к делу, без напоминания о базовой теории, в листинге 1 я привожу основную инструкцию t-sql из первой части статьи.
Динамическое административное представление sys.dm_db_index_usage_stats обеспечивает информацию об использовании для всех индексов и кучи для каждой базы данных. Если активность для определенной таблицы или индекса отсутствует, то эта информация не появляется в результатах из sys.dm_db_index_usage_stats. Возвращается информация, разделенная по активности пользователей и систем (или внутренних компонентов). Затем эта активность делится на операции чтения (сканирование, поиски и уточняющие запросы) и записи («обновления»). Также отслеживается, когда выполнялось последнее действие с индексом. Во всех дискуссиях о динамических административных объектах (DMO) поднимается вопрос о качестве информации, полученной через объекты DMO: оно такое же, как у метаданных, кэшированных после последнего перезапуска службы (с некоторыми исключениями).
Еще более важный вопрос, относящийся к конкретному DMV, касается информации об использовании, которая сбрасывается при перестроении индекса. Вам придется учитывать ее при анализе этих показателей. Однако реорганизация не влияет на выходные данные sys.dm_db_index_usage_stats.
От основ можно перейти к вопросам, которым посвящена данная статья.
Какие используются индексы и таблицы
В дополнение к столбцам, предоставляемым через sys.dm_db_index_usage_stats, я ввел столбцы набора результатов, в которых консолидированы различные типы операций чтения и вычисляемый столбец, чтобы получить метрику для сравнения операций чтения с операциями записи (r_per_w). Для наших индексов желательно, чтобы операции чтения преобладали над операциями записи. Это свидетельствует об эффективном использовании индексов и оправданности накладных расходов на обслуживание индекса через операции записи (дорогостоящие операции — кластерные и некластерные вставки, обновления и удаления). Индексы должны служить одной цели: ускорить чтение. Если вы не обнаруживаете высоких показателей чтения-записи ни в одном из этих запросов и сведения об активности, собранные за время, прошедшее после перезапуска службы, когда были сброшены кэшированные метаданные DMO, отражают вашу типичную транзакционную нагрузку, то вам следует подумать об удалении индекса.
Ответ на вопрос об индексах и таблицах можно дать на двух уровнях детализации: для конкретных индексов и табличных сведений. По очевидным причинам нас интересует индексная детализация, в частности следует сохранить индекс или, может быть, консолидировать похожие индексы в единый индекс. При детализации на уровне таблиц необходимо решить, нужна ли вообще таблица в базе данных. Она может указывать на изменения в способах работы пользователей, происходящих со временем, или изменения в продукте, в результате которых части схемы базы данных устарели и потому подлежат архивации или удалению.
Вопрос 1. Какие индексы используются в моей базе данных? Требуется выдать информацию с указанием в первую очередь индексов, наиболее часто используемых для операций чтения. Запрос представлен в листинге 2.
Все выходные данные будут выдаваться в Excel для удобства обработки результатов (см. экран 1). Я также исключил некоторые медианные («типичные») результаты, чтобы сосредоточиться на крайних значениях. Мне хочется обратить ваше внимание на столбец r_per_w (столбец F), показанный на экране 2. Вы обнаружите большой разброс значений. Более высокие значения указывают, что эти индексы используются для обслуживания операций чтения, а не записи в большей степени, чем приведенные в списке результатов. Ценность этой информации ограничена, так как необходимо учитывать число операций записи, и неясно, компенсируют ли преимущества чтения нагрузку, создаваемую операциями записи. Рассмотрим в качестве примера строки 23 и 24. Очевидно, соотношение чтения-записи для строки 23 выше, но одновременно для нее выполняется на 10 001 операцию записи больше для обслуживания 1966 операций чтения. Насколько это выгодно? Для ответа на этот вопрос потребуется A/B-тестирование, показывающее различие в производительности запросов, использующих индекс на строке 23, в зависимости от наличия или отсутствия этого индекса в таблице, в данном случае кластеризованного индекса для первичного ключа.
.jpg) |
| Экран 1. Опрашиваемые данные |
.jpg) |
| Экран 2. Выходные данные для вопроса 1 |
Этот запрос дает нам определение соотношений чтения-записи для действий, выполняемых с индексами и кучами.
Вопрос 2. Какие таблицы используются в моей базе данных? Требуется выдать информацию с указанием в первую очередь индексов, наиболее часто используемых для операций чтения. Запрос представлен в листинге 3.
Я озабочен не столько типами выполняемых операций чтения, сколько тем, вовлечены ли в них таблицы, и если они используются, то насколько искажены пропорции между операциями чтения и записи. Это достигается путем объединения database_id и object_id. Используется предложение GROUP BY, поэтому необходимо изменить синтаксис и применить удобные для агрегации функции, такие как SUM (), вместо использовавшихся прежде. Я скрыл на экране 3 столбец базы данных, чтобы нагляднее показать результаты.
.jpg) |
| Экран 3. Результаты ответа на вопрос 2 |
Этот запрос дает нам определение соотношения чтения-записи на уровне таблицы, что не только поможет определить, запрашиваются или записываются объекты, но и может предоставить метаданные для балансирования ввода-вывода путем распределения нагрузки ввода-вывода между группами файлов и отдельными дисками.
Какие таблицы и индексы не используются
Вопрос 3. Какие индексы не вовлечены в процессы чтения или записи?
Ранее в статье упоминалось, что из sys.dm_db_index_usage_stats возвращаются записи только для индексов и куч, связанных с действиями. Поэтому, используя базовую структуру, мы никогда не видим результатов для случаев, когда число операций чтения и записи равно 0. Сначала я построил обобщенное табличное выражение (CTE) из динамического административного представления sys.dm_db_index_usage_stats, чтобы обнаружить какую-либо активность. На следующем шаге я использовал его вместо связанного вложенного запроса. Этот следующий шаг — просто LEFT JOIN из системного представления для индексов (sys.indexes) в CTE. Я выбираю нужный столбец таблицы с правой стороны объединения, где это значение NULL, чтобы возвратить индексы/кучи без зарегистрированных действий; в данном случае object_id (см. листинг 4).
Кроме того, я добавил разъясняющий код (функция COALLESCE) для удаления NULL для куч и замены его на фразу Heap — No Index. Однако это остается полностью на усмотрение администратора базы данных. Результаты на экране 4 сокращены для удобства чтения.
.jpg) |
| Экран 4. Результаты для вопроса 3 |
Этот запрос дает нам определение индексов без действий со времени последнего перезапуска служб или перестройки индексов.
Вопрос 4. Какие таблицы не вовлечены в действия чтения либо записи?
Этот запрос позволяет понять, как способы использования базы данных могут меняться с течением времени. Я обслуживаю очень крупную инфраструктуру SQL Server, и мы используем непрерывную интеграцию для ежедневного применения изменений в производственных условиях. Постепенно мы внедряем новые функции и пошагово совершенствуем продукт. Со временем эти небольшие шаги могут привести нас на новый путь, и некоторые таблицы начнут использоваться реже или выйдут из употребления навсегда. Последнее, что хотелось бы делать мне, как администратору базы данных, — это обслуживать неиспользуемые таблицы, что потенциально может потребовать дополнительных гигабайтов пространства на диске не только для динамической базы данных, но и для всех итераций резервных копий, которые могут храниться в различных местах. Влияние «мертвых таблиц» может ощущаться и при восстановлении баз данных или реализации любой из архитектур HADR (высокой доступности — восстановления после аварий).
Моей целью было идентифицировать объекты, которые не используются, и отслеживать их состояние в течение некоторого времени, а затем выделить с помощью скрипта ссылочный код целостности на месте, удалить эти ограничения и перенести неиспользуемые таблицы и их данные в отдельную базу данных. В течение определенного времени они доступны в сети, но в конечном итоге архивируются на долгосрочной основе на выбранной платформе, прежде чем будут удалены или перемещены в экземпляр SQL, предназначенный для баз данных архивации.
Для достижения такого результата я изменил предыдущий запрос, чтобы собрать результаты на уровне объектов. Мне пришлось внести еще несколько изменений, чтобы удалить любые ссылки на sys.indexes и заменить ряд системных функций, которые будут использовать sys.tables в качестве источника вместо sys.indexes (см. листинг 5 и результаты на экране 5).
.jpg) |
| Экран 5. Результаты для вопроса 4 |
Этот запрос дает нам определение таблиц без действий со времени последнего перезапуска служб или перестройки индексов для возможного архивирования, удаления из базы данных с целью экономии места и сокращения времени восстановления в случае аварии.
Собственно динамическое административное представление sys.dm_db_index_usage_stats может дать ответы на многие важные вопросы о способах применения индексов, а также об обнаружении индексов и объектов, более не задействованных в текущих операциях доступа. Как будет показано в следующей статье серии, объединив эти результаты с метаданными объекта динамического управления, можно получить о них более точное представление.
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 CAST((ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) / (ixUS.user_updates * 1.0) AS DECIMAL(15,1)) 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] DESC , total_writes , total_reads DESC , OBJECT_NAME(ixUS.object_id, IxUS.database_id) , ixUS.index_id;
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 CAST((ixUS.user_seeks + ixUS.user_scans + ixUS.user_lookups) / (ixUS.user_updates * 1.0) AS DECIMAL(15,1)) 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] DESC , total_writes , total_reads DESC , OBJECT_NAME(ixUS.object_id, IxUS.database_id) , ixUS.index_id;
SELECT DB_NAME(ixUS.database_id) AS database__name , OBJECT_SCHEMA_NAME(ixUS.object_id, ixUS.database_id) AS schema__Name , OBJECT_NAME(ixUS.object_id, ixUS.database_id) AS object__name , CASE SUM(ixUS.user_updates) WHEN 0 THEN (SUM(ixUS.user_seeks) + SUM(ixUS.user_scans) + SUM(ixUS.user_lookups)) ELSE CAST(( SUM(ixUS.user_seeks) + SUM(ixUS.user_scans) + SUM(ixUS.user_lookups) ) / (SUM(ixUS.user_updates) * 1.0) AS DECIMAL(12,1)) END AS [r_per_w] , SUM(ixUS.user_seeks) + SUM(ixUS.user_scans) + SUM(ixUS.user_lookups) AS total_reads , SUM(ixUS.user_updates) AS total_writes FROM sys.dm_db_index_usage_stats ixUS WHERE ixUS.database_id = DB_ID() GROUP BY ixUS.database_id , ixUS.object_id ORDER BY [r_per_w] DESC , total_writes , total_reads DESC , OBJECT_NAME(ixUS.object_id, IxUS.database_id);
WITH Used AS ( SELECT ixU.object_id , ixU.index_id FROM sys.dm_db_index_usage_stats ixU WHERE ixU.database_id = DB_ID() GROUP BY --ixU.database_id, ixU.object_id , ixU.index_id ) SELECT OBJECT_SCHEMA_NAME(I.object_id, DB_ID()) AS schema__name , OBJECT_NAME(I.object_id) AS table__name , I.index_id , COALESCE(I.name, ‘Heap - No Index’) AS index__name FROM sys.indexes I INNER JOIN sys.tables T ON I.object_id = T.object_id LEFT JOIN Used ON I.object_id = Used.object_id AND I.index_id = I.index_id WHERE Used.object_id IS NULL AND OBJECT_NAME(I.object_id) <> ‘sysdiagrams’ ORDER BY schema__name , table__name , I.index_id;
WITH Used AS ( SELECT ixU.object_id FROM sys.dm_db_index_usage_stats ixU WHERE ixU.database_id = DB_ID() GROUP BY --ixU.database_id, ixU.object_id --, ixU.index_id ) SELECT OBJECT_SCHEMA_NAME(T.object_id, DB_ID()) AS schema__name , T.name AS table__name FROM sys.tables T LEFT JOIN Used ON T.object_id = Used.object_id WHERE Used.object_id IS NULL AND T.name <> ‘sysdiagrams’ ORDER BY schema__name , table__name;