Заметки Дмитрия Пилюгина о Microsoft SQL Server 

PDF

Optimization: Full Optimization: Search 1

В данном разделе:
— update statistics with row_count, page_count;
— преобразования memo;
— параллельный план;

Данная фаза, называется также Quick Plan. Как мы уже говорили, запросы могут миновать стадию Transaction Processing (search 0), и сразу перейти к этой фазе, если в запросе менее трех таблиц.
Также эта фаза примечательна тем, что на ней осуществляется поиск параллельного плана.

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

Начнем с того, что изменим наши данные так, чтобы план запроса, найденный на предыдущей стадии search0, перестал удовлетворять внутреннему порогу оптимизатора для этой стадии и оптимизатор продолжил поиски на стадии search1.
Для этого, нужно увеличить размер исследуемых таблиц, однако, мы воспользуемся другим приемом, а именно недокументированными опциями команды update statistics — with row_count, page_count. Эти опции изменяют метаданные об индексах, устанавливая те значения, которые мы укажем.

Помните, что все операции мы выполняем на тестовом сервере.

Выполним:

select
	[allocation_units: data_pages] = au.data_pages,
	[partitions: rows] = p.rows,
	[partition_stats: in_row_data_page_count] = ps.in_row_data_page_count,
	[partition_stats: row_count] = ps.row_count
from
	sys.allocation_units au
	join sys.partitions p on au.container_id = p.hobt_id
	join sys.dm_db_partition_stats ps on ps.partition_id = p.partition_id
where
	p.[object_id] in (object_id('t1'),object_id('t2'),object_id('t3'))

update statistics t1 with rowcount = 1000000, pagecount = 1000
update statistics t2 with rowcount = 1000000, pagecount = 1000
update statistics t3 with rowcount = 1000000, pagecount = 1000

select
	[allocation_units: data_pages] = au.data_pages,
	[partitions: rows] = p.rows,
	[partition_stats: in_row_data_page_count] = ps.in_row_data_page_count,
	[partition_stats: row_count] = ps.row_count
from
	sys.allocation_units au
	join sys.partitions p on au.container_id = p.hobt_id
	join sys.dm_db_partition_stats ps on ps.partition_id = p.partition_id
where
	p.[object_id] in (object_id('t1'),object_id('t2'),object_id('t3'))

Результат (до и после):

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

Поговорим немного подробнее об этой команде.
Хотя это опции команды update statistics, но в реальности сама статистика не обновляется.
Посмотрим, к примеру, какие есть статистики для таблицы t1:

select * from sys.stats WHERE object_id = object_id('t1')

У меня это:

Одна статистика для кластерного индекса, и две автоматически созданные статистики для колонок b,c.

Прим. Кстати, как формируется имя автоматической статистики, последняя часть – это object_id объекта для которого создана статистика, дальше идет id столбца, дальше префикс Sys, ну а WA – встречал в литературе такое описание, что якобы это Washington, место, где располагается команда разработки, однако люди, которые сейчас работают в оптимизаторе сказали, что этой истории не помнят. Впрочем, может, помнит кто-то другой, пока будем считать это просто легендой.

Давайте выполним команду для просмотра статистики по первичному ключу (у вас наверняка будет другой идентификатор первичного ключа, так что поменяйте на свой):

dbcc show_statistics(t1,PK__t1__3BD0198E0F624AF8)

Результат:

Как видите, никаких данных о 1000000 строк нет. Т.е. команда update statistics with row_count,page_count – не затрагивает саму статистику.

Более того, если вы выполните (заменив PK__t1__3BD0198E0F624AF8 на то значение, которое будет у вас):

update statistics t1 PK__t1__3BD0198E0F624AF8 with rowcount = 1000000, pagecount = 1000

Команда пройдет успешно, но если вы выполните (т.е. попытаетесь обновить данные по статистике без индекса):

update statistics t1 _WA_Sys_00000002_0D7A0286 with rowcount = 1000000, pagecount = 1000

