Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Есть запрос (прошу прощения за синтаксис, это выдрано из 1C, и сгенерировано тоже автоматически 1Сом):

DECLARE @P1 NUMERIC(6, 0) = 201611
	,@P2 NUMERIC(6, 0) = 201611
	,@P3 BINARY (4) = 0x0000006F
	,@P4 NUMERIC(1, 0) = 1

SELECT T1._IDRRef
FROM _Document111 T1 WITH (NOLOCK)
WHERE (
		(T1._Fld1712 >= @P1)
		AND (T1._Fld1712 <= @P2)
		)
	AND T1._IDRRef IN (
		SELECT CASE 
				WHEN T2._Fld2977_TYPE = 0x08
					AND T2._Fld2977_RTRef = @P3
					THEN T2._Fld2977_RRRef
				ELSE 0xFF
				END AS Q_001_F_000RRef
		FROM _AccumRg2973 T2 WITH (NOLOCK)
		WHERE (T2._Fld2976 = @P4)
		)

Он имеет радикально разный априорный и фактический планы, если добавить OPTION (RECOMPILE) (см. приложенную картинку):
Для сравнения, Ad hoc запрос имеет план, совпадающий с с планом после OPTION (RECOMPILE), третий план на той же картинке:
SELECT T1._IDRRef
FROM _Document111 T1 WITH (NOLOCK)
WHERE (
		(T1._Fld1712 >= 201611)
		AND (T1._Fld1712 <= 201611)
		)
	AND T1._IDRRef IN (
		SELECT CASE 
				WHEN T2._Fld2977_TYPE = 0x08
					AND T2._Fld2977_RTRef = 0x0000006F
					THEN T2._Fld2977_RRRef
				ELSE 0xFF
				END AS Q_001_F_000RRef
		FROM _AccumRg2973 T2 WITH (NOLOCK)
		WHERE (T2._Fld2976 = 1)
		)

При этом запрос без OPTION (RECOMPILE) считается пару часов (и даже больше), а с опцией - менее 10 с. И адхок считается тоже 10 с.
Но это 1С, и, как вы понимаете, никаких хинтов вставить - не получится.

Почему так происходит? И как с этим бороться???
Сервер:
Microsoft SQL Server 2014 (SP2-GDR) (KB3194714) - 12.0.5203.0 (X64)
Sep 23 2016 18:13:56
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

Статистика по таблицам - свежая, с полным сканированием, индексы перестроены.
Даже ребилд таблиц сделан!
20 дек 16, 18:01    [20028658]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Вот планы

К сообщению приложен файл. Размер - 138Kb
20 дек 16, 18:02    [20028661]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

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

потому, что для разного значения параметров может выбираться разный план. Сервер запомнил несколько планов и не всегда попадает в оптимальный.
RECOMPILE вызывает очистку кэша планов выполнения для данного запроса. То же самое, например, может происходить после переиндексации таблиц, входящих в запрос.
Можете попробовать использовать руководство планов с параметризацией для фиксации хорошего плана. Но это не универсальная таблетка, т.к. подходит для запросов, которые сохраняют написание.
20 дек 16, 18:11    [20028690]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Иногда помогает пересчёт статистик. Если не копаться в планах, можно попробовать.
20 дек 16, 18:13    [20028697]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Пересчет статистик тоже может привести к удалению плана из кэша.
20 дек 16, 18:15    [20028700]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
uaggster,

RECOMPILE пробрасывает значения переменных в запрос из-за этого актуальный план строится на актуальных значениях, а не скомпилированных(в кеше), Как бороться с parameter sniffing можете гуглить, вариантов не так и много :)
прибить хинт можено и вне 1С : plan guide, query store

автор
Он имеет радикально разный априорный и фактический планы,

как только не переводят estimate :)
20 дек 16, 18:23    [20028726]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
чего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане
20 дек 16, 18:25    [20028738]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Мистер Хенки
чего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане

а в хорошем?
20 дек 16, 18:28    [20028745]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
CASE
WHEN T2._Fld2977_TYPE = 0x08
AND T2._Fld2977_RTRef = 0x0000006F
THEN T2._Fld2977_RRRef
ELSE 0xFF
END AS Q_001_F_000RRef
вот это сделать как хранимое вычисляемое и индекс на него и _Fld2976 может и взлетит
20 дек 16, 18:29    [20028746]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
Мистер Хенки
чего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане

а в хорошем?

а "хороший" не очень то хорош. Жрет проц как в последний раз
20 дек 16, 18:32    [20028761]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Мистер Хенки
CASE
WHEN T2._Fld2977_TYPE = 0x08
AND T2._Fld2977_RTRef = 0x0000006F
THEN T2._Fld2977_RRRef
ELSE 0xFF
END AS Q_001_F_000RRef
вот это сделать как хранимое вычисляемое и индекс на него и _Fld2976 может и взлетит

переменную @P3 тоже в вычисляемое поле?

автор
а "хороший" не очень то хорош. Жрет проц как в последний раз

парралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп
20 дек 16, 18:33    [20028769]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
Мистер Хенки
CASE
WHEN T2._Fld2977_TYPE = 0x08
AND T2._Fld2977_RTRef = 0x0000006F
THEN T2._Fld2977_RRRef
ELSE 0xFF
END AS Q_001_F_000RRef
вот это сделать как хранимое вычисляемое и индекс на него и _Fld2976 может и взлетит

переменную @P3 тоже в вычисляемое поле?

автор
а "хороший" не очень то хорош. Жрет проц как в последний раз

парралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп

индекс по вычисляемому и _Fld2976
20 дек 16, 18:37    [20028783]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Мистер Хенки,

чукча не читатель
оригинал запроса
CASE 
	WHEN T2._Fld2977_TYPE = 0x08
	AND T2._Fld2977_RTRef = @P3
	THEN T2._Fld2977_RRRef
	ELSE 0xFF
END AS Q_001_F_000RRef
20 дек 16, 18:39    [20028788]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
парралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.
20 дек 16, 18:41    [20028797]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Мистер Хенки
TaPaK
парралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.

ну раз всё в памяти уместилось, то не так и много, но как вы предлагаете менять запрос в не меняемом приложении?
20 дек 16, 18:50    [20028825]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
Мистер Хенки
пропущено...

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.

ну раз всё в памяти уместилось, то не так и много, но как вы предлагаете менять запрос в не меняемом приложении?

ну по идее индекс должен подхватиться запросом, а для этого приложение менять не надо. Вот поле вычисляемое придется строить, не знаю позволяет ли такие вольности 1с. Я же предлагаю, а не настаиваю.
20 дек 16, 18:53    [20028844]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Мистер Хенки,

вы принципиально не видите переменную в запросе????
20 дек 16, 18:56    [20028862]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
o-o
Guest
Мистер Хенки
TaPaK
парралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.

...и т.к. объемы немаленькие, то в самый раз NL!!!
сверху поди строк под миллион и снизу тоже, в аккурат такое считать через NL,
там логические чтения зашкаливают.
uaggster, выньте ему статистику планов и покажите, cколько там чтений в каждом случае, пусть ужаснется
----
в первом плане server считает, что в верхней таблице по условию пара строк отберется , и в нижней тоже,
сканы оценены в 0.
в следующих двух он уже видит, что реально каждый скан будет выдавать тучу строк, потому и выбрал хэш джойн.
какое счастье, что решает все же программа, а не Мистер Хенки,
последнего надо посадить вручную соединять те две таблицы, ага, в цикле
20 дек 16, 18:59    [20028874]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
TaPaK
Мистер Хенки,

вы принципиально не видите переменную в запросе????

а это
AND T2._Fld2977_RTRef = @P3 
- не заметил . Тогда конечно не получится вычисляемое поле построить. Печально конечно.
20 дек 16, 19:10    [20028920]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
o-o
Мистер Хенки
пропущено...

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.

...и т.к. объемы немаленькие, то в самый раз NL!!!
сверху поди строк под миллион и снизу тоже, в аккурат такое считать через NL,
там логические чтения зашкаливают.
uaggster, выньте ему статистику планов и покажите, cколько там чтений в каждом случае, пусть ужаснется
----
в первом плане server считает, что в верхней таблице по условию пара строк отберется , и в нижней тоже,
сканы оценены в 0.
в следующих двух он уже видит, что реально каждый скан будет выдавать тучу строк, потому и выбрал хэш джойн.
какое счастье, что решает все же программа, а не Мистер Хенки,
последнего надо посадить вручную соединять те две таблицы, ага, в цикле

