Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 77
Ситуация: имеется вот такая вьюшка:
ALTER view dbo.vw_claim_entities as
select
  e.claim_id,
  e.id,
  e.entity_id,
  r.name,
  r.description,
  e.permit,
  d.term permit_name,
  d.substitutes permit_name_subst,
  e.free_text,
  row_number() over(partition by e.claim_id order by e.id) ord,
  u.fullname responsible_name,
  u.id responsible_id,
  e.reason
from dbo.tb_claim_entities e
join dbo.tb_claim_header h on e.claim_id=h.id
join dbo.tb_nc_resources nr on h.type=nr.id
join dbo.tb_claim_resources r on r.type=h.type and (r.id=e.entity_id or nr.allow_multiple=1)
left join dbo.tb_dicdata d on d.code=e.permit and d.dic=nr.permits_dic
left join syn.vw_users_light u on u.id=r.responsible_id and nr.has_responsible=1

Вот такой запрос из неё:
select
  name, description, permit_name permit, free_text, responsible_name, reason
from dbo.vw_claim_entities
where claim_id=22824
order by ord

отрабатывает за доли секунды, и выдает вот такой план:
+
Картинка с другого сайта.

При вот таком видоизменении запроса:
declare
  @id int=22824;
select
  name, description, permit_name permit, free_text, responsible_name, reason
from dbo.vw_claim_entities
where claim_id=@id
order by ord

запрос выполняется гораздо дольше (~3-5 секунд), и план кардинально меняется:
+
Картинка с другого сайта.

я читал статьи "Slow in the Application, Fast in SSMS" (в переводе SomwhereSomehow), в частности, часть про эффекты parameter sniffing. Из предложенных способов помогла только OPTION(RECOMPILE) - с ней второй запрос имеет время отработки и план исполнения, аналогичный первому. В принципе, вполне можно дальше жить и с этой опцией, но хотелось бы понять, можно ли вообще от второго случая добиться плана, идентичного первому? Пытался зафиксировать план хинтами - после прописывания индексов для таблиц tb_claim_header и tb_claim_entities, и задания для tb_claim_header хинта FORCESEEK, запрос вообще валится с ошибкой

Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

И еще. Вот такой вариант запроса:
declare
  @claim_id int=22824;
select
  e.claim_id,
  r.name,
  r.description,
  e.permit,
  d.term permit_name,
  e.free_text,
  row_number() over(partition by e.claim_id order by e.id) ord,
  u.fullname responsible_name,
  e.reason
from dbo.[tb_claim_entities] e
join dbo.tb_claim_header h on e.claim_id=h.id
join dbo.tb_nc_resources nr on h.type=nr.id
join dbo.tb_claim_resources r on r.type=h.type and (r.id=e.entity_id or nr.allow_multiple=1)
left join dbo.tb_dicdata d on d.code=e.permit and d.dic=nr.permits_dic
left join syn.vw_users_light u on u.id=r.responsible_id and nr.has_responsible=1
where e.claim_id=@claim_id
order by ord

(фактически - текст view+условие) выполняется аналогично самому первому вышеприведенному запросу. При вот таком изменении:
declare
  @claim_id int=22824;
select v.* from (
select
  e.claim_id,
  r.name,
  r.description,
  e.permit,
  d.term permit_name,
  e.free_text,
  row_number() over(partition by e.claim_id order by e.id) ord,
  u.fullname responsible_name,
  e.reason
from dbo.[tb_claim_entities] e
join dbo.tb_claim_header h on e.claim_id=h.id
join dbo.tb_nc_resources nr on h.type=nr.id
join dbo.tb_claim_resources r on r.type=h.type and (r.id=e.entity_id or nr.allow_multiple=1)
left join dbo.tb_dicdata d on d.code=e.permit and d.dic=nr.permits_dic
left join syn.vw_users_light u on u.id=r.responsible_id and nr.has_responsible=1
) v
where claim_id=@claim_id
order by ord