То результатом будет:
Msg 3739, Level 11, State 3, Line 1
Cannot UPDATE the index ‘_WA_Sys_00000002_0D7A0286’ because it is not a statistics collection.

Если вы попытаетесь вернуть все значения назад, при помощи команды:

update statistics t1 with fullscan
update statistics t2 with fullscan
update statistics t3 with fullscan

select object_name(ps.[object_id]), ps.in_row_data_page_count, ps.row_count
from sys.dm_db_partition_stats ps
where ps.[object_id] in (object_id('t1'),object_id('t2'),object_id('t3'))

То результат останется прежним:

Все это говорит о том, что при обновлении метаданных в данном случае, реальная статистика никак не затрагивается, а обновляются только метаданные индекса. Поэтому, конечно было бы логичнее поместить этот функционал, например, в alter index, но, впрочем, т.к. эта опция недокументированна, то и претензии предъявлять неуместно.

По этому, вернуть значения к «нормальным» можно перестроив индексы:

alter index all on t1 rebuild;
alter index all on t2 rebuild;
alter index all on t3 rebuild;

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

Теперь, выполним наш запрос, с информацией по стадиям оптимизации:

select *
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604
,querytraceon 8675
)

Результат:

End of simplification, time: 0 net: 0 total: 0 net: 0
end exploration, tasks: 89 no total cost time: 0.002 net: 0.002 total: 0.003 net: 0.003
end search(0),  cost: 9.35972 tasks: 207 time: 0 net: 0 total: 0.004 net: 0.004
end exploration, tasks: 460 Cost = 9.35972 time: 0.001 net: 0.001 total: 0.006 net: 0.006
end search(1),  cost: 8.5812 tasks: 895 time: 0.003 net: 0.003 total: 0.01 net: 0.01
end exploration, tasks: 896 Cost = 8.5812 time: 0 net: 0 total: 0.01 net: 0.01
end search(1),  cost: 8.5812 tasks: 1397 time: 0.003 net: 0.003 total: 0.014 net: 0.014
End of post optimization rewrite, time: 0 net: 0 total: 0.014 net: 0.014
End of query plan compilation, time: 0 net: 0 total: 0.014 net: 0.014

Мы видим, что запрос действительно прошел стадии, упрощения, search 0. Почему в выводе search1 встречается два раза? Дело в том, что эта фаза может быть повторно выполнена для поиска параллельного плана. Стоимость 8.5812, больше заданного по умолчанию значения 5.
Чтобы посмотреть, какое пороговое значение стоимости для параллелизма задано у вас, выполните:

Теперь, измените значение на 10 и перезапустите запрос:

exec sp_configure 'cost threshold for parallelism', 10
reconfigure with override
go
select * 
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604
,querytraceon 8675
)

Вы увидите, что теперь вывод:

Configuration option 'cost threshold for parallelism' changed from 10 to 10. Run the RECONFIGURE statement to install.

End of simplification, time: 0 net: 0 total: 0 net: 0
end exploration, tasks: 89 no total cost time: 0 net: 0 total: 0.001 net: 0.001
end search(0),  cost: 9.23264 tasks: 207 time: 0 net: 0 total: 0.002 net: 0.002
end exploration, tasks: 460 Cost = 9.23264 time: 0 net: 0 total: 0.003 net: 0.003
end search(1),  cost: 8.45349 tasks: 887 time: 0.003 net: 0.003 total: 0.007 net: 0.007
End of post optimization rewrite, time: 0 net: 0 total: 0.007 net: 0.007
End of query plan compilation, time: 0 net: 0 total: 0.007 net: 0.007

Т.е. повторный запуск фазы search1 и исследования для параллельного плана, не производятся, т.к. стоимость 8.45349 меньше значения10, после которого нужно производить поиск параллельного плана.

Не забудем все вернуть на круги своя:

exec sp_configure 'cost threshold for parallelism', 5
reconfigure with override
go
exec sp_configure 'show advanced options', 0
reconfigure with override
go

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

А пока, давайте взглянем на то, как между стадиями оптимизации преобразуется структура memo.
Для этого выполним запрос с некоторыми недокументированными флагами, описанными в предыдущей части:

