SQL-Ex blog

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

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

Для начала несколько основных определений без углубления в детали:

• Index Seek: Это лучшее и желательное использование индекса. Подразумевается, что для непосредственного доступа к необходимым записям используется дерево индекса.


• Index Scan: Не так хорошо, как поиск по индексу, так что могло быть лучше. Однако иногда даже сканирование индекса хорошо; сканирование некластеризованного индекса означает, что страниц индекса меньше, и их сканирование предпочтительней, чем сканирование кластеризованного индекса. Есть много причин, которые делают хорошим сканирование индекса, но в большинстве случаев вам не нужно достигать этого уровня анализа, вы можете достичь своей цели, только анализируя поиск по индексу.


• Update: Когда поля обновляются (UPDATE/INSERT/DELETE), все индексы, включающие эти поля, также должны обновляться. Индексы - это баланс: мы улучшаем производительность чтения и ухудшаем запись. Проблемы возникают, когда запись происходит чаще, чем чтение.

Давайте проверим использование индекса:

SELECT Db_name(database_id)   db,

       Object_name(object_id) [table],

       si.NAME,

       index_id, user_seeks, user_scans, user_lookups,

       user_updates, system_seeks, system_scans,

       system_lookups, last_user_seek,

       last_user_scan, last_user_lookup, last_user_update

FROM   sys.dm_db_index_usage_stats istat,

       sys.sysindexes si

WHERE  istat.object_id = si.id

       AND istat.index_id = si.indid

ORDER  BY user_seeks

Все индексы с низким использованием могут ухудшать производительность системы. Но что такое низкое использование? Как можно определить низкое использование индекса?

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

AND

user_updates > (user_seeks + user_lookups + system_seeks + system_lookups)

Обнаружение использования индексов

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

Нам нужно определить, какие запросы используют эти индексы. Для этого мы можем запросить кэш планов SQL Server, анализируя XML каждого плана запроса, и найти, где эти индексы использовались.

DML sys.DM_exec_query_stats содержит статистическую информацию о запросах в кэше планов, а также позволяет нам извлечь эти планы с помощью столбца plan_handle и DMF sys.dm_exec_query_plan. Мы можем получить даже лучший результат, также возвращая текст SQL с помощью столбца sql_handle и DMF sys.dm_exec_sql_text.

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

Запрос будет примерно таким:

SELECT qp.query_plan,

       qt.text

FROM   sys.dm_exec_query_stats

       CROSS apply sys.Dm_exec_sql_text(sql_handle) qt

       CROSS apply sys.Dm_exec_query_plan(plan_handle) qp

WHERE

qp.query_plan.exist(‘declare namespace  qplan=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”; //qplan:Object[@Index=”[IX_IDTransaction_AccountTy_TransTy_RefNum_Usr]”]’) = 1 

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

С помощью CTE (общие табличные выражения) мы можем сделать этот запрос более читабельным, разбив его, по крайней мере, на две части. Вот результат:

WITH idxnames AS

(

       SELECT si.NAME

       FROM   sys.dm_db_index_usage_stats istat,

              sys.sysindexes si

       WHERE  istat.object_id=si.id

       AND    istat.index_id=si.indid

       AND    user_updates > (user_seeks + user_lookups + system_seeks + system_lookups) )

SELECT     *

FROM        idxnames

CROSS apply

            (

                        SELECT      qp.query_plan,

                                    qt.text

                        FROM        sys.dm_exec_query_stats

                        CROSS apply sys.Dm_exec_sql_text(sql_handle) qt

                        CROSS apply sys.Dm_exec_query_plan(plan_handle) qp

                        WHERE       qp.query_plan.exist(‘declare namespace qplan=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”; //qplan:Object[@Index=”[‘ + idxnames.NAME + ‘]”]’)=1 )  idxplan

Это естественная структура запроса, но результат разочаровывает: мы получаем сообщение об ошибке "The argument 1 of the XML data type method “exist” must be a string literal" (первый аргумент метода exist типа данных XML должен быть строковым литералом). Другими словами, метод EXIST не принимает конкатенацию строк.

Исправление выражения XQUERY

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

WITH idxnames

     AS (SELECT si.NAME,

         ‘declare namespace  qplan=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”; //qplan:Object[@Index=”‘ 

                + si.NAME + ‘]”]’ AS filter

         FROM   sys.dm_db_index_usage_stats istat,

                sys.sysindexes si

         WHERE  istat.object_id = si.id

                AND istat.index_id = si.indid

                AND user_updates > ( user_seeks + user_lookups + system_seeks

                                     + system_lookups ))

SELECT *

FROM   idxnames

       CROSS apply (SELECT qp.query_plan,

                           qt.text

                    FROM   sys.dm_exec_query_stats

                           CROSS apply sys.Dm_exec_sql_text(sql_handle) qt

                           CROSS apply sys.Dm_exec_query_plan(plan_handle) qp

                    WHERE

       qp.query_plan.exist(‘sql:column(“idxNames.filter”)’) = 1)

               idxPlan 

Фильтрация обновлений индекса из запроса

Вас может удивить изначально большое количество результатов для каждого индекса. Однако, анализируя план запроса XML вы обнаружите, что индекс наиболее часто используется для обновлений, то есть индекс обновляется при обновлении записи.

Планы, в которых индекс используется для обновления не нужен нам в этом запросе, они именно то, что создает проблемы производительности. Нам нужно проанализировать только использование индекса в SELECT'ах, а не UPDATE'ах. Для фильтрации запроса, чтобы он вернул только использование индекса в SELECT, нам нужно разобраться со структурой плана запроса XML. Ниже показан фрагмент XML плана запроса:

 NodeId="7" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.2" 

          EstimateCPU="2e-005" AvgRowSize="33" EstimatedTotalSubtreeCost="0.200021" Parallel="0" 

           EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">

        DMLRequestSort="0">

              Database="[INS_3180]" Schema="[dbo]" Table="[IDTransaction]" 

                  Index="[IX_IDTransaction_AccountTy_ID_CurrencyTy_TransDate_TransTy_Amount_MiscDescr_RefNum_EscrowDate_ProgName_DetailID]" 

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

Вот какие LogicalOp нам нужно получить: Clustered Index Scan, Clustered Index Seek, Index Scan и Index Seek. Нам нужно изменить фильтр, чтобы извлечь только эти запросы.

WITH idxnames

     AS (SELECT si.NAME,

           ‘declare namespace  qplan=”http://schemas.microsoft.com/sqlserver/2004/07/showplan”;                         //qplan:RelOp[@LogicalOp=”Index Scan”

                           or @LogicalOp=”Clustered Index Scan”

                           or @LogicalOp=”Index Seek”

                           or @LogicalOp=”Clustered Index Seek”]/Object[@Index=”‘ 

                + si.NAME + ‘]”]’ AS filter

         FROM   sys.dm_db_index_usage_stats istat,

                sys.sysindexes si

         WHERE  istat.object_id = si.id

                AND istat.index_id = si.indid

                AND user_updates > ( user_seeks + user_lookups + system_seeks

                                     + system_lookups ))

SELECT *

FROM   idxnames

       CROSS apply (SELECT qp.query_plan,

                           qt.text

                    FROM   sys.dm_exec_query_stats

                           CROSS apply sys.Dm_exec_sql_text(sql_handle) qt

                           CROSS apply sys.Dm_exec_query_plan(plan_handle) qp

                    WHERE

       qp.query_plan.exist(‘sql:column(“idxNames.filter”)’) = 1)

               idxPlan 

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