Отслеживание запросов с отсутствующими индексами

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

if object_id ( N'dbo.Data', N'U' ) is null

begin

       create table dbo.Data

       (

             iRowId int identity ( 1, 1 )      not null,

             iItem  int                        not null,

             vcStr  varchar ( 100 )            not null

       ) on [PRIMARY]

end

go

insert into dbo.Data with ( tablock ) ( iItem, vcStr )

       select abs ( binary_checksum ( newid () ) ), concat ( '', newid () )

       from

             master.dbo.spt_values t1

                    cross join

             master.dbo.spt_values t2

go

create nonclustered index IX_iRowId on dbo.Data ( iRowId asc ) on [PRIMARY]

go

select *

from dbo.Data with ( nolock )

where iItem between 1000 and 1000000

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

Существует способ автоматически извлекать и логировать эту полезную информацию. Это позволяет создавать автоматические уведомления для авторов запросов и более оперативно реагировать на снижение производительности баз данных. Для извлечения планов выполнения текущих запросов можно воспользоваться каталогом dm_exec_requests и столбцами plan_handle, sql_handle в сочетании с функциями dm_exec_text_query_plan, dm_exec_sql_text для извлечения текста запроса и плана выполнения в формате XML.
Функция dm_exec_text_query_plan чрезвычайно полезна, так как получает в качестве параметров не только код плана, но и оффсеты пакета, что позволяет получать план не всего пакета, а отдельной инструкции. Я начну писать хранимую процедуру, которая проверяет текущие запросы на предмет отсутствующих индексов.

if object_id ( N'dbo.GetMissingINdexes', N'P' ) is null

begin

       exec ( N'create proc dbo.GetMissingINdexes as return' )

end

go

alter proc dbo.GetMissingINdexes

as

begin

       set nocount, xact_abort on

       if object_id ( N'tempdb..#QueryData', N'U' ) is not null

             drop table #QueryData

       select *

             into #QueryData

       from

       (

             select

                    iSessionId   = bat.session_id,

                    vcDb         = db_name ( pl.dbid ),

                    vcObj        = quotename ( db_name ( pl.dbid ) ) + N'.' +

                           quotename ( object_schema_name ( pl.objectid, pl.dbid ) ) + N'.' +

                           quotename ( object_name ( pl.objectid, pl.dbid ) ),

                    vcSql        = substring

                    (

                           txt.text,

                           iif ( bat.statement_start_offset > 0, bat.statement_start_offset / 2, 1 ),

                           iif ( bat.statement_end_offset = -1, len ( cast ( txt.text as nvarchar ( max ) ) ), ( bat.statement_end_offset - bat.statement_start_offset ) / 2 + 2 )

                    ),

                    xPlan        = try_convert ( xml, pl.query_plan )

             from

                    sys.dm_exec_requests bat

                           outer apply

                    sys.dm_exec_sql_text ( bat.sql_handle ) txt

                           outer apply

                    sys.dm_exec_text_query_plan ( bat.plan_handle, bat.statement_start_offset, bat.statement_end_offset ) pl

       ) Queries

       where

             Queries.vcSql is not null and

             Queries.xPlan is not null and

             Queries.iSessionId is not null and

             Queries.vcDb not in ( 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'semanticsdb' )

       alter table #QueryData add primary key clustered ( iSessionId asc ) on [PRIMARY]

       create primary xml index IX_xPlan on #QueryData ( xPlan )

       create xml index IX_xPlan_path on #QueryData ( xPlan ) using xml index IX_xPlan for path

Запрос извлекает планы выполнения и сохраняет их в таблице. На таблице строится первичный ключ, что позволяет создать первичный xml-индекс. Вторичный xml-индекс типа path позволит быстро находить строки, в которых есть конкретные узлы xml-документа. Наша ближайшая цель - найти все планы, в которых есть элемент MissingIndexes. Делается это так:

       declare @iSessionId smallint, @xPlan xml, @fInfl float, @vcDb nvarchar ( 200 ), @vcSch nvarchar ( 200 ), @vcTab nvarchar ( 200 ), @xIndData xml

       declare curInd cursor local static forward_only for

             select iSessionId, xPlan

             from #QueryData

             where xPlan.exist

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    /NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes'

             ) = 1

       open curInd

       fetch next from curInd into @iSessionId, @xPlan

       while @@fetch_status = 0

       begin

При применении метода exist помогает вторичный xml-индекс. Теперь найдем оценку влияния отсутствия индекса, а также таблицу, на которой не хватает индекса, и схему и базу данных, которым принадлежит таблица. Для этого воспользуемся методом value.

             set @fInfl = @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/@Impact)[1]',

                    'float'

             )

            

             set @vcDb = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Database)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

             set @vcSch = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Schema)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

             set @vcTab = parsename ( @xPlan.value

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    (/NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup/NS:MissingIndex/@Table)[1]',

                    'nvarchar ( 200 )'

             ), 1 )

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

             select @xIndData = Info.IndCol.query ( '.' )

             from @xPlan.nodes

             (

                    'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                    /NS:ShowPlanXML/NS:BatchSequence/NS:Batch/NS:Statements/NS:StmtSimple/NS:QueryPlan/NS:MissingIndexes/NS:MissingIndexGroup'

             ) Info ( IndCol )

Используя метод nodes, можно написать запрос, который даст представление переменной @xIndData в табличном виде:

             ;

             with Cols

             as

             (

                    select

                           vcType = ColList.Col.value ( '(@Usage)[1]', 'nvarchar ( max )' ),

                           xCol = ColList.Col.query ( '.' )

                    from @xIndData.nodes

                    (

                           'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                          /NS:MissingIndexGroup/NS:MissingIndex/NS:ColumnGroup'

                    ) ColList ( Col )

             )

             select vcType, vcColumn = parsename ( colinfo.onecol.value ( '(@Name)[1]', 'nvarchar ( max )' ), 1 )

             from

                    Cols

                           outer apply

                    Cols.xCol.nodes

                    (

                           'declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";

                           /NS:ColumnGroup/NS:Column'

                    ) colinfo ( onecol )

             select @iSessionId as iSessionId, @fInfl as fInfl, @vcDb as vcDb, @vcSch as vcSch, @vcTab as vcTab

             fetch next from curInd into @iSessionId, @xPlan

       end

       close curInd

       deallocate curInd

end

go

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