- аналогично второму вышеприведенному запросу. Т.е. какую-то свою роль играет заворачивание запроса во вьюшку. Как при этом меняется логика оптимизатора?
Дефрагментацию индексов проверял, сильно дефрагментированные ребилдил/переорганизовывал - на формирование вышеприведенной картины это не повлияло.
select @@version

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) 
Jul 9 2008 14:17:44
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)

Детальный планы исполнения в аттаче.

К сообщению приложен файл (plans.7z - 16Kb) cкачать
16 апр 15, 13:37    [17523684]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
komrad
Member

Откуда:
Сообщений: 5735
WinterGraveyard
Как при этом меняется логика оптимизатора?
Дефрагментацию индексов проверял, сильно дефрагментированные ребилдил/переорганизовывал - на формирование вышеприведенной картины это не повлияло.


у вас сиквел RTM - поставьте актуальные SP и потом можно снова на проблему взглянуть
16 апр 15, 13:49    [17523797]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Поле claim_id nullable?
16 апр 15, 13:54    [17523841]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
WinterGraveyard,

Знакомый эффект. Давно обсуждалось и тут в том числе.
row_number() тому виной.
Попробуйте все те же запросы, только закомментируйте row_number().
16 апр 15, 14:04    [17523945]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 77
komrad,

да, я знаю про RTM, но обновить не могу - не моя компетенция (иначе давно уже бы сделал, поскольку там есть один очень неприятный баг с XQuery, исправленный в релизе)

Владислав Колосов
Поле claim_id nullable?

Нет, оно not null, и входит в составной уникальный ключ.

SomewhereSomehow,

понятно, спасибо, у меня были подозрения на эту тему. Причем ситуацию спасает даже просто упорядочение выборки по любому другому полю, кроме ord (которое производное от row_number). Но это поле нужно, и нужна сортировка именно по нему. Хорошо, буду использовать OPTION(RECOMPILE)
16 апр 15, 14:17    [17524071]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
komrad
Member

Откуда:
Сообщений: 5735
WinterGraveyard
Хорошо, буду использовать OPTION(RECOMPILE)

а OPTIMIZE FOR UNKNOWN пробовали?
16 апр 15, 14:44    [17524380]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 77
komrad,

да, пробовал - эффекта не дало.
16 апр 15, 14:46    [17524400]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
WinterGraveyard,

Более подробный ответ на ваш вопрос, если интересно.

Сперва упростим репро.
Использую тестовую бд opt.
+ simple repro

use opt;
go
-- 1. Index Seek
declare @a int = 1;
select t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1 where a = @a
go
-- 2. Index Scan
declare @a int = 1;
select * from
(
	select  t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = @a
go
-- 3. Index Seek
declare @a int = 1;
select * from
(
	select  t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = 1
go
-- 4. Index Seek
declare @a int = 1;
select * from
(
	select  t1.a, r = row_number() over(partition by t1.a order by (select null)) from t1
) t
where t.a = @a
option(recompile)
go



Теперь по вопросам.

View vs. Query
Когда в запросе используется представление, сервер, а точнее его компонент алгебраизатор - всегда раскрывает текст представления в запрос. Одно исключение - индексированное представление с подказкой noexpand. Иначе - просто подставляет вместо представления - текст. Т.е. представление это некторый синтаксический сахар. Есть оговорки - например, он кэширует дерево представления (можете наблюдать в кэше такие объекты, как Bound Tree), чтобы не парсить его каждыйраз. И использует метеданные представления (эффект, когда пишут select * в представлении, потом меняют внутри что-то, а столбцы остаются прежние, до момента обновления вью). В остальном - сводится к подставлению текста.

Итого - сняли проблему - вью или нет - ваш второй запро для оптимизатора эквивалентен четвертому.

Option recompile vs none
До 2008 сервера эта опция говорила оптимизатору буквально следующее: при выполнении, пррослушай заново все параметры и перекомпилируй.
Начиная с 2008, разработчики сиквела осознали, что в момент рекомпиляции уже идет выполнение запроса, все необходимые блокировки и т.д. наложены и можно пойти дальше, можно не просто прослушать параметр, вообще заменить параметр на константу, и оптимизировать более агрессивно.

Например select * from t1 where @a = 1. Если просто прослушаем, то построим план либо для всех строк, либо для 0. Но план в кэш не записывается. Зачем тогда строить универсальный план, который учитывает разные значения переменной. Если в момент выполнения в переменной @a значение 2, план в кэш мы не помещаем, то запро превращается в select * from t1 where 2 = 1. такой запро никогда ничего не вернет - и сервер упрощает план просто до сканирвоания константы - Constant Scan.

Итого - опция рекомпиляции - фактически равна тому, как если бы вы прописали константу, т.е. запросу 2 из вашего поста.

Прослушивание параметров
Здесь не играет роли

Проталкивание предикатов
Фактически, мы свели вопрос к тому, что:
Если используется фильтр по неизвестной переменной в самом запросе - это поиск. Если используется во внешнем запросе это просмотр. Если посмотреть упрощенное репро, видно, что в случае 1 - поиск, в случае 2 - просмотр. Почему?

Дело в проталкивании предикатов и в оконной функции.
Оптимизатор не должен нарушать логику/семантику/смысл запроса.

Если есть оконная функция во вложенном запросе у него есть два выбора:
1) протолкнуть условие поиска во внутренний запрос, отфильтровать и потом пронумеровать
2) пронумеровать во внутреннем запросе, а потом отфильтровать во внешнем.

