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

Twitter RSS
Home SQL Server (все заметки) Ошибка вычисления селективности для предикатов с isnumeric
formats

Ошибка вычисления селективности для предикатов с isnumeric

Недавно на одном из форумов был задан вопрос о том, почему при добавлении в запрос условия isnumeric(column)=0, запрос начинает выполняться очень медленно. Изучение этой ситуации привело к интересным результатам.

Подготовка данных

Создадим тестовую бд, таблицу и скрипт для заполнения данными.

create database IntrinsicCETest;
go
use IntrinsicCETest;
go
create table t (a int identity primary key, b varchar(8), c char(200) not null default(''));
create index ix_b on t(b);
go
create proc FillTestData( @SampleRowCount int )
as
truncate table t; --clear all data
with numbers(n) as --insert @SampleRowCount rows
(
	select top(@SampleRowCount) row_number() over(order by (select null)) from sys.columns c1,sys.columns c2,sys.columns c3
)
insert t(b)
select
	case when n%2 = 0 then 'A'+left(newid(),7) else convert(varchar(8),dateadd(dd,n,'19000101'),112) end
from
	numbers
alter table t rebuild; --rebuild all indexes
update statistics t with fullscan; --update statistics with full scan to get accurate estimates
go

В колоноке b половина строк удовлетворяет условию isnumeric(b)=0, половина нет.

Странное поведение

Теперь, давайте выполним один и тот же запрос для одной и той же таблицы но с разным числом строк 10,100 и 1000.

use IntrinsicCETest;
go
exec FillTestData 10;
select page_count from sys.dm_db_index_physical_stats(db_id(),object_id('t'),1,null,null);
set statistics io on
select t.b,t.c from t where isnumeric(t.b) = 0;
set statistics io off
go
exec FillTestData 100;
select page_count from sys.dm_db_index_physical_stats(db_id(),object_id('t'),1,null,null);
set statistics io on
select t.b,t.c from t where isnumeric(t.b) = 0;
set statistics io off
go
exec FillTestData 1000;
select page_count from sys.dm_db_index_physical_stats(db_id(),object_id('t'),1,null,null);
set statistics io on
select t.b,t.c from t where isnumeric(t.b) = 0;
set statistics io off
go

Результаты:

1) Размер таблицы: 1 страница
Table ‘t’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
2) размер таблицы 3 страницы
Table ‘t’. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
3) размер таблицы 29 страниц
Table ‘t’. Scan count 1, logical reads 1005, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Между результатами 2 и 3 наблюдается колоссальная разница в чтениях. При том, что таблица увеличилась в 10 раз, чтения увеличились в 200 раз!
Ответ на вопрос почему, будем искать в плане выполнения.
Повторим запросы 2 и 3 с опцией Include actual execution plan.

exec FillTestData 100;
select page_count from sys.dm_db_index_physical_stats(db_id(),object_id('t'),1,null,null);
set statistics io on
select t.b,t.c from t where isnumeric(t.b) = 0;
set statistics io off
go

exec FillTestData 1000;
select page_count from sys.dm_db_index_physical_stats(db_id(),object_id('t'),1,null,null);
set statistics io on
select t.b,t.c from t where isnumeric(t.b) = 0;
set statistics io off
go


Как видно из планов, во втором запросе сервер решил применить поиск по индексу, вместо сканирования, и это при том, что выбирается половина таблицы. Довольно странно.
Может возникнуть вопрос, почему вообще планы отличаются? Дело в том, что оптимизатор принимает решения на основе стоимости и с определенного момента, более выгодным становится использовать одну стратегию доступа, чем другую. В данном случае, оптимизатор посчитал, что кластерный индекс достаточно разросся и его сканирование будет более затратным, чем просмотр не кластерного индекса (index scan) + поиск по кластерному индексу (key lookup).
Очевидно, что тут оптимизатор ошибается, мы это ясно видим по стремительному росту чтений. Посмотрим, какие оценки привели к такому плану.

