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

PDF

Optimization: Trivial Plan Optimization

В этом разделе:
— применение правил преобразования;
— особенности стадии trivial plan;
— почему загружается статистика;
— как пропустить фазу поиска тривиального плана (upd)

Итак, мы получили наше упрощенное дерево. Но как оптимизатор догадался его упростить, что именно сделал. Разберемся. Для начала, немного теории.

При упрощении, поиске вариантов соединения, поиске физических операторов для реализации логических операторов, т.е. в целом для построения плана – оптимизатор использует правила преобразования (transformation rules). Эти правила основываются на реляционной алгебре и позволяют оптимизатору генерировать различные варианты. У каждого правила есть pattern и substitute, т.е. шаблон и заменитель. Шаблон – выражение, по которому ищется, к какой части дерева может быть применено правило, заменитель – выражение эквивалент, на которое может быть заменено исходное.
Все правила, можно условно разделить на три группы:
1) simplification rules (упрощающие) — в качестве результата производят более простые логические деревья, чаще всего используются на этапе упрощения, перед полной оптимизацией.
2) exploration rules (исследующие) — так же называются правила преобразования логики, генерируют логически эквивалентные альтернативы (пример:»коммутативный join»-A join B -> B join A).
3) implementation rules (реализующие) — правила преобразования физических операций, используются для поиска альтернативных физических операций (пример:»выбор типа соединения»-Hash Join или Merge Join).

Можно ли посмотреть, какие вообще есть правила? Да, можно! Для этого есть недокументированная команда dbcc showonrules. Прежде, как обычно, в случае недокументированных команд требуется включить флаг трассировки 3604.
Итак, выполним

dbcc traceon(3604);
dbcc showonrules;

результат (сокращен):

JNtoNL – Join to Nested Loops
LOJNtoNL – Left Outer Join to Nested loops
и т.д...

Всего их в 2008 R2 — 377 (в 2012 еще больше).
Можно ли посмотреть, какие правила применяются к конкретному запросу.
Да, для этого есть несколько способов.
Первый из них заключается в том, чтобы для анализа использовать недокументированную dmv sys.dm_exec_query_transformation_stats. В этом представлении сохраняется статистика о применении правил. Что это значит для нас? Это значит, что если мы обеспечим изолированность сервера от других запросов ( т.е. использовать тестовый сервер у себя на локальной машине, о чем я говорил изначально), невлияние на компиляцию исследуемого запроса запросов собирающих информацию, то мы сможем получить данные о том, какие правила были применены при оптимизации данного запроса.

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

-- поместим эти запросы в кэш планов
go
select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
-- удалим созданные таблицы
drop table BeforeQ,AfterQ;
go
-- теперь, соберем статистику до выполнения
go
select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
--выполним запрос
go
select
	t1.b,
	t1.c
from
	t1
	join t2 on t1.b = t2.b
	outer apply (select c from t3 where c = 1) t3
where
	t1.a = 200 and
	t1.c between 1 and 50
option(recompile)
go
--соберем статистику после
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
-- теперь, посмотрим, что изменилось
select
	a.name,
	promissed = (a.promised - b.promised)
from
	BeforeQ b
	join AfterQ a on b.name = a.name
where
	a.succeeded > b.succeeded
;
drop table BeforeQ,AfterQ;

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

<table>

Можно попытаться расшифровать некоторые из этих сокращений, например RedundantApplyOJ – Redundant Apply Outer Join, что значит, было применено правило исключения излишнего оператора Apply. Или CollapseSelects – свернуть выражения select.

Что можно сделать дальше? Дальше, давайте немного поиграем с оптимизатором, ведь правила можно отключать! Да, да, для управления правилами есть еще пара недокументированных команд:

dbcc ruleoff('ruleName')
dbcc ruleon('ruleName')

Давайте отключим, например, правило удаления излишнего Apply, а также некоторые правила связанные с ним и посмотрим на план запроса.
Помните, что делаем мы это, только на тестовом сервере!

dbcc ruleoff('RedundantApplyOJ')
dbcc ruleoff('ApplyHandler')
dbcc ruleoff('SELonApply')
dbcc ruleoff('SelApplyHandler')
go
select
	t1.b,
	t1.c
from
	t1
	join t2 on t1.b = t2.b
	outer apply (select c from t3 where c = 1) t3
where
	t1.a = 200 and
	t1.c between 1 and 50
option(recompile)
go
dbcc ruleon('RedundantApplyOJ')
dbcc ruleon('ApplyHandler')
dbcc ruleon('SELonApply')
dbcc ruleon('SelApplyHandler')
go

План запроса при этом выглядит так:

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

Выполним запрос, с этим флагом:

dbcc ruleoff('RedundantApplyOJ')
dbcc ruleoff('ApplyHandler')
dbcc ruleoff('SELonApply')
dbcc ruleoff('SelApplyHandler')
go
select 
	t1.b,
	t1.c
from 
	t1
	join t2 on t1.b = t2.b
	outer apply (select c from t3 where c = 1) t3
where
	t1.a = 200 and
	t1.c between 1 and 50
option(recompile, querytraceon 3604, querytraceon 2373)
go
dbcc ruleon('RedundantApplyOJ')
dbcc ruleon('ApplyHandler')
dbcc ruleon('SELonApply')
dbcc ruleon('SelApplyHandler')

В итоге вот часть того, что можно увидеть:

Если вы скомбинируете этот флаг, с флагом 8606 из предыдущей части, вы увидите картину в совокупности, какие преобразования выполняются и какое дерево получается в итоге.

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

('IJtoIJSEL'),('IJtoIJSEL'),('MatchGet'),('SelToLASJ'),('SelToLSJ'),('MatchGet'),('JoinCommute'),
('MatchGet'),('SelToLASJ'),('SelToLSJ'),('ApplyToNL'),('SelToIdxStrategy'),('JNtoIdxLookup'),
('SelIdxToRng'),('SelToIdxStrategy'),('SelIdxToRng'),('WCJNonSELtoIdxLookup'),('FetchToApply'),
('ImplRestrRemap'),('SelIdxToRng'),('AppIdxToApp'),('SelIdxToRng')

select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
drop table BeforeQ,AfterQ;
go
go
select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
dbcc ruleoff('RedundantApplyOJ')
dbcc ruleoff('ApplyHandler')
dbcc ruleoff('SELonApply')
dbcc ruleoff('SelApplyHandler')
go
select 
	t1.b,
	t1.c
from 
	t1
	join t2 on t1.b = t2.b
	outer apply (select c from t3 where c = 1) t3
where
	t1.a = 200 and
	t1.c between 1 and 50
option(recompile, querytraceon 3604, querytraceon 2373)
go
dbcc ruleon('RedundantApplyOJ')
dbcc ruleon('ApplyHandler')
dbcc ruleon('SELonApply')
dbcc ruleon('SelApplyHandler')
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
with trace_flag_stats(name,occured)as
(
	select name, occured = count(*) from 
	(
		values
		('IJtoIJSEL'),('IJtoIJSEL'),('MatchGet'),('SelToLASJ'),('SelToLSJ'),('MatchGet'),('JoinCommute'),
		('MatchGet'),('SelToLASJ'),('SelToLSJ'),('ApplyToNL'),('SelToIdxStrategy'),('JNtoIdxLookup'),('SelIdxToRng'),
		('SelToIdxStrategy'),('SelIdxToRng'),('WCJNonSELtoIdxLookup'),('FetchToApply'),('ImplRestrRemap'),('SelIdxToRng'),
		('AppIdxToApp'),('SelIdxToRng')
	) tf(name)
	group by
		name
),
dmv_stats as(
	select
		a.name,
		promissed = (a.promised - b.promised),
		built_subst = (a.built_substitute - b.built_substitute),
		succeeded = (a.succeeded - b.succeeded),
		promissed_value = (a.promise_avg*a.promise_total - b.promise_avg*b.promise_total)/isnull(nullif(a.promised - b.promised,0),1)
	from
		BeforeQ b
		join AfterQ a on b.name = a.name
	where
--a.succeeded <> b.succeeded
		a.built_substitute <> b.built_substitute
)
select
	name = isnull(isnull(tfs.name,ds.name),'TOTAL'),
	built_subst = sum(ds.built_subst),
	promissed = sum(ds.promissed),
	promissed = sum(ds.promissed_value),	
	succeeded = sum(ds.succeeded),
	occured	= sum(tfs.occured)	
