ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
25 сообщений из 47, страница 1 из 2
  1  все
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371270
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Есть запрос (прошу прощения за синтаксис, это выдрано из 1C, и сгенерировано тоже автоматически 1Сом):

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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), третий план на той же картинке:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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: )

Статистика по таблицам - свежая, с полным сканированием, индексы перестроены.
Даже ребилд таблиц сделан!
...
Рейтинг: 0 / 0
20.12.2016, 18:01
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371271
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Вот планы
...
Рейтинг: 0 / 0
20.12.2016, 18:02
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371278
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
uaggster,

потому, что для разного значения параметров может выбираться разный план. Сервер запомнил несколько планов и не всегда попадает в оптимальный.
RECOMPILE вызывает очистку кэша планов выполнения для данного запроса. То же самое, например, может происходить после переиндексации таблиц, входящих в запрос.
Можете попробовать использовать руководство планов с параметризацией для фиксации хорошего плана. Но это не универсальная таблетка, т.к. подходит для запросов, которые сохраняют написание.
...
Рейтинг: 0 / 0
20.12.2016, 18:11
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371280
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Иногда помогает пересчёт статистик. Если не копаться в планах, можно попробовать.
...
Рейтинг: 0 / 0
20.12.2016, 18:13
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371281
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
Пересчет статистик тоже может привести к удалению плана из кэша.
...
Рейтинг: 0 / 0
20.12.2016, 18:15
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371288
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
uaggster,

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

авторОн имеет радикально разный априорный и фактический планы,
как только не переводят estimate :)
...
Рейтинг: 0 / 0
20.12.2016, 18:23
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371292
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
чего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане
...
Рейтинг: 0 / 0
20.12.2016, 18:25
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371295
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Мистер Хенкичего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане
а в хорошем?
...
Рейтинг: 0 / 0
20.12.2016, 18:28
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371296
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
CASE
WHEN T2._Fld2977_TYPE = 0x08
AND T2._Fld2977_RTRef = 0x0000006F
THEN T2._Fld2977_RRRef
ELSE 0xFF
END AS Q_001_F_000RRef
вот это сделать как хранимое вычисляемое и индекс на него и _Fld2976 может и взлетит
...
Рейтинг: 0 / 0
20.12.2016, 18:29
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371297
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
TaPaKМистер Хенкичего то у вас поиска по _AccumRg2973 нет . Сканы в "плохом" плане
а в хорошем?
а "хороший" не очень то хорош. Жрет проц как в последний раз
...
Рейтинг: 0 / 0
20.12.2016, 18:32
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371299
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
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 а это не проц и тд и тп
...
Рейтинг: 0 / 0
20.12.2016, 18:33
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371301
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
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
...
Рейтинг: 0 / 0
20.12.2016, 18:37
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371304
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Мистер Хенки,

чукча не читатель
оригинал запроса
Код: sql
1.
2.
3.
4.
5.
6.
CASE 
	WHEN T2._Fld2977_TYPE = 0x08
	AND T2._Fld2977_RTRef = @P3
	THEN T2._Fld2977_RRRef
	ELSE 0xFF
END AS Q_001_F_000RRef
...
Рейтинг: 0 / 0
20.12.2016, 18:39
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371305
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
TaPaKпарралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп
соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.
...
Рейтинг: 0 / 0
20.12.2016, 18:41
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371310
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Мистер ХенкиTaPaKпарралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп
соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.
ну раз всё в памяти уместилось, то не так и много, но как вы предлагаете менять запрос в не меняемом приложении?
...
Рейтинг: 0 / 0
20.12.2016, 18:50
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371312
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
TaPaKМистер Хенкипропущено...

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие. Но если запросов мало на сервере и никому мешать не будет, то нормально.
ну раз всё в памяти уместилось, то не так и много, но как вы предлагаете менять запрос в не меняемом приложении?
ну по идее индекс должен подхватиться запросом, а для этого приложение менять не надо. Вот поле вычисляемое придется строить, не знаю позволяет ли такие вольности 1с. Я же предлагаю, а не настаиваю.
...
Рейтинг: 0 / 0
20.12.2016, 18:53
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371314
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Мистер Хенки,