Из плана видно, что оптимизатор оценил количество предполагаемых строк как 1, тогда как на самом деле их 500.

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

select t.b,t.c from t where isnumeric(t.b) = 1;

Как мы видим, на этот раз, оптимизатор не ошибся с планом, и хотя ввиду отсутствия статистики оценка не является точной, тем не менее, план выбран верно. Давайте при помощи хинта заставим использовать в этом запросе индекс ix_b и посмотрим, какая будет оценка.

Примечение:
Здесь и далее я пишу «выполнить» запрос, подразумевая, что он всегда будет выполнен в режиме set showplan_xml on. Т.е. просто получен оценочный план. В данном случае для понимания важен именно он, а не действительное выполнение запроса.

set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0;
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 1;
go
set showplan_xml off
go

Следует обратить внимание:
1. В первом запросе отсутствуют операторы Filter и Compute Scalar. Вместо этого, фильтр перемещен в предикат операции сканирования.
2. Планы отличаются по количеству операторов, но похожи по форме. Оценка запроса с единицей, даже при такой форме плана, остается нормальной (177 строк), в отличие от запроса с нулем (1 строка).

Что произошло с фильтром

На самом деле, операция фильтрации есть в плане, так или иначе запрос должен отобрать интересующие нас строки. В первом запросе, мы видим ее перемещенной на этап сканирования, а не отдельным оператором в плане. Так происходит потому, что план при создании проходит стадию post optimization rewrite. На ней план подвергается преобразованиям. Эта стадия наступает уже после того как выбран оптимальный план для выполнения, т.е. на этой стадии не принимается никаких решений на основе стоимости.
Те, кто читал мою предыдущую заметку относительно RowGoal на неравномерных данных, помнят представленный там флаг трассировки, который отключает такое преобразование для оператора фильтра. Это флаг 9130. Выполним запросы с этим флагом, чтобы посмотреть на формы планов.

set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0 
option(recompile, querytraceon 9130);
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile, querytraceon 9130);
go
set showplan_xml off
go

План первого запроса примет форму:

Как видно из плана, в оператор фильтр по оценке оптимизатора должно поступить 1000 строк (верно), после фильтрации ожидается одна строка (совсем не верно).

Почему фильтр помещается перед Nested Loops Join? Дело в том, что оптимизатор имеет в арсенале такой прием как «проталкивание предикатов» (predicate push down) вниз по плану. Это делается с целью уменьшить количество строк до того, как они попадут в соединение. Очевидно, в этом случае оптимизатор ошибочно совершил это действие.

Тривиальный план

Давайте упростим исходный запрос, исключив колонку t.c. Тем самым мы уберем необходимость в операции key lookup + nested loops, т.к. все нужные данные будут содержаться только в индексе ix_b.

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0 
option(recompile, querytraceon 9130);
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile, querytraceon 9130);
go
set showplan_xml off
go


Даже в этом случае оценка катастрофически неправильная. Теперь, 1 против 1000!

Примечание:
Я больше не буду включать количественную оценку 1 или 1000 в скриншоты, просто смотрите на толщину стрелок от оператора к оператору, тонкая – 1 строка, толстая – больше чем 1. Конкретное значение нам тут не важно.

Теперь выполним вот такой запрос (в список колонок добавлено выражение (select 1)):

set showplan_xml on
go
select t.b,(select 1) from t with(index(ix_b)) where isnumeric(t.b) = 0 
option(recompile, querytraceon 9130);
select t.b,(select 1) from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile, querytraceon 9130);
go
set showplan_xml off
go

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

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0 
option(recompile, querytraceon 9130, querytraceon 8757);
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile, querytraceon 9130, querytraceon 8757);
go
set showplan_xml off
go

Тут оценки тоже в порядке, только исчез один оператор Compute Scalar, тот который отвечал за выполнение (select 1).
Как я писал ранее, в заметке про тривиальный план, флаг 8757 предотвращает использование тривиального плана. Подзапросы тоже могут предотвратить тривиальный план, подзапрос (select 1) в select сделал именно это. Т.е. два последних запроса, с нормальной оценкой, имеют уровень оптимизации FULL. Упрощенный запрос с ошибочной оценкой — уровень TRIVIAL.

