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

Twitter RSS
Home SQL Server (все заметки) Оптимизатор (недокументированное): Отключить правила преобразования в отдельном запросе
formats

Оптимизатор (недокументированное): Отключить правила преобразования в отдельном запросе

Многие, кто интересуется внутренним устройством оптимизатора, уже наверняка знают, что такое правила преобразования и то, что их можно отключить в сессии при помощи dbcc ruleoff/ruleon. В этой короткой заметке мы посмотрим на недокументированный хинт, который позволяет отключать правила преобразования в отдельно взятом запросе.

Прим:
Те, кто не знают про правила преобразования, но интересуются, могут обратиться к моему предыдущему циклу заметок, части Оптимизатор (ч.2): Optimization: Trivial Plan Optimization.  Если вкратце, речь идет о том, что оптимизатор в процессе преобразования запроса в план, использует правила преобразования. Их довольно много, набор изменяется, некоторые правила удаляются, расширяются и неизменно добавляются, в целом количество увеличивается от версии к версии. Получить их можно запросив представление sys.dm_exec_query_transformation_stats.

Для короткой демонстрации создадим таблицу с полями a, b — которые заполнены значениями от 1 до 1000 и a – первичный ключ и кластерный индекс.

use tempdb;
go
create table t1(a int primary key, b int not null);
insert into t1(a,b) select number, number from master..spt_values where type = 'p' and number between 1 and 1000;
go

Теперь, посмотрим на план простого запроса. И раз уж заметка про недокументированный хинт, давайте посмотрим на план тоже «недокументированно» (хотя в этом и нет необходимости =))

set showplan_xml_with_recompile on
go
select * from t1 where t1.a = 1
go
set showplan_xml_with_recompile off

Подсказка: если вы включите перед этим в Sql Server Management Studio (SSMS) кнопку Include Actual Execution Plan, то у вас план отобразится на отдельной вкладке Execution Plan (а не в датасете в виде xml) отображение — задача SSMS и по этому распространяется и на документированные вещи, например set showplan_xml, statistics xml. Еще хотелось бы обратить внимание, на то, что этот режим дает Estimated план. Так что не путаете его с option(recompile) или statistics xml.

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

Как и ожидалось, тривиальный план (Optimization Level=TRIVIAL) поиска по кластерному индексу.
Давайте избавимся от тривиального плана для демонстрации. Для этого, используем недокументированный флаг трассировки, благодаря которому фаза поиска тривиального плана может быть пропущена. Я уже писал об этом флаге в дополнении к своей заметке около полутора месяцев назад Оптимизатор (ч.2): Optimization: Trivial Plan Optimization — это флаг 8757.
Выполним и посмотрим на план:

set showplan_xml_with_recompile on
go
select * from t1 where t1.a = 1
option (querytraceon 8757)
go
set showplan_xml_with_recompile off

Как мы видим теперь уровень оптимизации Optimization Level=FULL.
Ну а теперь, собственно, давайте сделаем гадость и заставим оптимизатор убрать поиск по индексу в этом запросе, отключив правило SelToIdxStrategy.

Одним из способов, будет выключение перед запросом и включение после запроса правила преобразования, при помощи пары недокументированных функций dbcc. Попробуем влоб:

set showplan_xml_with_recompile on
go
dbcc ruleoff('SelToIdxStrategy')
select * from t1 where t1.a = 1
option (querytraceon 8757)
dbcc ruleon('SelToIdxStrategy')
go
set showplan_xml_with_recompile off

Однако, если мы посмотрим на план, мы увидим, что там все тот же Clustered Index Seek.

Это все потому, что мы выполнили отключение правила после того как выполнили включение режима showplan.

И тогда, становится понятно, что абсолютно справедливо, если вы попытаетесь построить план запроса с отключенными правилами вот так (Display Estimated Execution Plan или Include Actual Execution Plan, без служебной команды разделения пакетов GO), то вы также получите:

Таким образом, перед получением плана нужно разделять команды служебным словом GO, а в случая недокументированной команды showplan_xml_with_recompile, еще и заботится о порядке вызова вот так:

dbcc ruleoff('SelToIdxStrategy')
go
set showplan_xml_with_recompile on
go
select * from t1 where t1.a = 1
option (querytraceon 8757)
go
set showplan_xml_with_recompile off
go
dbcc ruleon('SelToIdxStrategy')
go

Однако, есть хинт, который позволяет не думать о том, как применяются команды в сессии, а просто отключить некоторые правила для исследования. Это хинт queryruleoff. Вот как выглядит запрос:

set showplan_xml_with_recompile on
go
select * from t1 where t1.a = 1
option (querytraceon 8757, queryruleoff SelToIdxStrategy)
go
set showplan_xml_with_recompile off

План:

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

При этом, мы можем совершенно не париться о разделителях и использовать это для отдельно взятого запроса.

Так, если вы выполните:

set showplan_xml_with_recompile on
go
select * from t1 where t1.a = 1
option (querytraceon 8757, queryruleoff SelToIdxStrategy)
select * from t1 where t1.a = 1
option (querytraceon 8757)
go
set showplan_xml_with_recompile off

То вы получите скан в первом случае и поиск во втором.

Если вам требуется отключить несколько правил, просто перечисляйте их.
Например, такой набор:

select * from t1 where t1.a = 1
option (
	recompile
	,querytraceon 8757
	,queryruleoff SelToIdxStrategy
	,queryruleoff GetToIdxScan
	,queryruleoff GetToScan

)

Не позволит создать план запроса, т.к. мы отменили все преобразования чтобы перевести логический оператор Get в Scan.
Однако, если вы закоментируете флаг 8757, тривиального плана — вы получите план, запрос и результат. Это свидетельствует о разных подходах к запросам в сиквеле. При анализе запроса, не лишним будет смотреть, как запрос оптимизирован.

Хочу обратить внимание на две вещи:
1) Во-первых, синтаксис указания правила в хинте, не подразумевает кавычек.
2) Во-вторых, не вздумайте это применять где-то на постоянной основе. Например, может показаться, что такое отключение похоже на опцию with(forcescan) которая стала доступна только с 2008R2(SP1) – но это не так! Вы, таким образом, отключаете это правило во всем запросе! Тогда как документированный хинт, отключает только на указанной таблице. По этому, всегда помните применяйте с умом и желательно в исследовательских и образовательных целях.

Удачных экспериментов!

П.С.
Мои эксперименты проводились на версиях 2008R2, 2012 Express.

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *