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

Откуда:
Сообщений: 686
Есть запрос (прошу прощения за синтаксис, это выдрано из 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

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

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

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

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

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

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

Откуда: Kiev
Сообщений: 6498
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
Сообщений: 6498
Мистер Хенки
чего то у вас поиска по _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
Сообщений: 6498
Мистер Хенки
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
Сообщений: 6498
Мистер Хенки,

чукча не читатель
оригинал запроса
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
Сообщений: 6498
Мистер Хенки
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
Сообщений: 6498
Мистер Хенки,

вы принципиально не видите переменную в запросе????
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

Откуда:
Сообщений: 686
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

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

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

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

Откуда: Москва
Сообщений: 8739
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

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

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

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

автор
Причем оптимизатор по-прежнему ошибается.
все любят оскорблять оптимизатор, а он для вас старается и выбирает самый дешёвый по ресурсам план
21 дек 16, 10:33    [20030659]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить