alexeyvgartiiНу и в конце поста я хотел бы акцентировать внимание на вашем вопросе, скорее всего компиляция планов не является узким местом т.к. после очистки кэша все летит и запросы компилируются со свистом, но проблема все-равно где-то в процедурном кэше возможно в поиске готовых планов.Это взаимосвязанные вещи. Сама по себе компиляция такого простого запроса не создаёт нагрузки, но мллионы запросов - это миллионы хранящихся в кеше планов, и естественно серверу становится плохо. Поэтому и нужно избавляться от лишних компиляций и лишних планов.

Все правильно я и пытаюсь заставить SQL Server создавать параметризованые планы, в которых будут float-ы вместо различных комбинаций numeric и тем самым добиться повторного использования планов.

alexeyvgartiiЗначения в запросах не одинаковые, но это как бы не должно играть ни какой роли, если запрос имеет параметризованный план.Нут хорошо, дропустим, у вас есть 2 закешированных плана:
(@1 int,@2 numeric(12,6))INSERT INTO [A]([a],[b]) values(@1,@2)
(@1 int,@2 numeric(7,2))INSERT INTO [A]([a],[b]) values(@1,@2)

Но ведь всего 2, а не 2 миллиона? Всё таки если параметризация происходит, она даже при неких неоптимальностях не приведёт к тому, что на каждый стейтмент будет отдельный план.

Это при включённой Forced параметризации.

Чем забивается кеш-то?

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

Планы точно параметризуются

вот пример 10 планов и 15 тыс, у 80% из которых useCount 1 или 2 эти планы и забивают весь кеш