Можно было бы подумать, что на ошибочную оценку каким-то образом влияют разные уровни оптимизации. Если бы только запрос, с которого мы начали, не имел уровень FULL. Однако он имеет уровень FULL, но тем не менее ошибка в оценке есть.

Предварительные итоги

Прежде чем дать всем этим странностям объяснения, подведем итоги того, что выяснилось:
1. Упрощенный запрос isnumeric=0 имеет ошибочную оценку в случае TRIVIAL и имеет нормальную оценку в случае FULL.
2. Запрос isnumeric=1 всегда имеет нормальную оценку.
3. Изначальный запрос isnumeric=0 + NL имеет ошибочную оценку, несмотря на то что уровень оптимизации такого запроса FULL.

Объяснение

Сначала необходимо вспомнить, какие стадии проходит запрос прежде чем превратится в план выполнения. Это: parsing/binding, simplification, exploration, optimization (trivial или full). Одним из самых важных параметров, от которого зависит процесс оптимизации, является кардинальность (или количество строк). На протяжении всего процесса, оптимизатор использует механизм оценки кардинальности для принятия того или иного решения.
Укрупненно схему можно представить так:

Интересно было бы посмотреть, что происходит с запросом и оценками на этих (Simplification, Exploration, Optimization) стадиях. В этом могут помочь недокументированные флаги трассировки. Я уже описывал их и их вывод в заметках про оптимизатор, в частности это флаги: 8606 (выводит дерево операторов на этапе упрощения), 8612 (добавляет в это дерево информацию о кардинальности операторов) и общеизвестный флаг 3604, который позволяет весь этот вывод направить на консоль, чтобы мы могли увидеть результат в SSMS.

Выполним запросы и переключимся на вкладку Messages:

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0 
option(recompile, querytraceon 3604, querytraceon 8606, querytraceon 8612);
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile, querytraceon 3604, querytraceon 8606, querytraceon 8612);
go
set showplan_xml off
go

Из этого вывода видно, что уже на этапе упрощения, кардинальность логического оператора LogOp_Select, для запроса isnumeric=0, равна 1. А для случая isnumeric=1, равна 1000.
Если повторить выполнение, но при этом включить флаг запрета тривиального плана.
Мы увидим абсолютно такую же оценку на этапе упрощения:

Но совсем другую в плане:

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

Остается вопрос, почему тогда он не находит хороший план также в случае самого первого запроса (в котором задействован Nested Loops Join)? На этот вопрос ответим чуть позже.
Прежде нужно разобраться, что такого магического происходит между этапами join collapse tree и simplified tree, в результате чего так отличаются оценки.
Дело в том, что в этот момент как раз и происходит процесс вычисления кардинальности и селективности. Можно наблюдать процесс вычисления свойств включив флаг 2373, среди достаточно объемного ввывода между этапами «simplified tree» и «join collapse tree» можно увидеть:

Memory before deriving properties of LogOp_Get: 7
Memory after deriving properties of LogOp_Get: 7
Memory before deriving properties of LogOp_Select: 7
Memory after deriving properties of LogOp_Select: 7

Среди этих свойств есть и селективность с кардинальностью.
Схематично, процесс можно изобразить так:

Посмотрим, что именно скрывается за этим Derive properties.
Для понимания, следует вспомнить, как вычисляется кардинальность (процитирую сам себя из предыдущей заметки):

Кардинальность вычисляется, начиная с нижнего базового оператора, используя статистическую информацию. Поднимаясь вверх по дереву операторов, оценка претерпевает изменения. В частности, например, при вычислении кардинальности операции фильтра происходит вычисление селективности скалярных операторов. Селективность скалярных операторов зависит от контекста реляционного оператора, в котором она вычисляется. Например, селективность предиката a=b в соединении будет зависеть от контекста, и будет разной, например, для inner join и semi join. После этого селективность применяется к входной кардинальности, и мы получаем выходную кардинальность. Прочая статистическая информация так же модифицируется.