вы принципиально не видите переменную в запросе????
...
Рейтинг: 0 / 0
20.12.2016, 18:56
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371315
o-o
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
o-o
Гость
Мистер ХенкиTaPaKпарралельный план и там и там, разница больше в NL vs HASH а это не проц и тд и тп
соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие . Но если запросов мало на сервере и никому мешать не будет, то нормально.
...и т.к. объемы немаленькие, то в самый раз NL!!!
сверху поди строк под миллион и снизу тоже, в аккурат такое считать через NL,
там логические чтения зашкаливают.
uaggster, выньте ему статистику планов и покажите, cколько там чтений в каждом случае, пусть ужаснется
----
в первом плане server считает, что в верхней таблице по условию пара строк отберется , и в нижней тоже,
сканы оценены в 0.
в следующих двух он уже видит, что реально каждый скан будет выдавать тучу строк, потому и выбрал хэш джойн.
какое счастье, что решает все же программа, а не Мистер Хенки,
последнего надо посадить вручную соединять те две таблицы, ага, в цикле
...
Рейтинг: 0 / 0
20.12.2016, 18:59
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371321
Мистер Хенки
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мистер Хенки
Участник
TaPaKМистер Хенки,

вы принципиально не видите переменную в запросе????
а это
Код: sql
1.
AND T2._Fld2977_RTRef = @P3

- не заметил . Тогда конечно не получится вычисляемое поле построить. Печально конечно.
...
Рейтинг: 0 / 0
20.12.2016, 19:10
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371327
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
o-oМистер Хенкипропущено...

соединение хэшированием требует немало проца. А там по всему и обьемы немаленькие . Но если запросов мало на сервере и никому мешать не будет, то нормально.
...и т.к. объемы немаленькие, то в самый раз NL!!!
сверху поди строк под миллион и снизу тоже, в аккурат такое считать через NL,
там логические чтения зашкаливают.
uaggster, выньте ему статистику планов и покажите, cколько там чтений в каждом случае, пусть ужаснется
----
в первом плане server считает, что в верхней таблице по условию пара строк отберется , и в нижней тоже,
сканы оценены в 0.
в следующих двух он уже видит, что реально каждый скан будет выдавать тучу строк, потому и выбрал хэш джойн.
какое счастье, что решает все же программа, а не Мистер Хенки,
последнего надо посадить вручную соединять те две таблицы, ага, в цикле
В эстимейтед они считает, что скан Document111 обойдется в 600 000 строк, а Accum - 4,5 миллиона.
В актуальном - 10 000 Х 8,5 миллионов
...
Рейтинг: 0 / 0
20.12.2016, 19:28
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371366
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Тут основная проблема в том, что это не человек писал. Это генератор 1C накодил.
И перестроить запрос - не получится.
Кстати, если построить подсказываемый индекс - то это никак делу не помогает.

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

Нет никакого трейс-флага, который заставлял бы сервер принудительно рекомпилить все запросы?
Понятно, что это не очень хорошо, но как временное решение, бока идет препирательство с разработчиками - сгодилось бы!
...
Рейтинг: 0 / 0
20.12.2016, 20:55
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371376
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
uaggsterНет никакого трейс-флага, который заставлял бы сервер принудительно рекомпилить все запросы?Нету.
Plan guide вам поможет.
...
Рейтинг: 0 / 0
20.12.2016, 21:17
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371585
Basma4
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Basma4
Участник
если база в уровне соместимости 120, попробуй включить старую оценку кардинальности с помощью TF 9481 и протестировать запрос с ним.
...
Рейтинг: 0 / 0
21.12.2016, 09:43
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371643
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Basma4, да, в 120
Но трейсфлаг не помог. Оценка кардинальности осталась прежней.
Причем оптимизатор по-прежнему ошибается.
...
Рейтинг: 0 / 0
21.12.2016, 10:31
| Ответить | Цитировать | Написать  
Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
    #39371646
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
uaggsterBasma4, да, в 120
Оценка кардинальности осталась прежней.

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

авторПричем оптимизатор по-прежнему ошибается. все любят оскорблять оптимизатор, а он для вас старается и выбирает самый дешёвый по ресурсам план
...
Рейтинг: 0 / 0
21.12.2016, 10:33
| Ответить | Цитировать | Написать  
25 сообщений из 47, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Почему так сильно различаются планы запроса с Option(recompile) и без? И как это побороть?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&msg=39371312&tid=1685943]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 66ms
get tp. blocked users:
 2ms
others: 15ms
total:  174ms
созд. / загр.: 174ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]