ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поясните про ограничение сервера в 160036 планов запросов?
25 сообщений из 30, страница 1 из 2
  1  все
Поясните про ограничение сервера в 160036 планов запросов?
    #40079392
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Всем привет. Столкнулся с данным ограничением на одном из своих серверов, вот каков результат следующих запросов:
Код: sql
1.
2.
3.
4.
5.
6.
7.
select name, type, buckets_count 
from sys.dm_os_memory_cache_hash_tables
where name IN ( 'SQL Plans' , 'Object Plans' , 'Bound Trees' )
 
select name, type, pages_kb, entries_count 
from sys.dm_os_memory_cache_counters
where name IN ( 'SQL Plans' , 'Object Plans' ,  'Bound Trees' )



Вероятно для запроса подобного типа:
авторSELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 10:00:27',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](2,'22.06.2021 12:00:26',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](6,'22.06.2021 13:00:36',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](5,'21.06.2021 18:00:09',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](3,'22.06.2021 13:00:27',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](1,'22.06.2021 13:00:34',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](1,'22.06.2021 16:00:09',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 13:30:11',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](6,'22.06.2021 13:00:41',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](3,'22.06.2021 13:00:28',15)

каждый раз создается новый план.

Можно ли как то оптимизировать со стороны сервера эту особенность?

Версия сервера
Microsoft SQL Server 2019 (RTM-CU4) (KB4548597) - 15.0.4033.1 (X64) Mar 14 2020 16:10:35 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Размер оперативки 128гб.
...
Рейтинг: 0 / 0
22.06.2021, 16:19
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079397
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
Эээ... Форсированную параметризацию включить?
...
Рейтинг: 0 / 0
22.06.2021, 16:26
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079400
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
uaggster
Эээ... Форсированную параметризацию включить?


Так просто, и больше даже ничего анализировать не нужно?
...
Рейтинг: 0 / 0
22.06.2021, 16:37
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079405
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/optimize-for-ad-hoc-workloads-server-configuration-option?view=sql-server-ver15
...
Рейтинг: 0 / 0
22.06.2021, 17:29
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079417
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
teCa,

сравните запросы, чем они отличаются.
...
Рейтинг: 0 / 0
22.06.2021, 18:09
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079499
uaggster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
uaggster
Гость
teCa
uaggster
Эээ... Форсированную параметризацию включить?


Так просто, и больше даже ничего анализировать не нужно?

Разумеется нужно :-)
Поведение системы после, например :-)))

Но количество планов в кэше радикально уменьшится.
...
Рейтинг: 0 / 0
23.06.2021, 08:27
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079505
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Не совсем понял из мануалов, как работает параметризация, планов в кэше действительно стало меньше, может просто нужно больше времени, что-бы кэш заполнился.

Включил forced parameterization на самую активную бд, сбросил кэш на сервере, но судя по содержимому кэша, результат не тот, какого я ждал, при вожу пример того же запроса:
...
Рейтинг: 0 / 0
23.06.2021, 09:36
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079506
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Я ожидал, что для всего пула этого запроса с разными параметрами сформируется один план.
...
Рейтинг: 0 / 0
23.06.2021, 09:38
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079537
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
teCa,

Вернулись в исходное положение:
...
Рейтинг: 0 / 0
23.06.2021, 11:33
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079543
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
teCa
авторSELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 10:00:27',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](2,'22.06.2021 12:00:26',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](6,'22.06.2021 13:00:36',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](5,'21.06.2021 18:00:09',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](3,'22.06.2021 13:00:27',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](1,'22.06.2021 13:00:34',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](1,'22.06.2021 16:00:09',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 13:30:11',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](6,'22.06.2021 13:00:41',15)
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](3,'22.06.2021 13:00:28',15)
Такое сервером не параметризуется.
...
Рейтинг: 0 / 0
23.06.2021, 11:55
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079545
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
teCa,

принудительная параметризация поможет, если запросы отличаются только знfчениями констант в выражении where. Поэтому надо определить - чем отличаются запросы.

Например, select a from b where c = 1 (1 план) select a from b where c = 2 (2 план) select a from b where c = 3 (3 план) можно привести к одному сохранённому плану.

Если имеется в виду выполнение скалярной функции вида SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 10:00:27',15), то привести это выражение к единому плану нельзя. Параметры необходимо задать переменными.
...
Рейтинг: 0 / 0
23.06.2021, 11:58
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079548
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
invm
teCa
пропущено...
Такое сервером не параметризуется.


А есть способы увеличить этот придел? Как я понимаю, он пропорционален размеру памяти?
...
Рейтинг: 0 / 0
23.06.2021, 12:07
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079553
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
teCa,

у вас ситуация, когда нет ограничений на рост, как я понимаю, то есть разнообразие запросов бесконечно. Можно попробовать включить оптимизацию для AD-HOC.
...
Рейтинг: 0 / 0
23.06.2021, 12:18
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079555
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
teCa,

у вас при включенной серверной опции optimize for ad-hoc workloads
каждый первый запуск такой инструкции будет создавать plan-stub, но если на сервер прилетит запрос с таким же текстом, то plan-stub уже сформируется в полноценный CachedPlan

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

к приеру запустите в ssms отдельно два запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
set statistics xml on;
go
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](4,'22.06.2021 10:00:27',15)
go
SELECT [dbo].[fn_GetClosingGladeByGoodsGroup](2,'22.06.2021 12:00:26',15)
go
set statistics xml off;