В случае 1) - можно выполнить поиск, в случае 2) - нужно выполнить сначала нумерацию и сканирование, а потом отфильтровать.
Ясно, что результаты будут разные.
например данные:
Арбуз
Барабан
Велосипед

Есть нумерация по алфавиту и фильтрация по слову Велосипед (where name = 'Велосипед').

1) первый подход - нумеруем потом фильтруем:

Нумеруем:
1 Арбуз
2 Барабан
3 Велосипед

Фильтруем:
3 Велосипед

Результат:
3 Велосипед

2) второй подход - фильтруем потом нумеруем:

Фильтруем:
Велосипед

Нумеруем
1 Велосипед

Результат:
1 Велосипед

Очевидно разные результаты - нельзя проталкивать предикат, т.е. фильтровать до нумерации.

Но! Есть же указание partition by. Т.е. разделить по группам, где критерий группы - условие фильтра.
Уберите в репро partition by t1.a и увидите, что никакого проталкивания предикатов, кроме первого случая нет. Все потому, чтобы не столкнуться ситуацией описанной выше.
Теперь допустим, мы говорим, что нумеровать надо в пределах группы, а группа определяется условием фильтра.
В таком случае, если значение фильтра известно (константа или option recompile - что по сути одно и то же) - оптимизатор проталкивает фильтрацию вниз и может использовать поиск (примеры 3 и 4 из репро).
Но если значение фильтра неизвестно - то оптимизатор, будучи консервативным и чтобы не нарушать логику запоса - не будет проталкивать фильтрацию вниз, а значит у него не будет условия поиска. Так что форсируй поиск по индексу или нет - искать-то не по чему. По этому, ошибка построения плана с форсированными индексами.

Ситуация старая, вот мы обсуждали ее с Полом Уайтом в комментах к статье три года назад: The Curious Case of the Optimizer that doesn’t
А вот обсуждали на форуме:
Не могу понять физику процесса в параметризированном запросе

Для решения вашей проблемы, есть два варианта:
1) отказаться от представления
2) использовать option(recompile)

Баланс между удобством поддержки кода и производительностью (затраты на рекомпиляцию)
Выбирайте меньшее из зол для вашей системы и вперед =)
Удачи!
16 апр 15, 15:09    [17524656]     Ответить | Цитировать Сообщить модератору
 Re: Parameter sniffing: как обойтись без OPTION(RECOMPILE)  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 77
SomewhereSomehow,

спасибо, теперь все понятно. Да, лучше всего в моем случае, думаю, будет просто использовать запрос.
16 апр 15, 15:42    [17524910]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить