Если запрос долго выполняется, то часто это свидетельствует о том, что используется плохой план выполнения. Происходить это может по разным причинам, например, не хватает индексов или схема данных неудачно спроектирована. Изучая план выполнения, можно увидеть наиболее ресурсоемкие итераторы плана. Это может во многом помочь понять как следует оптимизировать запрос.
Можно автоматизировать сбор данных о запросах, которые в данный момент выполняются более заданного промежутка времени. Более того, для каждого запроса, можно получить в виде таблицы список наиболее дорогих итераторов плана выполнения и их стоимости. Начнем писать хранимую процедуру для логирования дорогих итераторов:
Таблица #Iterator предназначена для хранения перечня итераторов текущего плана выполнения.
В таблице #Iterators хранится иерархия итераторов планов выполнения. Когда оптимизатор строит план выполнения, то после стадий парсинга и алгебраизации получается дерево, в узлах которого хранятся логические операции для выполнения запроса. Далее оптимизатор с помощью трех стадий оптимизации работает с этим деревом осуществляя перестановки. Начальные операции хранятся на листовом уровне дерева. Каждый итератор плана выполнения реализуется определенным методом специального класса. Метод получает набор строк в качестве параметра. Также метод имеет выходной параметр для передачи набора обработанных строк следующему итератору. Таким образом между итераторами есть иерархия. Она будет храниться в таблице #Iterators.
Продолжим разработку кода. Процедура сохраняет набор запросов, которые выполняются более заданного числа минут, вместе с их планами. Для извлечения плана выполнения текущей инструкции используется функция sys.dm_exec_text_query_plan. О ней можно прочитать в моей статье Статистика запросов.
Теперь необходимо построить цикл по имеющимся планам и цикл по итераторам текущего плана выполнения. Итераторы со своими атрибутами будут сохраняться в таблице #Iterators.
Среди атрибутов итератора мы сохраняем идентификатор узла дерева, стоимость, наименование логического оператора, список объектов баз данных, столбцы которых возвращаются итератором. О стоимости следует поговорить отдельно. Получается, что XML-представление плана выполнения так устроено, что XML-документ плана, соответствующий определенному итератору, содержит в себе и все документы дочерних итераторов любого уровня. И среди атрибутов каждого XML-документа, соответствующего итератору, в качестве стоимости фигурирует не стоимость данного итератора, а общая стоимость всех итераторов для данного поддерева, в корне которого находится заданный итератор. Из этого также следует, что иерархия в таблице #Iterators будет не совсем нормализованной. Вместе в идентификатором итератора в столбце iIteratorId будут храниться дочерние итераторы всех уровней в столбце #iIteratorChildId. Код ниже осуществляет вставку иерархии:
Теперь необходимо правильно рассчитать стоимость конкретного итератора и вывести информацию о наиболее дорогих итераторах. Для этого в коде реализован алгоритм, который для каждого итератора, определяет только лишь непосредственных потомков. Если их найти, то из стоимости поддерева можно отнять сумму стоимостей поддеревьев для всех непосредственных потомков, что даст стоимость итератора.
Для определения непосредственных потомков определяются потомки итератора, которые не имеют предка более высокого уровня, который бы при этом являлся потомком рассматриваемого итератора. Комментарии в запросе разъясняют описанные идеи.
В конце происходит закрытие курсора и логирование ошибки. Инструкция top 3 with ties позволяет отобрать итераторы с тремя наиболее высокими стоимостями (приведенными к int).
В этой процедуре можно добавить логирование информации о найденных итераторах для дальнейшего сбора статистики. Или вставить уведомление по электронной почте.
Можно автоматизировать сбор данных о запросах, которые в данный момент выполняются более заданного промежутка времени. Более того, для каждого запроса, можно получить в виде таблицы список наиболее дорогих итераторов плана выполнения и их стоимости. Начнем писать хранимую процедуру для логирования дорогих итераторов:
if
sessionproperty ( N'quoted_identifier'
) = 0
set quoted_identifier on
go
if
sessionproperty ( N'ansi_nulls' ) = 0
set ansi_nulls on
go
if
object_id ( N'dbo.GetQueryPlanInfo', N'P' ) is null
exec ( N'create proc
dbo.GetQueryPlanInfo as return' )
go
alter
proc dbo.GetQueryPlanInfo
(
@iThresholdTimeMin
int = 5
)
as
begin
set nocount, xact_abort on
begin try
declare
@vcErr varchar ( max ), @xPlan xml, @dtCurDate datetime = getdate (), @iSessionId smallint,
@vcDb
nvarchar ( 200 ), @vcObj nvarchar ( 200 ), @vcSql nvarchar ( max ), @xIteratorInfo xml, @fTotalCost float
if object_id ( N'tempdb..#Iterator', N'U' ) is not null
begin
drop
table #Iterator
end
if object_id ( N'tempdb..#Iterators', N'U' ) is not null
begin
drop
table #Iterator
end
create
table #Iterator
(
iIteratorId int not null,
fSubTreeCost float not
null,
vcOperator nvarchar ( 1000 ) not null,
vcObjects nvarchar ( max ) not null,
primary
key clustered ( iIteratorId asc )
) on [PRIMARY]
if object_id ( N'tempdb..#QueryPlans', N'U' ) is not null
begin
drop
table #QueryPlans
end
create
table #Iterators
(
iIteratorId int
not null,
iIteratorChildId int not null,
primary
key clustered ( iIteratorId asc, iIteratorParentId asc
) on [PRIMARY]
) on [PRIMARY]Таблица #Iterator предназначена для хранения перечня итераторов текущего плана выполнения.
В таблице #Iterators хранится иерархия итераторов планов выполнения. Когда оптимизатор строит план выполнения, то после стадий парсинга и алгебраизации получается дерево, в узлах которого хранятся логические операции для выполнения запроса. Далее оптимизатор с помощью трех стадий оптимизации работает с этим деревом осуществляя перестановки. Начальные операции хранятся на листовом уровне дерева. Каждый итератор плана выполнения реализуется определенным методом специального класса. Метод получает набор строк в качестве параметра. Также метод имеет выходной параметр для передачи набора обработанных строк следующему итератору. Таким образом между итераторами есть иерархия. Она будет храниться в таблице #Iterators.
Продолжим разработку кода. Процедура сохраняет набор запросов, которые выполняются более заданного числа минут, вместе с их планами. Для извлечения плана выполнения текущей инструкции используется функция sys.dm_exec_text_query_plan. О ней можно прочитать в моей статье Статистика запросов.
if object_id ( N'tempb..#QueryPlans',
N'U' ) is not null
begin
drop
table #QueryPlans
end
select
*
into
#QueryPlans
from
(
select
iSessionId = bat.session_id,
dtBegin = bat.start_time,
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
isnull ( Queries.vcDb, '' ) not in ( '', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'semanticsdb' )
alter
table #QueryPlans
add primary key clustered ( iSessionId asc ) on [PRIMARY]
Теперь необходимо построить цикл по имеющимся планам и цикл по итераторам текущего плана выполнения. Итераторы со своими атрибутами будут сохраняться в таблице #Iterators.
declare curPlan cursor local static forward_only for
select
iSessionId, xPlan, vcDb, vcObj, vcSql
from
#QueryPlans
where
datediff ( minute, dtBegin, @dtCurDate ) >= @iThresholdTimeMin
open
curPlan
fetch
next from curPlan into @iSessionId, @xPlan, @vcDb, @vcObj, @vcSql
while
@@fetch_status =
0
begin
select
@xPlan
truncate
table #Iterator
truncate
table #Iterators
declare
curIterators cursor
local static forward_only for
select Oper.OperData.query ( '.' )
from @xPlan.nodes
(
'
declare
namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//NS:RelOp
'
) Oper ( OperData )
open
curIterators
fetch
next from curIterators into @xIteratorInfo
while
@@fetch_status =
0
begin
insert into #Iterator
(
iIteratorId,
fSubTreeCost,
vcOperator,
vcObjects
)
values
(
@xIteratorInfo.value
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(/NS:RelOp/@NodeId)[1]',
'int'
),
@xIteratorInfo.value
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(/NS:RelOp/@EstimatedTotalSubtreeCost)[1]',
'float'
),
@xIteratorInfo.value
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(/NS:RelOp/@LogicalOp)[1]',
'nvarchar ( max )'
),
isnull ( (
select obj.vcDb + '.' + obj.vcSch + '.' + obj.vcTab + ', ' as [data()]
from
(
select distinct
vcDb = objlist.obj.value ( '(@Database)[1]', 'nvarchar ( max )' ),
vcSch = objlist.obj.value ( '(@Schema)[1]', 'nvarchar ( max )' ),
vcTab = objlist.obj.value ( '(@Table)[1]', 'nvarchar ( max )' )
from @xIteratorInfo.nodes
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/NS:RelOp/NS:OutputList/NS:ColumnReference'
) objlist ( obj )
) obj
for xml path ( '' )
), ''
)
)Среди атрибутов итератора мы сохраняем идентификатор узла дерева, стоимость, наименование логического оператора, список объектов баз данных, столбцы которых возвращаются итератором. О стоимости следует поговорить отдельно. Получается, что XML-представление плана выполнения так устроено, что XML-документ плана, соответствующий определенному итератору, содержит в себе и все документы дочерних итераторов любого уровня. И среди атрибутов каждого XML-документа, соответствующего итератору, в качестве стоимости фигурирует не стоимость данного итератора, а общая стоимость всех итераторов для данного поддерева, в корне которого находится заданный итератор. Из этого также следует, что иерархия в таблице #Iterators будет не совсем нормализованной. Вместе в идентификатором итератора в столбце iIteratorId будут храниться дочерние итераторы всех уровней в столбце #iIteratorChildId. Код ниже осуществляет вставку иерархии:
insert into #Iterators ( iIteratorId, iIteratorChildId )
select
nodes.node.value
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
(//NS:RelOp/@NodeId)[1]',
'int'
),
nodes.node.value ( '(@NodeId)[1]', 'int' )
from @xIteratorInfo.nodes
(
'declare namespace
NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//NS:RelOp'
) nodes ( node )
fetch next from curIterators into @xIteratorInfo
end
close
curIterators
deallocate
curIterators
update
#Iterator
set vcObjects = left ( vcObjects, iif ( len ( isnull ( vcObjects, '' ) ) > 0, len ( vcObjects ) - 1, len ( vcObjects ) ) )Теперь необходимо правильно рассчитать стоимость конкретного итератора и вывести информацию о наиболее дорогих итераторах. Для этого в коде реализован алгоритм, который для каждого итератора, определяет только лишь непосредственных потомков. Если их найти, то из стоимости поддерева можно отнять сумму стоимостей поддеревьев для всех непосредственных потомков, что даст стоимость итератора.
Для определения непосредственных потомков определяются потомки итератора, которые не имеют предка более высокого уровня, который бы при этом являлся потомком рассматриваемого итератора. Комментарии в запросе разъясняют описанные идеи.
select top 3 with ties vcOperator, fOpCost, vcObjects
from
(
-- расчет стоимости итератора: из стоимости поддерева отнять
стоимость непосредственных потомков
select ini.vcOperator, ini.vcObjects, fOpCost = 100 *
(
fSubTreeCost - isnull
(
-- поиск непосредственных потомков
(
select sum ( fSubTreeCost )
from #Iterator child -- непосредственный
потомок
для ini
where
ini.iIteratorId <> child.iIteratorId -- не
является
потомком
самого
себя
and
exists -- является потомком
для ini
(
select *
from #Iterators childForIni
where
childForIni.iIteratorId = ini.iIteratorId and
childForIni.iIteratorChildId = child.iIteratorId
)
-- для child не
предка
на
более
низком
уровне
чем ini
and not exists
(
select *
from #Iterator parentAnother
where
parentAnother.iIteratorId <> ini.iIteratorId -- предок
отличен
от ini
and
-- является предком для child
exists
(
select *
from #Iterators parentAnotherLink
where
parentAnotherLink.iIteratorId = parentAnother.iIteratorId and
parentAnotherLink.iIteratorId <> parentAnotherLink.iIteratorChildId and -- искомый предок не
является
потомком
самого
себя
parentAnotherLink.iIteratorChildId = child.iIteratorId
)
and
not exists -- не является предком
для ini, то есть является
предком
для child на более низком
уровне
чем ini
(
select *
from #Iterators parentForIni
where
parentForIni.iIteratorId = parentAnother.iIteratorId and
parentForIni.iIteratorChildId = ini.iIteratorId
)
)
)
, 0
)
) / @fTotalCost
from #Iterator ini
) OpData
order by cast ( fOpCost as int ) desc
fetch
next from curPlan into @iSessionId, @xPlan, @vcDb, @vcObj, @vcSql
end
close
curPlan
deallocate
curPlan
end try
begin catch
set
@vcErr = concat
(
N'Исключение: ', error_message (),
N' Номер: ', error_number (),
N'. Строка: ', error_line (),
N'. Серьезность: ', error_severity (),
N'. Объект: ', error_procedure ()
)
; throw 60000, @vcErr, 1
end catch
end
goВ конце происходит закрытие курсора и логирование ошибки. Инструкция top 3 with ties позволяет отобрать итераторы с тремя наиболее высокими стоимостями (приведенными к int).
В этой процедуре можно добавить логирование информации о найденных итераторах для дальнейшего сбора статистики. Или вставить уведомление по электронной почте.
Комментариев нет:
Отправить комментарий