и посмотрите на query_plan_hash, он будет одинаковым для обоих запросов.
если по этому query_plan_hash вы полезите в представление sys.dm_exec_query_stats то в случае parameterization simple вы увидите две записи где plan_handle будет соотвествовать отдельному CompliedPlan, а в случае forced parameterization ссылка будет на план использующий параметры

но это не отменяет самого факта что на каждый ad-hoc запрос у вас будет сформирован план, его соджержимое будет единичный оператор select в таком случае
...
Рейтинг: 0 / 0
23.06.2021, 12:22
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079556
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Владислав Колосов,

В том то и дело, судя по тому, что в функцию передается параметр время "22.06.2021 10:00:27", то и для каждого запроса с отличным временем от предыдущего будет создаваться новый план. Видимо в перспективе, любой лимит будет так же заполнен. Было бы логично предположить, что в таком случае, план используется только 1 раз и хранить мне его вообще нет смысла?
...
Рейтинг: 0 / 0
23.06.2021, 12:23
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079558
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
teCa,

для каждого набора параметров сервер формирует отдельный план. Да, хранить нет смысла, попробуйте поработать с включенной AD-hoc оптимизацией. Но это может оказать влияние на прочие запросы.
...
Рейтинг: 0 / 0
23.06.2021, 12:27
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079567
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
Собрал побольше информации о этом типе запросов.
Для всех планов этого запроса дата_создания = дата_последнего_выполнения = дата_компиляции, те, план действительно применяется 1 раз, а дальше остается висеть баластом.

Могу ли я вручную удалять эти планы?
...
Рейтинг: 0 / 0
23.06.2021, 12:57
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079575
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
teCa,

можете, получите их plan_handle

и вызывайте dbcc freeproccache (<plan_handle>);
...
Рейтинг: 0 / 0
23.06.2021, 13:29
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079600
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
felix_ff
teCa,

можете, получите их plan_handle

и вызывайте dbcc freeproccache (<plan_handle>);


Написал вот такой запрос, который формирует список команд для удаления плана:

Код: sql
1.
2.
3.
DECLARE @drop_plan varchar(max)
select 'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [Plan_Handle], 1) + ');'
FROM sys.dm_exec_query_stats where query_hash in (0xD381521787B5B500)



Подскажите, есть ли возможность сохранить результат в текст и выполнить одним разом, без использования курсоров. Полагаю, что курсором выйдет крайне медленно.
...
Рейтинг: 0 / 0
23.06.2021, 14:58
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079608
felix_ff
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
felix_ff
Участник
teCa,

формируйте строку скрипта единым пакетом, что бы у вас получалась команда
Код: sql
1.
2.
3.
dbcc freeproccache (0x00001...);
dbcc freeproccache (0x00002...);
dbcc freeproccache (0x00003...);



примеров как получить агрегацию строки полно.
STRING_AGG, for xml path, select @var+=, GroupConcat(CLR)
...
Рейтинг: 0 / 0
23.06.2021, 15:17
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079613
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
felix_ff,

Спасибо, все получилось.
...
Рейтинг: 0 / 0
23.06.2021, 15:30
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079626
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
teCa,

Опять немного запутался.

Хочу понять, как можно выявить "одноразовые планы", как я понимаю, отправная точка в поисках это sys.dm_exec_query_stats
вот таким запросом, я получаю запросы, для которых скомпилировано боле чем один план:

Код: sql
1.
2.
3.
select sql_handle,count(plan_handle) as [Plan_count] from sys.dm_exec_query_stats
GROUP BY sql_handle
order by 2 desc



Это будет правильно?
...
Рейтинг: 0 / 0
23.06.2021, 16:06
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079633
Фотография komrad
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
komrad
Участник
teCa


Это будет правильно?

нет, смотрите sys.dm_exec_cached_plans (usecounts, refcounts)
...
Рейтинг: 0 / 0
23.06.2021, 16:31
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079634
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
teCa
Это будет правильно?
Нет.

В sys.dm_exec_query_stats есть столбец execution_count.
...
Рейтинг: 0 / 0
23.06.2021, 16:31
| Ответить | Цитировать | Написать  
Поясните про ограничение сервера в 160036 планов запросов?
    #40079641
teCa
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
teCa
Участник
komrad
teCa


Это будет правильно?

нет, смотрите sys.dm_exec_cached_plans (usecounts, refcounts)


Получается, что кандидаты на удаление планы usecounts=1?

Код: sql
1.
2.
3.
4.
5.
declare @var varchar(max)
select @var=STRING_AGG (CONVERT(VARCHAR(max),'DBCC FREEPROCCACHE (' + CONVERT(VARCHAR(128), [Plan_Handle], 1) + ');'),CHAR(13))
FROM sys.dm_exec_cached_plans where usecounts=1

execute sp_sqlexec @var



конечно грубое решение, тк в этот список могут попадать валидные планы, которые редко используются, но из 160т вычистилось 120т.
...
Рейтинг: 0 / 0
23.06.2021, 16:54
| Ответить | Цитировать | Написать  
25 сообщений из 30, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Поясните про ограничение сервера в 160036 планов запросов?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=46&fpage=21&tid=1684558]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 44ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 63ms
get tp. blocked users:
 2ms
others: 263ms
total:  417ms
созд. / загр.: 417ms
x
x
Закрыть


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