Ниже показано, какие именно функции вызываются для определения селективности скалярных операторов и соответствующей кардинальности реляционных.

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

Следует обратить внимание на три функции:

  • CScaOp_Intrinsic::FCalcSelectivity (функция подсчета селективности для isnumeric);
  • CScaOp_Const::FCalcSelectivity (функция подсчета селективности для константы);
  • CScaOp_Comp::ConstConstSelec (функция для подсчета селективности CScaOp_Comp, в случае если сравниваются две константы).

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

Дело в том, что результатом работы функции CScaOp_Comp::ConstConstSelec в случае isnumeric = 0, является 0! Таким образом кардинальность фильтра — 0*1000 = 0 строк. Т.е. оптимизатор считает, что не будет выбрано ни одной строки. Но т.к. это модель оценки, в которой оптимизатор никогда не оценивает число строк нулем, то оптимизатор не умножает на 0, а производит дополнительные исследования, которые в результате дают оценку 1.
Вот как выглядит иерархия вызовов в функции расчет кардинальности фильтра для случая isnumeric=0 и isnumeric=1.

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

Интересно отметить, что на результат работы функции CScaOp_Comp::ConstConstSelec можно повлиять очередным недокументированным флагом трассировки. Это флаг 2328.
Выполним запрос:

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile)
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile, querytraceon 2328)
go
set showplan_xml off

Если мы посмотрим на планы, то увидим, что с этим флагом трассировки оценка строк стала равна 500.

Это произошло потому, что данный флаг трассировки вместо определения селективности исходя из селективности дочерних операторов, указывает серверу использовать догадки, для этого вызывается функция CScaOp_Comp::GuessSelect, которая, в данном случае, определяет селективность как 0.5, т.е. 50%.
Внимание!
Специально добавлю, что данный флаг не является решением проблемы. Он не документирован и используется только для демонстрации концепции.

Upd 20.11.2012: недавно нашел некоторое описание данного флага и его влияния на оценки в блогах MS — Disabling Constant-Constant Comparison Estimation

Теперь, вернемся к первоначальному запросу с Nested Loops Join. У нас остался один не отвеченный вопрос.
Почему для упрошенного запроса (который не требует Nested Loops Join+Key lookup) при полной оптимизации мы получаем план с хорошей оценкой даже для isnumeric = 0, а для исходного запроса опять план с оценкой единица.

Для ответа на этот вопрос, нужно вспомнить, что на этапе полной оптимизации решения принимаются исходя из стоимости. Значит ответ на этот вопрос простой – план выбирается либо потому, что он дешевле, чем альтернатива, либо потому, что альтернативу искать не имеет смысла, т.к. план считается достаточно хорошим (еще есть концепция timeout, но для данной заметки это не важно).

Для подсчета стоимости альтернатив применяется структура Memo. О ней я подробно рассказывал в заметке Full Optimization: Search 0. Сейчас нам важно только то, что мы снова можем залезть внутрь и посмотреть эту структуру, в этом нам поможет флаг 8615.
Посмотрим структуру для упрощенного запроса (обратите внимание, что тривиальный план специально отключен флагом 8757, т.к. при тривиальном плане Memo не строится):

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130,querytraceon 3604,querytraceon 8757,querytraceon 8615)
go
set showplan_xml off
go

На вкладке сообщений вы должны увидеть структуру Memo. Если внимательно посмотреть на вывод, то можно заметить, что в нем присутствуют два оператора фильтра PhyOp_Filter, двух группах. При этом кардинальность одной группы [Card=1], другой [Card=177.828]. В данном случае, дешевле оказывается построить план с группой 9, чем с группой 12.