from
	trace_flag_stats tfs
	full join dmv_stats ds on tfs.name = ds.name
where
	not ( ds.succeeded = 0 and tfs.occured is null)
group by
	isnull(tfs.name,ds.name)
with rollup
;
go
drop table BeforeQ,AfterQ;
go

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

dbcc ruleoff('GetToScan')В итоге вы получите вот такой результат:В итоге вы получите вот такой результат:
dbcc ruleoff('GetToTrivialScan')
dbcc ruleoff('GetToIdxScan')
go
select * from  t1 option(recompile)
go
dbcc ruleon('GetToScan')
dbcc ruleon('GetToTrivialScan')
dbcc ruleon('GetToIdxScan')
go

В итоге вы получите вот такой результат:

Что логично, т.к. мы начисто лишили оптимизатор возможности использования сканирования таблицы, индекса и сканирование в тривиальном плане.
При всем этом, остаются еще некоторые вопросы.
Опытным путем, удалось выяснить, что наш запрос, в котором исключается apply t3, и join t2 может быть выполнен при всех отключенных правилах, кроме этих трех: GetToScan,JNtoNL,SelectToFilter

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

select 'dbcc ruleoff('''+name+''')' from sys.dm_exec_query_transformation_stats where name not in ('GetToScan','JNtoNL','SelectToFilter') --1
select 'dbcc ruleon('''+name+''')' from sys.dm_exec_query_transformation_stats where name not in ('GetToScan','JNtoNL','SelectToFilter') --2

Теперь, выполним наш запрос, со сбором статистики и всеми включенными флагами трассировки, при этом, предварительно поместим перед запросом результат запроса для отключения правил, а после запроса для включения, также включим опцию Include Actual Plan, а также ограничим оптимизатор в поиске параллельного плана, чтобы говорить только о последовательном.

select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
drop table BeforeQ,AfterQ;
go
select * into BeforeQ from sys.dm_exec_query_transformation_stats;
go
dbcc ruleoff('LOJNtoNL')
/*(cut)...много-много правил из запроса 1...*/
dbcc ruleoff('SpatialJointoApply')
go
select 
	t1.b,
	t1.c
from 
	t1
	join t2 on t1.b = t2.b
	outer apply (select c from t3 where c = 1) t3
where
	t1.a = 200 and
	t1.c between 1 and 50
option (recompile, maxdop 1, querytraceon 3604, querytraceon 2373, querytraceon 8606)
go
dbcc ruleon('LOJNtoNL')
/*(cut)...много-многоправил из запроса 2...*/
dbcc ruleon('SpatialJointoApply')
go
select * into AfterQ from sys.dm_exec_query_transformation_stats;
go
select
	a.name,
	promissed = (a.promised - b.promised)
from
	BeforeQ b
	join AfterQ a on b.name = a.name
where
	a.succeeded > b.succeeded
;
drop table BeforeQ,AfterQ;

Посмотрим на результаты.
План:

Статистика преобразований:

<table>

Вывод флага трассировки 2373 (частично, сокращен):

Memory after rule SelectToFilter: 22
Memory after rule JNtoNL: 23
Memory after deriving properties of LogOp_Spool: 23
Memory after rule GetToScan: 23

Вывод флага трассировки 8606 и 2373 (частично, сокращен):

*******************
*** Simplified Tree: ***
        LogOp_Select
            LogOp_Apply (x_jtLeftOuter)
                LogOp_Select
                    LogOp_Join
                        LogOp_Get TBL: t1 t1 TableID=226099846 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                        LogOp_Get TBL: t2 t2 TableID=306100131 TableReferenceID=0 IsRow: COL: IsBaseRow1004 
                        ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
                        .............................
                        /*сокращено*/                        
Memory after deriving properties of LogOp_Project: 18
Memory before deriving properties of LogOp_Apply: 18
Memory after deriving properties of LogOp_Apply: 19
Memory before deriving properties of LogOp_Select: 19
.............................
/*сокращено*/
*******************
*** Join-collapsed Tree: ***
        LogOp_Select
            LogOp_Select
                LogOp_Join
                    LogOp_Get TBL: t1 t1 TableID=226099846 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                    LogOp_Get TBL: t2 t2 TableID=306100131 TableReferenceID=0 IsRow: COL: IsBaseRow1004 
                    ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
                ScaOp_Comp x_cmpEq
                .............................
                /*сокращено*/

Взглянем на план, мы видим, что вроде бы план логичен, во-первых, сканируются обе таблицы, при этом, не происходит никакой фильтрации, число строк равно 1000*1000=1000 000. Во-вторых, не происходит predicate push down, т.е. проталкивание и значит, нет фильтрации на ранней стадии во время сканирования, вместо этого фильтрация происходит позже. В первом фильтре, отбираются только строки где t1.b = t2.b, во втором фильтруется условие where по значению 200 и диапазону 1 – 50. Но позвольте спросить, куда делась таблица t3? Почему она была исключена, а t2 нет? Ведь вроде бы все правила, кроме трех преобразующих логику в физические операторы, были отключены? Никаких правил кроме трех не показала, ни статистика, ни вывод флага 2373, т.е. преобразований вроде бы не было. Частично, ответ на этот вопрос дает вывод флага 8606. Видно, что на этапе упрощения дерево все еще содержит логический оператор apply, но на этапе join collapse, он исчезает, хотя никаких примененных правил, кроме преобразования логических операторов мы не видим. Это позволяет сказать, что либо 1) недокументированные утилиты выводят неполную информацию (что вряд ли), либо 2) преобразование дерева логических операций осуществляется, помимо правил преобразования, еще и другими средствами. Подтвердить или опровергнуть этот факт я, к сожалению, пока не могу, но результат налицо.

После всего этого, убедитесь, что у вас не осталось отключенных правил, выполнив команду:

dbcc traceon (3604);
dbcc showoffrules;

Если какие-то правила остались не включенными, обязательно их включите.
Кстати, в литературе, встречается упоминание бага, что в сиквел сервере 2005 команды showoffrules и showonrules – перепутаны, и выводят противоположные своему названию результаты. Но я это не проверял, и более того, еще раз замечаю, что не следует выполнять приведенные скрипты на другой версии сервера.

Теперь, вернемся к особенностям стадии поиска тривиального плана.
Известно, что при построении тривиального плана, не используется статистика. Однако, это не совсем так. Чтобы посмотреть на это, используем в запросах очередные недокументированные флаги трассировки 9292 и 9204, которые покажут, используется ли запросом статистика и какая. При этом, не забываем включить наш любимый флаг 3604, для перенаправления вывода в консоль.

Создадим дополнительный индекс на таблице t1 по столбцу b.

create index ix_b on t1(b);

Теперь, давайте выполним следующий запрос:

select b from t1 option(recompile, querytraceon 3604, querytraceon 9204, querytraceon 9292);

Переключимся на вкладку Messages и увидим, что никаких сообщений, кроме как о полученном количестве строк нет. А план является тривиальным, что логично, т.к. в данном случае, единственно хорошим способом получения данных будет просмотр индекса ix_b.

Теперь, немного изменим наш запрос, добавим предикат b = 1

select b from t1 where b = 1 option(recompile, querytraceon 3604, querytraceon 9204, querytraceon 9292);

Посмотрим на план, во-первых, он поменялся, теперь используется Index Seek, что кажется логичным.

И более того на вкладке Messages мы видим:

Stats header loaded: DbName: opt, ObjName: t1, IndexId: 2, ColumnName: b, EmptyTable: FALSE
Stats loaded: DbName: opt, ObjName: t1, IndexId: 2, ColumnName: b, EmptyTable: FALSE
Stats header loaded: DbName: opt, ObjName: t1, IndexId: 4, ColumnName: b, EmptyTable: FALSE
Stats loaded: DbName: opt, ObjName: t1, IndexId: 4, ColumnName: b, EmptyTable: FALSE

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

Однако, это не так, изменим запрос, сделаем так, чтобы он нам вернул все строки:

select b from t1 where b >= 1 option(recompile, querytraceon 3604, querytraceon 9204, querytraceon 9292);

Результат – 1000 строк, но взглянем на план:

Уровень оптимизации по-прежнему TRIVIAL, но поиск (Index Seek) не преобразовался в просмотр (Index Scan), хотя он тут явно выгоднее. Кроме того, на вкладке Messages видно, что также была подгружена статистика. Почему так? Вроде бы статистика используется, а план не меняется в зависимости от числа строк. Дело в том, что статистика используется, но на ее основе не принимаются решения на основе стоимости. В данном (втором) случае, посмотрите внимательно в плане на детали Index Seek.
Вы увидите следующее:

Действительно был осуществлен поиск по индексу (Seek), но на самом деле этот поиск является частичным просмотром индекса (в данном случае всего индекса) по условию b Greater or Equal (больше или равно) 1. Количество выполнений равно 1. Т.е. поиск был осуществлен один раз, после чего начался последовательный просмотр листовых (самых нижних) уровней индекса. Этот вид доступа к данным называется Index Seek + Partial Scan. Необходимо его отличать от просто поиска по индексу, который осуществляется много раз и действительно при больших диапазонах приводит к накладным расходам за счет модели случайного доступа к диску, вместо последовательного и чтению лишних, не корневых, страниц индекса.
Теперь, давайте посмотрим на Estimated Plan, т.е. оценочный план выполнения этих двух запросов, тогда сразу станет понятно, зачем подгружается статистика.

select b from t1 where b = 1 option(recompile, querytraceon 3604, querytraceon 9204, querytraceon 9292);
select b from t1 where b >= 1 option(recompile, querytraceon 3604, querytraceon 9204, querytraceon 9292);

Вот что мы увидим:

Очевидно, что количество оценочных строк различается. А как оптимизатор мог оценить количество строк, если только не просмотреть статистику? По этому, она была загружена. В случае просмотра всей таблицы, он мог взять эту информацию из метаданных о количестве строк в таблице, но в запросах с предикатом, статистика используется, чтобы оценить число строк. Число строк так же влияет на то, сколько памяти будет выделено под этот запрос, но об этом разговор отдельный. А пока, можно сказать только одно — статистика на стадии тривиального плана все же используется, другое дело, что на ее основе не применяются так называемые «cost based decisions», т.е решения по преобразованию операторов на основе стоимости (в этом мы еще убедимся в следующей части).

upd:16.04.2012
Фазу поиска тривиального плана можно принудительно пропустить при помощи очередного недокументированного флага трассировки: 8757.
Можете попробовать выполнить любой из приведенных выше запросов удовлетворяющих тривиальному плану, и увидеть что оптимизатор пропустил фазу поиска тривиального плана (причем, именно пропустил, а не продолжил дальнейшую оптимизацию).
Например:

Что ж, мы посмотрели подробно на стадию поиска тривиального плана. Но вопросы еще остались. Как именно происходит применение правил и поиск оптимального плана? Где и как происходят оценки и выбор? Ответы на эти вопросы будут даны в следующих разделах, где мы рассмотрим процесс выбора плана.

PDF