В эстимейтед они считает, что скан Document111 обойдется в 600 000 строк, а Accum - 4,5 миллиона.
В актуальном - 10 000 Х 8,5 миллионов
20 дек 16, 19:28    [20028961]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Тут основная проблема в том, что это не человек писал. Это генератор 1C накодил.
И перестроить запрос - не получится.
Кстати, если построить подсказываемый индекс - то это никак делу не помогает.

Причем я сомневаюсь, что это параметер сниффинг. Потому что после DBCC FREEPROCCACHE - ничего не меняется.
Статистика актуальная, я первым делом пересчитал ее через Exec sp_MSForEachtable 'update statistics ? with fullscan'

Нет никакого трейс-флага, который заставлял бы сервер принудительно рекомпилить все запросы?
Понятно, что это не очень хорошо, но как временное решение, бока идет препирательство с разработчиками - сгодилось бы!
20 дек 16, 20:55    [20029281]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
invm
Member

Откуда: Москва
Сообщений: 8750
uaggster
Нет никакого трейс-флага, который заставлял бы сервер принудительно рекомпилить все запросы?
Нету.
Plan guide вам поможет.
20 дек 16, 21:17    [20029363]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Basma4
Member

Откуда:
Сообщений: 124
если база в уровне соместимости 120, попробуй включить старую оценку кардинальности с помощью TF 9481 и протестировать запрос с ним.
21 дек 16, 09:43    [20030367]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Basma4, да, в 120
Но трейсфлаг не помог. Оценка кардинальности осталась прежней.
Причем оптимизатор по-прежнему ошибается.
21 дек 16, 10:31    [20030650]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
uaggster
Basma4, да, в 120
Оценка кардинальности осталась прежней.

это какой? 5+?

автор
Причем оптимизатор по-прежнему ошибается.
все любят оскорблять оптимизатор, а он для вас старается и выбирает самый дешёвый по ресурсам план
21 дек 16, 10:33    [20030659]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
кстати, включите, если не включён Trace Flag 4199
21 дек 16, 10:40    [20030699]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

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

автор
Потому что после DBCC FREEPROCCACHE - ничего не меняется.

С чего бы изменилось, если это процедурный кэш, а не кэш запросов.
21 дек 16, 11:00    [20030844]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
Владислав Колосов
uaggster,

автор
Потому что после DBCC FREEPROCCACHE - ничего не меняется.

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

Эээ... еще раз, по пунктам... Есть какой то отдельный кэш запросов?
И параметризованный запрос с точки зрения энжин - это не хранимая процедура?
21 дек 16, 11:17    [20030930]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
uaggster
Владислав Колосов
uaggster,

пропущено...

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

Эээ... еще раз, по пунктам... Есть какой то отдельный кэш запросов?
И параметризованный запрос с точки зрения энжин - это не хранимая процедура?

с точки зрения "энжин" (за что его так) ad hoc, а не хранимая процедура. Но всё равно они в PROCCACHE...
21 дек 16, 11:27    [20030974]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

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

Добрый день.
Выбор того или иного типа соединения в плане запроса обусловлен двумя главными причинами:
1) стоимость
2) принципиальная "выполнимость" логической операции соединения физическим оператором соединения

В вашем случае, играет роль второй пункт.

Попробуйте дописать в ваш запрос, вместо option(recompile) - option(hash join), вы получите ошибку:
"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."

Чтобы понять почему, нужно посмотреть на конструкцию
		T1._IDRRef IN (
		SELECT 
			CASE 
				WHEN T2._Fld2977_TYPE = 0x08 AND T2._Fld2977_RTRef = @P3
				THEN T2._Fld2977_RRRef
				ELSE 0xFF
			END AS Q_001_F_000RRef

Когда вы пишите "столбец1 in (столбец2 из подзапроса)" это означает логическую операцию полусоединения (Semi Join) по условию столбец1 = столбец2.

В вашем же запросе, в зависимости от @P3 это условие может быть либо "столбец1 = столбец2", либо "столбец1 = константа".
Соединение хэшированием (Hash Join) или слиянием (Merge Join, кроме full) - требует хотя бы одного предиката равенства в соединении (equijoin), т.е. операцию "столбец1 = константа" (можно представить себе это как некую вариацию "cross join"), выполняет только оператор Nested Loops. Который раз за разом, по числу строк из верхней таблицы, сканирует нижнюю, а точнее заполняет Spool и читает оттуда. Это очень долго.

Если же вы пишете option(recompile), то оптимизатор подставляет вместо параметров константы, так как если бы они были написаны литералами в запросе (это называется Parameter Embedding Optimization), тогда проблема с переменным условие соединения уходит и оптимизатор сразу переключается более выгодный алгоритм соединения, т.е. данном случае Hash Join.

Что тут в итоге можно сказать, причина не в стоимости и не в плохих оценках, а в том, что "универсальные запросы работают универсально плохо". Единственный вменяемый вариант, пока 1С-ники переписывают запрос, это, как уже тут советовали - руководство планов (plan guide), которое бы применяло к этому запросу хинт "recompile". В хелпе такой вариант даже указан в качестве наиболее типичных примеров использования plan guides:
Attach Query Hints to a Plan Guide
Common Query Hints Used in Plan Guides
Queries that can benefit from plan guides are generally parameter-based, and may be performing poorly because they use cached query plans whose parameter values do not represent a worst-case or most representative scenario. The OPTIMIZE FOR and RECOMPILE query hints can be used to address this problem. OPTIMIZE FOR instructs SQL Server to use a particular value for a parameter when the query is optimized. RECOMPILE instructs the server to discard a query plan after execution, forcing the query optimizer to recompile a new query plan the next time that the same query is executed. For an example, see Plan Guides.
21 дек 16, 12:45    [20031470]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
o-o
Guest
SomewhereSomehow
Попробуйте дописать в ваш запрос, вместо option(recompile) - option(hash join), вы получите ошибку
...
Если же вы пишете option(recompile), то оптимизатор подставляет вместо параметров константы,
...
тогда проблема с переменным условие соединения уходит

...а я еще думаю, озверел он что ли, при оценках
автор
они считает, что скан Document111 обойдется в 600 000 строк, а Accum - 4,5 миллиона

выбрать NL, это воистину надо быть Мистером Хенки.
т.е. меня это смутило, но нисколько не заставило задуматься о том, что hash join может быть невозможен,
ведь он есть на соседнем плане.
спасибо!!!
21 дек 16, 13:39    [20031780]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow
проблема с переменным условие соединения уходит

Перечитал, решил уточнить на всякий случай.
Это не то, что у нас во время выполнения Has Join в столбце _Fld2977_RTRef не может быть значения <> 0x0000006F и выполнение не может пойти по ветке "столбец1 = константа", а то что условие перестает быть переменным и альтернатива "столбец1 = константа" / "столбец1 = столбец2" превращается в "столбец1 = столбец2", где в роли "столбца2" выступает выражение, известное в момент (ре)компиляции и сервер может построить экви соединение, что он и делает:
+
Картинка с другого сайта.


И не может получить такое же условие, когда имеется параметр и переменное условие соединения:
+
Картинка с другого сайта.
21 дек 16, 14:19    [20032011]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow,

А почему нельзя вычислять кейс и хешджойнить по результату?
21 дек 16, 16:46    [20032915]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6526
Любопытный гость
SomewhereSomehow,

А почему нельзя вычислять кейс и хешджойнить по результату?

а вот если вы посмотрите на его план с recompile, то узнаете что когда значения известны так и есть
21 дек 16, 16:50    [20032957]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любопытный гость,

Я объяснил выше почему, потому что оптимизатор не знает какое будет значение параметра во время выполнения и должен гарантировать "универсальный план" под все возможные значения, а предикат соединения может меняться из-за параметра, а hash-у обязательно нужен один equi join, вот простой пример:
use tempdb;
go
if object_id ('t1') is not null drop table t1;
if object_id ('t2') is not null drop table t2;
create table t1(a int not null, b int not null, c int check (c between 1 and 50), constraint pk_a primary key(a));
create table t2(b int not null, c int, d char(10), constraint pk_b primary key(b));
insert into t1(a,b,c) select number, number%100+1, number%50+1 from master..spt_values where type = 'p' and number between 1 and 1000;
insert into t2(b,c) select number, number%100+1 from master..spt_values where type = 'p' and number between 1 and 1000;
go

-- 1) OK - hash join with hint
declare @p int = 1;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile, hash join);
go

