Столкнулся со странным поведением механизма параметризации у SQL Server.
Проблема в том, что SQL Server создает разные планы для казалось бы одинаковых запросов на insert.

Пример такой

CREATE TABLE A (a int, b float) --создаем тестовую табличку
GO

DBCC FREEPROCCACHE -- чистим операционный кэш, чтобы проще искать результат
GO

-- вставляем строки
insert into A (a, b) values (1, 10000.01)
insert into A (a, b) values (1, 990000.019999)
GO

Смотрим, что у нас лежит в кэше
SELECT text, * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle)

а там два разных плана
(@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)

Как заставить SQL Server, чтобы он создавал один план в такой ситуации и использовал его повторно?

Версия сервера
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Это помогает, к сожалению, только для запросов select
Я проверял это все для insert такая же картина с двумя планами.

Knyazev Alexey


а так?

так лучше

declare @cmd nvarchar(1024) = 'insert into A (a, b) values ( @p1, @p2 )'
exec sp_executesql @cmd, N'@p1 int, @p2 float', @p1 = 1, @p2 = 10000.01
exec sp_executesql @cmd, N'@p1 int, @p2 float', @p1 = 1, @p2 = 990000.019999


(@p1 int, @p2 float)insert into A (a, b) values ( @p1, @p2 )

GlorykalimbaРеально не работает : (
declare @x decimal (16,7)
set @x = 10000.01
INSERT INTO A (a, b) values (1, @x)
set @x = 990000.019999
INSERT INTO A (a, b) values (1, @x)

Такой вариант, вообще не приводит к параметризации

к формированию не параметризованного плана для конкретного батча.

declare @x decimal (16,7) set @x = 10000.01 INSERT INTO A (a, b) values (1, @x) set @x = 990000.019999 INSERT INTO A (a, b) values (1, @x)

Knyazev Alexey


а так?

Отдельное спасибо за такой вариант, но он мне не совсем подходит, из-за низкой производительности такого подхода.

Я накопал такой вариант, как создание структуры плана для связывания запросов через sp_create_plan_guide.
Попробовал создать структуру плана, но в итоге опять создается два плана.

Пример:

EXEC sp_create_plan_guide
@name = N'update_A',
@stmt = N'insert into A (a, b) values ( @p1, @p2 )',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 int, @p2 float',
@hints = BULL
GO

DBCC FREEPROCCACHE
GO

insert into A (a, b) values (1, 10000.01)
insert into A (a, b) values (1, 990000.019999)
GO

на выходе опять
(@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)



посмотреть и удалить схемы можно так
SELECT * FROM sys.plan_guides
exec sp_control_plan_guide

Вопрос будет создание схемы аналогом вызова sp_executesql с параметрами?

У меня работа с БД при загрузке данных построена таким образом.
Запросы insert(update) склеиваются в один батч от одного до N шт. При низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO

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

Пока это написал я понял, что я не совсем прав, говоря, что этот подход с sp_executesql будет медленнее, чем описанный выше.
Я предполагал, что sp_executesql будет делаться для каждого insert или update по-отдельности, что конечно же не так и можно переделать приложение, подставляя в вызов sp_executesql 20 строк на вставку, но это как-то не красиво и не спортивно.

Хотелось бы выкрутить руки SQL Server, чтобы он таки нашел нужный план плану у себя к кэше и не создавал новый.

Crimeanp.s.

а вы боретесь со скоростью вставки? ну не планы ради планов же? тогда (1) хранимки и (2) оборачивать по ~ 100..1000..10000 вставок в транзакцию. но с транзакцией будет попадалово с ошибками, да.. и будет вам быстро. ну или примеряйте способы передавать не по 1 записи, а пачками сразу на сиквел - тоже будет быстрее

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

Но в процессе понимания, что происходило при тормозах на SQ Server единственное, что было не понятно это зачем SQL Server постоянно генерит новые планы для казалось бы одинаковых запросов, к тому же эти планы далеко не такие безобидные и потребляют довольно много памяти в моем случае это было около 1,3 Гб при этом часть из этих планов удаляется в процессе вставки 500 тыс строк.

ShakillartiiПри низкой нагрузке в БД могут ехать отдельные запросы при высокой запросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO а зачем так много букв, пишите сразу одной командой insert into (..) values (), (), (), ... и будет один план на пакет

Не знал про такой синтаксис, спасибо, но мне не подходит вариант, запрос откатывает все вставленные строки, если хотя бы одна вызывает ошибку например PK_violation

Гость333artiiзапросы объединяются в один пакет. т.е.
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
into (..) values ()
GO
Каким образом вы определили, что ваше узкое место — это компиляция запросов?

Знаете, я не могу достоверно говорить, как я это определил я просто скажу что произошло

совершенно случайно, я при проявлении этих тормозов, решил очистить процедурный кэш, ну так ради спортивного интереса,
DBCC FREEPROCCACHE
после этого все просто полетело, но через некоторое время все опять начало тормозить

порядок тормозов следующий, запросы при тормозах просасывались по 20 шт за 1 сек, после очистки по несколько тыс за сек

Далее начал смотреть, а что лежит в кэше и увидел , что практически весь процедурный кеш забит запросами к одной таблице, 80% от этих запросов имею UseCount 1 или 2. И этот факт вызвал большие вопросы т.к. запросы в целом одинаковые, т.е. insert содержит одинаковый набор полей в одинаковом порядке с одинаковыми отступами, пробелами и регистром буквенных символов отличаются только значениями полей полей типа float. Эти значения в планах приводятся к параметрам разных вариантов типа numeric(12,6), numeric(7,2) и т.д этих полей порядка 15 и получается, что с высокой вероятностью создается новый план.

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

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

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

Ну и вообще есть способы и в сотни раз увеличить скорость, просто вы говорите, что скорость вас не интересует, что вы выясняете конкретный вопрос поведения SQL Server...

Значения в запросах не одинаковые, но это как бы не должно играть ни какой роли, если запрос имеет параметризованный план.
Для этого собственно и нужна параметризация. Например для запросов Select в простом текстовом виде, если план параметризуется, добиться повторного использования параметризованного плана довольно просто, достаточно привести нужную константу к нужному типу и соотв параметр в плане примет указанный тип. С инсертами сложнее нужно использовать sp_exeutesql, возможно sp_create_plan_guide делает так же как и sp_exeutesql, но мне пока не удается его распинать применительно к инсертам.

Может кто-то уже использовал sp_create_plan_guide в таком же контексте как я и знает как создать правильную схему для планов?