Процесс построения итогового дерева операторов по структуре memo называется copy out. Как это делает оптимизатор, я могу только догадываться. Алгоритмически, я это пытался изобразить со многими оговорками в заметке Full Optimization: Search 0, но это что называется «на пальцах». К действительности это имеет мало отношения.
Чтобы посмотреть стоимость, того и другого дерева мы прибегнем к простому трюку, заставим оптимизатор построить то и другое дерево, как если бы в memo был только один оператор.
Для того чтобы это сделать, нужно вспомнить, как в memo появляются новые операторы. Они появляются в результате применения к исходным операторам правил преобразования. Таким образом, отключив определенное правило, мы лишим оптимизатор определённых альтернатив.
В данном случае, правило, которое отвечает за генерацию «хорошего» плана это правило SelectToFilter. Отключать правила «на лету» можно при помощи недокументированного хинта queryruleoff, о чем я писал в Отключить правила преобразования в отдельном запросе.
Итак, выполним запрос:

set showplan_xml on
go
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130, querytraceon 3604, querytraceon 8757, querytraceon 8615)
select t.b from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130,queryruleoff SelectToFilter, querytraceon 3604, querytraceon 8757, querytraceon 8615)
go
set showplan_xml off

Если вы посмотрите на вторую структуру memo, то увидите что теперь, там только один оператор PhyOp_Filter.
Посмотрим на планы:

Обратите внимание на толщину стрелок. Второй план, после отключения правила, дает точно такую же оценку после фильтра, что и исходный запрос. Кроме того имеет стоимость незначительно выше чем первый. Выбирая более дешевый план оптимизатор выбирает первый вариант.
Обратите внимание также на оценочное число строк в select. Это не ошибка, так оно и есть, число строк одинаковое. Это потому что во втором плане на самом деле перед вершиной SELECT присутствует оператор Compute Scalar, для которого дана оценка в 177.828 строк. Но об этом чуть позже.

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

Теперь вернемся к исходному запросу.
Выполним:

set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130,querytraceon 3604,querytraceon 8757,querytraceon 8615)
go
set showplan_xml off

Я не буду приводить результат memo, но если вы поищите, то найдете там только один оператор PhyOp_Filter. Он находится в группе 12 с кардинальностью 1.
Из memo видно, что другие альтернативы просто не исследуются. Оптимизатор считает достаточно хорошим тот план, который получает с оценкой единица.

Почему оптимизатор не исследует другие альтернативы? Как мы помним, из-за того, что оптимизатор ошибся с селективностью и считает, что будет выбрана одна строка, он пропихивает предикат isnumeric=0 вниз, вместо того, чтобы сначала вычислить выражение isnumeric и затем отфильровать.
В «хорошем» плане, если идти от узлов к вершине плана, сначала идет compute scalar, потом filter.
В «плохом» сначала filter, потом compute scalar, который мы не видим в плане. Однако, он есть. «Видишь суслика? Нет. И я нет, но он есть!»

За раннюю фильтрацию при выборке отвечает правило SelResToFilter. Именно оно в сочетании с неверной селективностью дает такой эффект. Выполним запрос, как есть (с включенным правилом), с выключенным правилом и с единицей, после чего посмотрим на планы.

set showplan_xml on
go
--as is
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130)
--disable rule SelResToFilter
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 9130,queryruleoff SelResToFilter)
-- with value 1
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile)
go
set showplan_xml off


Как видно из результатов, после отключения правила ранней фильтрации, мы получили точно такой же нормальный план, как и для значения 1.
Вернемся к скрытому оператору Compute Scalar и суслику, который есть.

Склонность к ошибкам в более поздних операторах плана

Как известно, ошибки оценки часто имеют свойство накапливаться и распространяться по плану. Например, ошибившись где-то внизу плана и оценив количество строк как 1 вместо реальных 10, оптимизатор, выполняя дальше по плану, например, соединение с таблицей, может умножить эту ошибку пропорционально количеству строк в соединяемой таблице и т.д.
С этой ошибкой интереснее. Как мы видели, корневой узел плана содержал одинаковое оценочное число строк как для плана с правильными оценками, так и для оценки в одну строку.

Возникают вопросы.
Во-первых, почему на вход узлу плана select (я намерено не называю его оператором, т.к. он таковым не является) подается одна строка (тонкая стрелка), а оценочное число строк показывается как 177.828.
Во-вторых, что если это будет не план целиком, а какая-то ветка более сложного плана.

Ответ на первый вопрос можно найти, выполнив исходный запрос с флагом трассировки вывода финального дерева операторов 8607, о котором я говорил в заметке Full Optimization: Search 0.

set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0
option(recompile,querytraceon 3604,querytraceon 8607,querytraceon 8612)
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 1
option(recompile,querytraceon 3604,querytraceon 8607,querytraceon 8612)
go
set showplan_xml off

На вкладе сообщений мы увидим (сокращено для краткости):

--isnumeric = 0
*** Output Tree: ***
  PhyOp_ComputeScalar [Card=177.828]
     PhyOp_Apply lookup TBL: t (0) (x_jtInner) [Card=1]
        PhyOp_Filter [Card=1]
           PhyOp_Range TBL: t(2) ASC
           ...
--isnumeric = 1
*** Output Tree: ***
  PhyOp_Filter [Card=177.828]
     PhyOp_ComputeScalar [Card=1000]
        PhyOp_Apply lookup TBL: t (0) (x_jtInner) [Card=1000]
           PhyOp_Range TBL: t(2) ASC
           ...

Как видно из результата, операции фильтра и вычисления скаляра имеют в деревьях разный порядок. В запросе с isnumeric = 1, дерево выглядит более логично.
Читаем снизу вверх:
прочитать диапазон(PhyOp_Range), подтянуть данные из кластерного индекса (PhyOp_Apply), посчитать выражение isnumeric(PhyOp_ComputeScalar), отфильтровать по этому выражению(PhyOp_Filter).
Для первого дерева, результат выглядит не таким логичным. Видно, что для фильтра кардинальность посчитана неверно, но самый верхний оператор PhyOp_ComputeScalar, его оценка и распространится далее по плану, если это будет ветка какого-то более сложного плана.
Чтобы продемонстрировать это, выполним запрос:

set showplan_xml on
go
declare @t table (a int primary key);
select t.b,t.c from t with(index(ix_b)) cross join @t t2 where isnumeric(t.b) = 0 option(recompile, querytraceon 9130);
go
set showplan_xml off
go

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

Удивительная картина, на вход Nested Loops Join поступает по одной строке, а на выходе 177.828 строк! Но мы уже знаем, откуда такая оценка, благодаря скрытому оператору Compute Scalar.

Еще один простой запрос:

select t.a,t.b,t.c into #t1 from t;
alter table #t1 add constraint pk_t1 primary key (a);
go
set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) join #t1 t1 on t1.a = t.a where isnumeric(t.b) = 0 option(recompile, querytraceon 9130);
go
set showplan_xml off
go
drop table #t1;

План:

В операторе сортировки снова откуда ни возьмись появляются те самые 177 строк, но ошибка в оценке в одну строку вместо 177, не расползается далее по плану.
Итак, отвечая на вопрос, склонна ли ошибка распространяться далее по плану, можно ответить – скорее нет, чем да. Но необходимо помнить, что план выбирается из всевозможных альтернатив, и если какой либо оператор в плане будет помещен до невидимого Compute Scalar, который исправляет ошибку, то влияние возможно.
Выполним его со включенной опцией Include Actual Plan:

set statistics io on
go
select t1.c,t2.c from t t1 join t t2 on t1.b = t2.b where isnumeric(t2.b) = 0 and isnumeric(t1.c)=0 option(recompile);
select t1.c,t2.c from t t1 join t t2 on t1.b = t2.b where isnumeric(t2.b) = 1 and isnumeric(t1.c)=0 option(recompile);
go
set statistics io off
go

Результаты:

Table ‘t’. Scan count 1001, logical reads 3182, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘t’. Scan count 2, logical reads 62, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


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

Несколько возможных решений