-- 2) Error: 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.
-- No join condidition, similar to "cross join", not supported by hash
declare @p int = null;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile, hash join);
go

-- 3) Ok - no hints, same @p = null value - Loops join
declare @p int = null;
select 
	*
from 
	t1
where
	t1.a in (select case when t2.c = @p then t2.b else 1 end from t2)
option(recompile);
go

Во всех случаях я добавил рекомпиляцию.

В первом случае, я ограничил выбор только хэш соединением и в качестве значения параметра задал 1.
При рекомпиляции условие было преобразовано в "case when t2.c = 1 then t2.b else 1 end".

Во втором случае, параметр null, при рекомпиляции условие было преобразовано в "case when t2.c = null then t2.b else 1 end", часть t2.c = null очевидно никогда не может быть true, по этому эта ветка была выкинута на этапе упрощения, осталось только проверить равенство на единицу, а условие соединения теперь фактически исчезло - нет предиката равенства, по этому хэш джойн невозможен и мы получаем ошибку.

В третьем случае, параметр тоже нулл, но я убрал ограничение на хэш и в плане вы увидите соединение вложенными циклами. Попробуйте найти в плане, в Nested Loops условие по которому соединяются таблицы (свойства Outer Refernce или Predicate), вы увидите что их нет, идет просто соединение таблиц вложенными циклами, предикат соединения был упрощен.

Конечно, у ТС там два предиката и формально оптимизатор мог бы что-то сделать, но тут наверное уже работает "консерватизм" оптимизатора, там где потенциально возможно получить некорректные результаты оптимизатор без нужды не упрощает.

Обычно такие манипуляции с условиями в соединения говорят о не очень удачно схеме данных, но я не 1С-ник, так что на этом умолкаю =)
21 дек 16, 17:29    [20033197]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow
Я объяснил выше почему, потому что оптимизатор не знает какое будет значение параметра во время выполнения и должен гарантировать "универсальный план" под все возможные значения
Спасибо.
Возможно я не понимаю элементарщину...
Если взглянуть на план первого запроса из Вашего примера там будет Compute Scalar по формуле [Expr1008] = Scalar Operator(CASE WHEN [tempdb].[dbo].[t2].[c]=(1) THEN [tempdb].[dbo].[t2].[b] ELSE (1) END) и по результату будет Hash build.
Почему нельзя в формуле сразу написать [Expr1008] = Scalar Operator(CASE WHEN [tempdb].[dbo].[t2].[c]=(@p) THEN [tempdb].[dbo].[t2].[b] ELSE (1) END)? Почему тогда план станет не универсальным?

Возможно спросил глупость...
21 дек 16, 18:49    [20033529]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Любопытный гость,

Нет, вопрос не глупый, как я писал выше, потому, что сервер не может построить корректное условие по эквисоединению (для Build или Probe), а вы видимо спрашиваете почему именно не может.
Пусть вам ответит сам разработчик оптимизатора Крейг Фридман, лучше чем у меня не получится =)
https://blogs.msdn.microsoft.com/craigfr/2009/04/28/implied-predicates-and-query-hints/

По данной проблеме далее в комментариях:
hackace April 6, 2012 at 8:11 pm
Hi craige,

can you help me ?
there are two TSQL sentences :sentence 1 and sentence 2
The sentence 1 can be excuted successfully ,but there will be throw an exception when I excuted the sentence 2,why?
--sentence 1
use [AdventureWorks]
go
DECLARE @c VARCHAR(10)
SET @c = ''
SELECT CustomerID
FROM dbo.Customers AS C
WHERE CustomerID IN (SELECT  @c + CustomerID FROM dbo.Orders)
OPTION (HASH JOIN, RECOMPILE)