(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(3,1),@8 numeric(2,1),@9 int,@10 int,@11 int,@12 numeric(6,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(3,2),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(1,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(2,1),@9 int,@10 int,@11 int,@12 numeric(6,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(3,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(1,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(3,1),@8 numeric(2,1),@9 int,@10 int,@11 int,@12 numeric(6,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(5,2),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(1,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(6,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(4,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(17,12),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [PositionHist]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(3,1),@8 numeric(2,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(4,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(4,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(1,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(6,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(2,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(17,12),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(6,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(2,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(6,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(2,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(2,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(4,2),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(1,1),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(6,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(3,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(17,12),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)
(@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 numeric(6,1),@8 numeric(6,1),@9 int,@10 int,@11 int,@12 numeric(7,1),@13 numeric(1,1),@14 numeric(1,1),@15 numeric(1,1),@16 numeric(1,1),@17 numeric(1,1),@18 numeric(3,1),@19 numeric(1,1),@20 numeric(1,1),@21 numeric(1,1),@22 numeric(1,1),@23 numeric(1,1),@24 numeric(17,13),@25 numeric(1,1),@26 numeric(1,1))INSERT INTO [Tab]([created_date],[created_time],[modified_date],[modified_time],[user_created],[user_modified],[Account_ID],[Asset_ID],[Date],[id],[OldDate],[Subacc_ID],[VolBlocked],[VolComBrok],[VolCredit],[VolForward],[VolForwardOut],[VolFree],[VolFreeStart],[VolGO],[VolInOut],[VolRepoIn],[VolRepoOut],[Volume],[VolumeAccrued],[VolVar]) values(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17,@18,@19,@20,@21,@22,@23,@24,@25,@26)

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


Все правильно я и пытаюсь заставить SQL Server создавать параметризованые планы, в которых будут float-ы вместо различных комбинаций numeric и тем самым добиться повторного использования планов.Вы пытаетесь шаманить. Параметризация по текстам в сиквеле - это шаманство, которое делалось как затычка для "вытягивания" старых унаследованных приложений, или просто приложений с плохим дизайном.

Скажем, в команде INSERT Table VALUES ('A', 12345) что является параметрами, а что - частью запроса, которую нужно учитывать при построении плана: 'A', 12345, оба из них, или ни один из них? Это не такой простой вопрос, как кажется, и правильный ответ на него может сильно влиять на производительность.

В некоторых случаях у сиквела это получается, но не понимаю, зачем такие мучения? Не проще просто использовать параметризированные запросы, а не слать сиквелу тексты, надеясь, что он сам из текстов сделает параметризированные запросы?

Я с вами не согласен, что это шаманство. В некоторых случаях грамотная параметризация приводит к сказочной производительности.
В моем случае я хочу добиться снижения издержек при работе с операционым кешем, которые влияют на производительность в худшую сторону.

alexeyvgartiiЯ с вами не согласен, что это шаманство. В некоторых случаях грамотная параметризация приводит к сказочной производительности.Ээээ, я не понял. Разве повышение производительности исключает шаманство???
artiiПланы точно параметризуются

вот пример 10 планов и 15 тыс, у 80% из которых useCount 1 или 2 эти планы и забивают весь кешНу вот видите, как они параметризируются?

Я предлагаю параметризировать явно, соответствующими конструкциями клиента. Вот это правильный подходж, а не надежда "параметризации" произвольных текстов.

Оставим разговор про шаманство.

Как вы предлагаете параметризировать запрос, использовать конструкцию sp_executesql?
Она мне не подходит т.к. есть ситуации, когда я отправляю не под транзакцией пачку запросов за раз и ожидаю, что в случае возникновения ошибки в одном из запросов остальные вставятся и не приведет к откату остальных запросов.

Crimeanartii,

вам предлагают с клиента параметризованные запросы формировать
внезапно, да? впрочем, этой возможности 100 лет в обед будет

А, я понял о чем речь, но если честно такой подход ни чем, на мой взгляд, не лучше вызова sp_sqlexecute.
Там для пачки запросов нужно будет готовить соответствующий заголовок, количество запросов в заголовке будет зависеть от размера пачки. И скорее всего при возникновении ошибки в команде откатятся все остальные строки, хотя это нужно проверять.

Хорошо, я понял про параметризацию запросов на стороне клиента и предлагаю эту тему больше не поднимать.
Хочется все-таки научиться управлять параметризацией на стороне сервера. Если у кого-то есть такой опыт, пожалуйста, напишите,
особенно интересует использование sp_create_plan_guide. Если нет то пусть тема тонет

Спасибо всем за обсуждение

Crimeanalexeyvg,

+1, 1 раз готовим запрос и описание параметров. а дальше тока параметры заполняем и говорим "execute" на каждую строку данных
p.s.
да, теперь понятно, почему ODBC закапывают.. а там целый пласт вокруг массовой обработки был..
вплоть до настройки отображения буфера данных на параметры..
но - всем же облом разбираться

Хорошо, если я делаю описание (в моей терминологии заголовок), для одной строки, потом я просто заполняю буфер для этого описания и делаю вызов. Один вызов одна строка в БД.

В нашем приложении такой подход использовался, но он оказался медленнее, чем если завернуть например 20 запросов в один текстовый батч. Особенно, когда много мелких запросов Add(update) и время их выполнения сравнимо в временем отклика от сервера.

Теоретически можно подготовить заголовки, в которых будут параметризация от 1 до N запросов,
Далее, если поступает например 5 запросов, выбирать заголовок, в котором 5 insert-ов, заполнять соотв буфер и делать вставку, если запрос выполняется то вставляется сразу 5 строк. Далее не понятно, что будет с остальными 4-мя строчками, если одна из пяти содержит ошибку откатятся они или останутся в БД. Не хочется с этим связываться, если можно распинать sp_create_plan_guide

ShakillCrimeanда, теперь понятно, почему ODBC закапывают.. а там целый пласт вокруг массовой обработки был..
вплоть до настройки отображения буфера данных на параметры..
но - всем же облом разбиратьсянаоборот же, OLE DB закапывают

+1, как раз Microsoft отказывается от использования OLEDB в своих внутренних разработках в пользу ODBC

kalimbaРаз пошла такая пьянка) У меня такой запрос часто приходит на сервер от стороннего PHP приложения:


Я создаю plan guide:

\
Но приходящие запросы все равно не параметризуются. Что сделал не так?

так а какой план-то получается в итоге?

alexeyvgartiiА, я понял о чем речь, но если честно такой подход ни чем, на мой взгляд, не лучше вызова sp_sqlexecute.
Там для пачки запросов нужно будет готовить соответствующий заголовок, количество запросов в заголовке будет зависеть от размера пачки. И скорее всего при возникновении ошибки в команде откатятся все остальные строки, хотя это нужно проверять.

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

Откуда там какие то "пачки", что такое "заголовок"? Ничего похожего при параметризации не используется.

Параметризация - это: http://msdn.microsoft.com/ru-ru/library/vstudio/yy6y35y8(v=vs.100).aspx

Там внизу и примеры есть. Как видите, никаких пачек и заголовков там не упоминается.

Впрочем, если для вас принципиально не говорить про параметризацию, то больше не буду.

Нет, я не то чтобы против того, чтобы говорить про параметризацию вообще. Я просто говорю, что в моем случае подсказывать SQL Server какие типы у констант должны быть в плане запросе, на клиентской стороне довольно с трудоемкая задача, которая еще может не выстрелить по быстродействию. Поэтому мой посыл был в том, чтобы сосредоточиться на поиске решения на стороне самого SQL Server, например используя sp_create_plan_guide

2 kalimba

А если попробовать так?

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT own_id FROM warehouse..catalog_good WHERE model_id = ''27985529'' AND deleted = 0 AND on_sell = 1 AND own_id IS NOT NULL AND own_id != '''''
,@stmt OUTPUT
,@params OUTPUT

EXEC sp_create_plan_guide N'TemplateGuide1'
,@stmt
,N'TEMPLATE'
,NULL
,@params
,N'OPTION (PARAMETERIZATION FORCED)';

EXEC sp_create_plan_guide N'SQLGuide1'
,@stmt
,N'SQL'
,NULL
,@params
,N'OPTION (RECOMPILE)';