1. Исправить оценку.
Отбить оптимизатору охоту пропихивать вниз предикат можно заменив его на локальную переменную следующим образом

set showplan_xml on
go
declare @0 int = 0;
select t.b,t.c from t with(index(ix_b)) where isnumeric (t.b) = @0;
go
set showplan_xml off
go

При этом, нужно иметь ввиду, что это поможет, только если @0 является локальной переменной. Если @0 это параметр, то к нему в полной мере применимо прослушивание параметров (parameter sniffing).
Пример:

create proc proc1
	@p int
as
select t.b,t.c from t where isnumeric(t.b) = @p;
go
--1. both "bad" plans
exec sp_recompile 'proc1';
exec proc1 0; --sniff value 0, build "bad" plan
exec proc1 1; --use same wrong plan for value 1 also

--2. both "good" plans
exec sp_recompile 'proc1';
exec proc1 1; --sniff value 1, build "good" plan
exec proc1 0; --use good plan for value 0 also
go
drop proc proc1;
go

В первом случае, оба плана имеют вид:

Во втором случае:

Если @0 это локальная переменная, но в запросе, для каких-то целей, используется option(recompile), то такой прием тоже не сработает.
Если вы хотите избавиться от эффекта прослушивания параметров или влияния option(recompile), вы можете использовать подсказку «optimize for», это работает.

declare @0 int = 0;
select t.b,t.c from t where isnumeric(t.b) = @0 option(recompile, optimize for(@0 unknown));

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

alter table t add isnum as isnumeric(t.b);
go
set showplan_xml on
go
select t.b,t.c from t with(index(ix_b)) where isnumeric(t.b) = 0;
go
set showplan_xml off
go
alter table t drop column isnum;
go

2. Исправить выбор индекса/соединения и т.д.
Можно воспользоваться хинтами запросов, в частности есть богатый арсенал по выбору типов соединения, форсирования поиска или просмотра, выбору индекса и т.д. То есть, если оптимизатор ошибся в оценке и не смог выбрать индекс сам из-за ошибки, то можно ему помочь. Этот как раз один из тех случаев, для чего вообще были придуманы хинты.

Итог

Основная причина медленного выполнения — выбор неудачного плана вследствие фильтрации по предикату на ранней стадии, которое в свою очередь стало следствием неверной оценки селективности предиката isnumeric(column)=0. Проявляется ли ошибка всегда? Если применяется соответствующее правило ранней фильтрации, то ошибка в оценке присутствует всегда, однако, проявляется в итоговом плане и становится заметной для пользователя не всегда. Явным образом в плане она проявляется, только если оптимизатор сочтет план с ошибочной оценкой более дешевым или достаточно хорошим, чтобы перестать искать дальше. Проявляется ли ошибка в плане позднее? Простые эксперименты показывают что нет, однако, принимая во внимание механизм построения плана, полностью исключить такую вероятность нельзя.

Заключительные мысли

  • При тестировании использовались версии сервера 2008R2 rtm, 2012 rtm express.
  • Вы можете сделать все описанные манипуляции на таблице в 100 000 тысяч строк и получить ту же оценку в 1 строку.
  • Вы можете проделать все описанные выше манипуляции и получить тот же результат с функцией isdate =)
  • Всех работающих с сиквел сервером я призываю изначально хранить данные в типизированных колонках, если значение число, храните в колонке числового типа, если дата, то в колонке типа дата – это поможет избежать (ну или хотя бы сократить число) вызовов таких функций в боевых запросах, и вообще избавит от очень многих проблем.
  • Разработчикам сиквел сервера, хочется пожелать отражать происходящее в запросах в планах максимально близко к действительности. Если где-то есть фильтр, хочется видеть этот фильтр, если где-то есть compute scalar, тоже хочется его видеть, это помогло бы делать анализ запроса более простым ясным, без обращений к недокументированным средствам.

Connect Item, можно зайти и проголосовать за исправление ошибки.

Что еще почитать

Query Optimizer Deep Dive
More Undocumented Query Optimizer Trace Flags

 

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

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

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