select * 
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604 --output to console
,querytraceon 8675 --optimization phase info
,querytraceon 8608 --initial memo
,querytraceon 8615 --final memo
)

Я не буду приводить здесь вывод с консоли, т.к. он довольно объемный.
Приведу сокращенный результат в виде рисунка.

Из этого результата видно, что на стадии search 1, в качестве входного, изначального memo используется результат предыдущей стадии search 0 (вы можете сравнить вывод с консоли, я например, сравнивал вывод memo, сохранив результат в два текстовых файла, при помощи Total Commander). Результаты 2 и 3 – полностью совпадают.
После этого, идет исследование альтернатив плана на стадии search 1, и оно отображается в части 4. Для стадии exploration при поиске параллельного плана, новое memo не строится, используется то, что было на стадии поиска последовательного, и в результате, мы получаем итоговое memo — 5. После этого выполняется поиск самого дешевого плана и в данном случае, при одинаковых стоимостях, выбирается последовательный.

Вернемся к вопросу, есть ли способ, заставить оптимизатор использовать параллельный или последовательный план? В случае последовательного плана, работает документированный хинт option(maxdop 1), а как насчет параллельного? Да, возможность есть, но она не документирована, это использование очередного флага трассировки 8649.

Выполним наш запрос и посмотрим на план:

select * 
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604
,querytraceon 8649
)

Мы видим, что теперь, мы получили параллельный план.
Если выполнить этот запрос, со всеми, упомянутыми ранее, включенными флагами трассировки, то мы не найдем в memo физический оператор Parallelism. Однако, в выходном дереве физических операторов, будет присутствовать операция Exchange Start:

Exchange Start
PhyOp_Apply lookup TBL: t2 (0) (x_jtInner)
    PhyOp_Apply lookup TBL: t3 (0) (x_jtInner)
        PhyOp_Filter
            PhyOp_Range TBL: t1(1) ASC  Bmk ( QCOL: [opt].[dbo].[t1].a) IsRow: COL: IsBaseRow1001 
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [opt].[dbo].[t1].b
                ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
        PhyOp_Range TBL: t3(1) ASC  Bmk ( QCOL: [opt].[dbo].[t3].c) IsRow: COL: IsBaseRow1007 
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [opt].[dbo].[t1].a
                ScaOp_Identifier QCOL: [opt].[dbo].[t3].c
    PhyOp_Range TBL: t2(1) ASC  Bmk ( QCOL: [opt].[dbo].[t2].b) IsRow: COL: IsBaseRow1004 
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier QCOL: [opt].[dbo].[t2].b
            ScaOp_Identifier QCOL: [opt].[dbo].[t3].c

Это говорит о том, что используется оператор параллелизм. В случае, когда в плане отображаются другие операторы параллелизма, например Parallelism — Repartition streams, вы можете увидеть в дереве операцию «Exchange Partition» и т.д. Почему мы не видим отдельных «параллельных» операторов. Дело в том, что параллелизм в сиквеле достигается не за счет распараллеливания отдельных операторов, а за счет распараллеливания всего плана (хотя в нем могут быть и последовательные ветки, на что мы позже посмотрим). Нагляднее это продемонстрирует позаимствованная мной картинка из блога Craig Freedman-а.

Что делать, если у вас машина, с одним ядром процессора, а посмотреть на параллельный план хочется. Можно ли добиться на ней параллельного плана. Да, для этого, есть еще одна недокументированная вкусность, выполните:

dbcc optimizer_whatif(1,4)
go
select * 
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604
,querytraceon 8649
)

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

dbcc optimizer_whatif(1,1)
go
select * 
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 3604
,querytraceon 8649
)

То вы получите последовательный план.

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

Однако, параллельный план возможен не всегда.

Выполним такой запрос, с флагом параллельного запроса:

create function dbo.uf_sf(@a int) 
returns int
as begin return 1; end;
go
select *, dbo.uf_sf(1)
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
option(recompile
,querytraceon 8649)
go
drop function dbo.uf_sf;

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

Далее, другой запрос:

create function dbo.uf_mtvf(@a int) 
returns @t table(a int)
as begin
insert into @t select 1;
return
end
go
select *
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
	cross apply dbo.uf_mtvf(1) 
where
	t1.b = 1
option(recompile
,querytraceon 8649)
go
drop function dbo.uf_mtvf;

План:

Multi-statement Table Valued Function – требуют последовательной ветки в плане, однако не ограничивает в параллелизме весь план.

И, наконец, inline table function:

create function dbo.uf_itvf(@a int) 
returns table
as return select c as a from t3 where c = @a
go
select *
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
	cross apply dbo.uf_itvf(a) 
where
	t1.b = 1
option(recompile
,querytraceon 8649)
go
drop function dbo.uf_itvf;

План:

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

create function dbo.uf_itvf(@a int) 
returns table
as return select c as a from t3 where c = 10
go

Теперь, выполним запрос:

select *
from
	t1
	join t2 on t1.a = t2.b
	join t3 on t2.b = t3.c
where
	t1.b = 1
	and object_id('t1') = object_id('t1')
option(recompile
,querytraceon 8649)

План:

Использование некоторых (но не всех) встроенных функций, также ограничивает параллелизм.

Предложение top может форсировать появление последовательной ветки в параллельном плане.
Выполним запросы и посмотрим на планы:

select *
from
	t1
	join t2 on t1.a = t2.b
	cross apply (select top(1) c from t3 where t3.c = 10 order by c) t3
where
	t1.b = 1
option(recompile
,querytraceon 8649
)
select *
from
	t1
	join t2 on t1.a = t2.b
	cross apply (select top(1) c from t3 where t3.c = t2.b order by c) t3
where
	t1.b = 1
option(recompile
,querytraceon 8649
)

Результат:

Как вы видите, в первом случае, top действительно привел к последовательной ветке в плане, во втором нет. Однако, следует сказать, что в случае Nested Loops, для операторов во внутреннем цикле, практически всегда идет последовательное выполнение, обрабатывая по одной строке внешнего цикла, хотя в плане все равно присутствует значок параллельного плана.
Давайте выполним один и тот же запрос с флагом параллельного плана и без:

select *
from
	t1
	join t2 on t1.a = t2.b
	cross apply (select top(1) c from t3 where t3.c = t2.b order by c) t3
where
	t1.b = 1
option(recompile
,querytraceon 8649
)
;
select *
from
	t1
	join t2 on t1.a = t2.b
	cross apply (select top(1) c from t3 where t3.c = t2.b order by c) t3
where
	t1.b = 1
option(recompile)

Посмотрим на план и оценки:

Посмотрим на оператор Clustered Index Scan t1.
Parallel Plan: 1.2932 (CPU = 0.550079)
Serial Plan: 1.84328 (CPU = 1.10016)
В случае параллельного плана, стоимость CPU снизилась в два раза (1.10016/2=0.55008), т.е. сервер масштабировал оценку, учитывая, что просмотр таблицы может происходить в несколько потоков. Что повлияло на общую оценку этого оператора.

Посмотрим на оператор Clustered Index Seek t3.
Parallel Plan: 2.12011 (CPU = 0.0001581)
Serial Plan: 2.12011 (CPU = 0.0001581)
Как мы помним, для операции lookup в Nested Loops в поле CPU оператора отражается всегда стоимость одной операции, поэтому не будем смотреть на то, что значение 0.0001581 и там и там одинаково. Однако, что интересно, и общая стоимость оператора никак не изменилась, не было сделано никакого масштабирования в зависимости от числа процессоров. Знак параллельного выполнения в данном случае, может означать то, что операторы во внутренней части цикла Nested Loops, выполняются последовательными потоками не зависящими от DOP. Об это также можно почитать в статье Paul White-а, и в упомянутом выше блоге Craig Freedman-а.

Совсем ли отсутствует реальный параллелизм во внутренней части оператора соединения вложенными циклами? Нет, не совсем. Например, случай, когда во внешней таблице один ряд, выполните:

select * 
from
	t1
	join t3 on t1.c = t3.c
where t3.c = 1
option(recompile
,querytraceon 8649
)
select * 
from
	t1
	join t3 on t1.c = t3.c
where t3.c = 1
option(recompile,
maxdop 1
)

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

На этом, мы завершаем разговор про параллельные планы и стадию search 1.

Не забудем вернуть метаданные индексов к нормальному состоянию, перестроив их:

alter index all on t1 rebuild;
alter index all on t2 rebuild;
alter index all on t3 rebuild;

upd.06.09.2012
В 2012 сервере появились новые интересные элементы плана выполнения. В том числе, касающиеся параллельного плана.
Например, можно посмотреть причину, почему не сгенерировался параллельный план.
За это отвечает недокументированный атрибут NonParallelPlanReason, элемента QueryPlan.
Также, появился элемент OptimizerHardwareDependentProperties, в нем нас интересует атрибут EstimatedAvailableDegreeOfParallelism.
Отмечу, что если вы также как и я, пока пользуетесь SSMS 2008, при работе с 2012 сервером, то не пользуйтесь графическим интерфейсом просмотра планов (даже если потом вы переключитесь в xml). Т.е. не включаете режим Include actual Execution Plan, и не пользуйтесь Display Estimated Execution Plan.
Вместо этого, можно включить режим или задействовать инструменты, перечисленные ниже:

set showplan_xml on --получить оценочный план
set statistics xml on --получить действительный план
sys.dm_exec_query_plan --вытащить запрос из кэша
--Profiler --использовать профайлер

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

<QueryPlan NonParallelPlanReason=»NoParallelPlansInDesktopOrExpressEdition« …/>

Если попробовать выполнить запрос со скалярной функцией, о котором говорилось выше, то причина указывается просто:

<QueryPlan NonParallelPlanReason=»CouldNotGenerateValidParallelPlan« …/>

Не очень информативно.
Ранее мы использовали недокументированную команду dbcc optimizer_whatif. Теперь, можно видеть как ее выполнение отражается в плане.
Если выполнить dbcc optimizer_watif (1,1), или dbcc optimizer_whatif(‘CPUs’,1), что одно и то же, и посмотреть на атрибут OptimizerHardwareDependentProperties. То можно увидеть

dbcc optimizer_whatif('CPUs',1)

<OptimizerHardwareDependentProperties … EstimatedAvailableDegreeOfParallelism=»1» />

dbcc optimizer_whatif('CPUs',8)

<OptimizerHardwareDependentProperties … EstimatedAvailableDegreeOfParallelism=»4» />

Потом, не забудьте сбросить результаты экспериментов.

dbcc optimizer_whatif('ResetAll')

Использованная литература

— Microsoft SQL Server 2008 Internals (Kalen Delany)
— Counting, Enumerating, and Sampling of Execution Plans in a Cost-Based Query Optimizer (Florian Waas, Cesar Galindo-Legaria)
— Inside the SQL Server Query Optimizer (Benjamin Nevarez) (можно скачать PDF)
— Microsoft SQL Server 7.0 Query Processor (Goetz Graefe, Jim Ewel, and Cesar Galindo-Legaria)
— Inside Microsoft SQL Server 2008 — TSQL Querying (Itzik Ben Gan)
— Блог Craig Freedman-а
— Статья: Forcing a Parallel Query Execution Plan

Другие ссылки по этой теме

Inside the Optimizer: Constructing a Plan — Part 1

upd. 28.04.2012
Benjamin Nevarez blog — Inside the Query Optimizer Memo Structure
Benjamin Nevarez blog — More Undocumented Query Optimizer Trace Flags
Paul White blog — Query Optimizer Deep Dive – Part 1

a href=»http://www.somewheresomehow.ru/wp-content/uploads/2012/03/14.jpg»td bgcolor=»#B9CCDF»/atd bgcolor=»#B9CCDF»

PDF