--sentence 2
use [AdventureWorks]
go
DECLARE @c VARCHAR(10)
SET @c = ''
SELECT CustomerID
FROM dbo.Customers AS C
WHERE CustomerID IN (SELECT  @c + CustomerID FROM dbo.Orders)
OPTION (HASH JOIN)

Craig Freedman April 10, 2012 at 9:53 am
Hi,

The first query succeeds because as noted in the above post, with the recompile hint, the optimizer substitutes the empty string constant for the variable @c. You can see the substitution by checking the compute scalar in the plan:

 |Hash Match(Left Semi Join, HASH:([C].[CustomerID])=([Expr1006]), RESIDUAL:([Northwind].[dbo].[Customers].[CustomerID] as [C].[CustomerID]=[Expr1006]))
      |Index Scan(OBJECT:([Northwind].[dbo].[Customers].[Region] AS [C]))
      |Compute Scalar(DEFINE:([Expr1006]=N''+[Northwind].[dbo].[Orders].[CustomerID]))
           |Index Scan(OBJECT:([Northwind].[dbo].[Orders].[CustomersOrders]))


Without the recomile hint, the optimizer cannot do the substitution. I believe it then incorrectly concludes that @c is not a runtime constant and further concludes that it cannot safely use the hash join since it would need to reevaluate the orders subquery for each row from the customers table. I'm afraid that I'm not aware of any workaround other than to remove hash join hint.

Craig

Это я и имел ввиду, когда писал выше:
SomewhereSomehow
Конечно, у ТС там два предиката и формально оптимизатор мог бы что-то сделать, но тут наверное уже работает "консерватизм" оптимизатора, там где потенциально возможно получить некорректные результаты оптимизатор без нужды не упрощает.

Проще говоря - не может и все. Заведите item на microsoft connect, скажите что требуете доработать алгоритм и почему, вам ответят с большими подробностями почему это так или наоборот, что доработают (в чем я сомневаюсь).
21 дек 16, 19:32    [20033716]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,
опечатка: "лучше чем у него у меня не получится"
21 дек 16, 19:33    [20033719]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Любопытный гость
Guest
SomewhereSomehow,

Спасибо. Теперь понятно.
21 дек 16, 21:33    [20034099]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

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

Следует ли из вышесказанного, что выгоднее использовать объединение с табличной переменной или временной таблице вместо использования констант, если есть сомнения?
22 дек 16, 10:53    [20035501]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 6711
Переменных, конечно же, а не констант. Если оптимизатор полагает, что там может быть что-то еще, а не скаляр (что странно, на мой взгляд, пусть даже и NULL).
22 дек 16, 10:55    [20035509]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
SomewhereSomehow, большое спасибо!
Абсолютно исчерпывающе и понятно.
Самое обидное, я даже вроде читал об этом, и слушал лекцию (АФАИК Зайцева Дмитрия, если не путаю). А увидев в жизни - утупился.

Еще раз спасибо!
23 дек 16, 15:34    [20042168]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
AlxOlm
Member

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

Эээ!?
А что спасибо? Как проблема то решилась?
10 сен 19, 17:23    [21967987]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
МуМу
Member

Откуда:
Сообщений: 1073
Ну для 1С-а и подобных систем мы например сделали продукт который может добавлять опции типа (recompile) и т.п.
А что касается вашего случая то вы должны обратить внимание при Hash join на оперативную память которую потребляет запрос. Сталкивался с ситуацией когда подобных запросов было очень много в единицу времени и сервер принимал решение переходить в долгие вложенные циклы. А была ситуация совокупностью факторов, одним из которых было объединение по ключу с индексированной строкой (255) в которой максимальные значения были до 10-и символов. По сути решением проблемы стало сокращение размерности поля.
16 сен 19, 13:41    [21971567]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32146
Блог
AlxOlm,

можно просто план закрепить
16 сен 19, 13:45    [21971573]     Ответить | Цитировать Сообщить модератору
 Re: Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?  [new]
uaggster
Member

Откуда:
Сообщений: 692
AlxOlm, никак не решилось.
Взял распечатку топика, пошел к лидеру разработчиков и сказал - "идете на... юг" (прошу прощения за географические подробности).
Я не разработчик, я dba, мне - простительно.
16 сен 19, 14:14    [21971626]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Microsoft SQL Server Ответить