Параметризация запросов. Ваш лучший друг? Часть 3/3.

Источник: sqlcmd

Параметризация ручная, с помощью sp_executesql.

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

1	ALTER DATABASE [~DB~] SET PARAMETERIZATION SIMPLE

Заодно проверьте, что индекс iT1 находится во включенном состоянии:

1	ALTER INDEX iT1 ON T1 REBUILD

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

Итак первый путь ручной настройки процесса параметризации - системная хранимая процедура sp_executesql. Ее главный функционал - позволить выполнять строку текста как инструкцию T-SQL - известен, конечно же, всем. Мы же сфокусируем наше внимание на побочной деятельности той же процедуры, а именно на ее способности явно выделять параметры содержащиеся в той самой строке-инструкции. Для удобства читателя напомню что мы понимаем под "запросом кэша":

1
2
3
4
5
6
7
8
9
10
11
12
13

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT cp.objtype,
cp.usecounts,
cp.size_in_bytes,
cp.plan_handle,
st.text,
parameterized_plan_handle=query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'NVARCHAR(128)'),
parameterized_text=query_plan.value('(//StmtSimple)[1]/@ParameterizedText', 'NVARCHAR(MAX)'),
qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan (cp.plan_handle) qp
WHERE st.text LIKE '%ColID%' AND st.text NOT LIKE '%sys.dm_exec_cached_plans%'

Это наш основной инструмент отвечающий на вопрос "как оно там все закэшировалось" и "имело ли место повторное использование уже существующего в кэше плана". Смысл и назначение каждой колонки возвращаемых данным запросом были подробно рассмотрены в первой части статьи.

Перейдем непосредственно к sp_executesql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

USE [~DB~]
go
DBCC FREEPROCCACHE
GO
DECLARE @query nvarchar(200)
DECLARE @param nvarchar(200)
SET @query = N'
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3=@my_param
'
SET @param = N'@my_param varchar(2)'
EXEC sp_executesql @query, @param, @my_param = 'ab'
EXEC sp_executesql @query, @param, @my_param = 'zz'
go
DECLARE @query nvarchar(200)
DECLARE @param nvarchar(200)
SET @query = N'
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)
'
SET @param = N'@my_param1 varchar(2), @my_param2 varchar(2)'
EXEC sp_executesql @query, @param, @my_param1 = 'ab', @my_param2 = 'ef'
EXEC sp_executesql @query, @param, @my_param1 = 'xx', @my_param2 = 'yy'
go

"Запрос кэша" рапортует нам что произошло вот что:

objtype usecounts text parameterized_plan_handle
Prepared 2 (@my_param1 varchar(2), @my_param2 varchar(2)) SELECT... NULL
Prepared 2 (@my_param varchar(2)) SELECT...WHERE Col3=@my_param NULL

Не вызывает сомнений, что параметризация произошла и что вторые вызовы процедур sp_executesql в каждом пакете не генерировали новый план, а пользовались планом существующим. Так же несомненно, что находясь даже в простом режиме параметризации оптимизатор отбросил свои "предрассудки" в вопросах возможности/невозможности параметризации того или иного запроса, и полностью положился на наши указания. Если мы считаем, что в WHERE/IN параметры my_param1/my_param2 являются именно ими, параметрами - так тому и быть, оптимизатор послушно исполняет наше указание. Так же приятно, что в вопросах типов параметров оптимизатор больше не играет в угадайку, а снова полностью полагается на программиста. Однако, складывается впечатление, что в отличии от авто-параметризации оптимизатор больше не прибегает к услугам shell-планов, а просто создает единый полноценный план? Так оно и есть, а вот и объяснение этому факту. Какая главная (да и по сути единственная) задача у shell-плана? Задача такая: облегчить поиск "почти совпадающих" запросов и их сопоставление с имеющимся в кэше планом. Помните наш эксперимент в первой части с двумя SELECT-ами набранных в различных регистрах? Там, благодаря shell-планам (и нормализации текста запроса в "главном" плане, разумеется), оптимизатор корректно соотнес оба запроса, установил что они, на самом деле, являются копиями друг друга, и успешно обошел потенциальную проблему двойной генерации одного и того же плана. Попробуем тоже самое с обсуждаемой хранимой процедурой:

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE @query1 nvarchar(200)
DECLARE @query2 nvarchar(200)
DECLARE @param nvarchar(200)
SET @query1 = N'
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)
'
SET @query2 = N'
select ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)
'
SET @param = N'@my_param1 varchar(2), @my_param2 varchar(2)'
EXEC sp_executesql @query1, @param, @my_param1 = 'ab', @my_param2 = 'ef'
EXEC sp_executesql @query2, @param, @my_param1 = 'ab', @my_param2 = 'ef'
go

"Запрос кэша" неумолим:

objtype usecounts text
Prepared 1 (@my_param1 varchar(2), @my_param2 varchar(2)) select...
Prepared 1 (@my_param1 varchar(2), @my_param2 varchar(2)) SELECT...

Увы, но это будут два разных плана, никакой нормализации текста запроса не случается. Однако подождите кричать "халтура" и "за что деньги берут" - своя логика есть и у этого подхода. Во-первых, shell-план до некоторой степени штука полезная, однако отнюдь не бесплатная. Он сидит себе в RAM требуя вполне конкретного места, и не пару байт, и даже не пару килобайт. Строго говоря, минимальнейший размер shell-плана для запроса из одного символа (если вы еще такой запрос придумаете) будет 16Kb, и ни байтом меньше. Больше символов в запросе - больше места. Во-вторых, и что еще более важно, характер и общая "заточенность" хранимой процедуры sp_executesql предполагают что у нас есть статическая строка запроса и статическая же строка с именами/типами параметров для него. Ровно как это было в предпоследнем нашем опыте, см. переменные @query/@param. Меняются при каждом вызове лишь значения параметров. То есть при таком подходе никаких выискиваний "похожих" запросов быть, по хорошему, и не должно. Программист раз за разом "скармливает" оптимизатору абсолютно, до последнего пробела, идентичные @query/@param и сопоставление текстов запросов происходит просто в силу того факта что это один и тот же запрос, но с разными параметрами, а точнее их значениями. Конечно, одни и те же @query/@param могут встречаться в нескольких файлах исходного кода, написанных разными членами команды разработки и в разное время, и тогда, как говорится, "появляются варианты". Совет здесь совершенно прежний - поддерживайте постоянство стилистики исходников на уровне всей вашей группы. Автор за свою долгую IT-карьеру вообще приходит к выводу, что среди всех языков программирования T-SQL наиболее "капризен" ко всяким "рюшечкам" исходного кода - лишние пробелы, регистр, переносы... Не строг (как бы он был в этом вопросе просто строг это б было и неплохо вовсе), а именно что "капризен". Вроде и свобода полная, и пиши как хочешь, но! Боком такая свобода выходит на самом деле. Впрочем - мы отвлеклись, а заключение будет таким: резон в отказе от shell-планов для не adhoc запросов очень даже имеется.

Так же не путайте хранимую процедуру sp_executesql с ее вечной коллегой-соперницей по созданию и исполнению динамических запросов, командой EXECUTE (она же EXEC). В вопросах на которых мы фокусируем свое внимание в рамках данной статьи между этими двумя механизмами нет абсолютно ни единой точки сходимости (и вообще у них больше различий чем сходства, но именно в обсуждаемом вопросе "разбег" между ними достигает максимальной дистанции). sp_executesql - это безусловное указание перехода кручной параметризации, вне зависимости от настроек любой из баз данных. EXEC - это не менее безусловное указание пользоваться тем режимом авто-параметризации, что установлен для текущей базы. Просто помните, что отрывок кода

1
2
3
4

DECLARE @query varchar(200)
SET @query = 'SELECT ColID, Col2, Col3 FROM T1 WHERE Col2=' + '5555'
EXEC (@query)
go

есть суть запрос

1	SELECT ColID, Col2, Col3 FROM T1 WHERE Col2=5555

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

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

Не менее хорошо (а в определенном смысле и еще лучше) дела с ручной параметризацией запросов обстоят если мы выберем второй путь ее реализации - хранимые процедуры. Для примера давайте посмотрим на такую:

1
2
3
4
5
6

CREATE PROC mySP @my_param1 varchar(2), @my_param2 varchar(2)
AS
IF EXISTS (SELECT * FROM T1 WHERE Col3=@my_param1)
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3 IN (@my_param1, @my_param2)
ELSE
SELECT ColID, Col2, Col3 FROM T1 WHERE Col2 = 9999

Совершенно очевидно, что уж если оптимизатор разобрался где находятся параметры у sp_executesql и какого они типа, то что уж говорить о CREATE PROC, чей синтаксис предполагает специальную секцию для описания именно входящих параметров. Посмотрим как оно все "в деле":

1
2
3
4
5
6
7
8

DBCC FREEPROCCACHE
go
EXEC mySP 'ab', 'cd'
go
EXEC mySP 'ab', 'zz'
go
EXEC mySP 'tt', 'dd'
go

"Запрос кэша" сообщает на это:

objtype usecounts size_in_bytes plan_handle text
Proc 3 114688 0x05000F... CREATE PROC mySP @my_param1 varchar(2), @my_param2 varchar(2)...

Во-первых, обратите внимание что мы имеем дело с кэшированным планом нового типа - Proc. До этого момента нам встречались только Adhoc планы (планы запросов не прошедших через фильтры авто-параметризации, либо shell-планы) и Prepared планы (параметризированный план для нескольких shell-планов). Вполне понятно из названия, что кэшированный объект такого типа был построен из исходного кода хранимой процедуры. Во-вторых, как и ожидалось, такой Proc-план был построен однажды, а вызван трижды, с разными значениями параметров. То есть параметризация налицо. В-третьих, снова никаких shell-планов. И уж если их отсутствие было объяснимо в случае sp_executesql, то тут он не нужен абсолютно. Повторное использование Proc-плана строится не на текстовом совпадении двух запросов, а на вызове той же самой процедуры, что оптимизатор элементарно выясняет просто по ID этого объекта. В-четвертых, обсуждая инструкции не поддающиеся авто-параметризации в любом из двух ее режимов, мы упоминали и "запросы содержащиеся внутри хранимых процедур и функций". Живой пример: последний SELECT в коде нашей процедуры с константой 9999 мог бы быть прекрасно параметризирован (и даже в simple режиме), но этого сделано не будет. Потому что, во-первых, ручная работа, как мы говорили, предполагает отключение автоматических помощников, а во-вторых, раз SQL-программист считает что пользователей могут интересовать только строки с 9999 (а иначе бы он сообразил ввести в объявление процедуры третий параметр, не правда ли?) - так оно и есть, параметр в этом месте просто ни к чему.

Пожалуй стоит еще упомянуть вот о чем. SQL Server 2005-й версии предложил новую "фичу" - рекомпиляцию хранимых процедур на уровне инструкций (statement-level recompilation). Несмотря на то, что сама по себе "фича" несомненно полезна, продуктивна, да и просто восхитительна (хотя и вновь находится за пределами границ данной публикации), ее внедрение породило столько "тумана" в головах DBA любых степеней "продвинутости", что автор далеко не уверен в положительности кумулятивного эффекта от такой новинки. Так, широко разошелся и был подхвачен миф о том, что теперь, мол, каждая отдельная инструкция внутри тела хранимой процедуры генерирует свой, отдельный план, и этот план может быть использован позже, если та же самая инструкция приходит к оптимизатору в виде независимого adhoc запроса. Ну что тут скажешь... Наша процедура mySP содержит, как мы видим, три отдельных инструкции SELECT. Наблюдаем мы три кэшированных плана? Ничего мы не наблюдаем, кроме единственного плана для именно целой хранимой процедуры. А помимо этого:

1
2
3
4
5
6
7
8
9
10

DBCC FREEPROCCACHE
go
EXEC mySP 'ab', 'cd'
go
EXEC mySP 'ab', 'zz'
go
EXEC mySP 'tt', 'dd'
go
SELECT ColID, Col2, Col3 FROM T1 WHERE Col2 = 9999
go

Если бы миф был справедлив, то последнему SELECT-у в скрипте никаких планов не понадобилось бы, он просто посимвольно идентичен последней инструкции нашей процедуры и был бы ей сопоставлен безо всяких проблем. На самом же деле:

objtype usecounts size_in_bytes plan_handle text
Proc 3 114688 0x05000F... CREATE PROC mySP @my_param1 varchar(2), @my_param2 varchar(2)...
Adhoc 1 24576 0x06000F... SELECT ColID, Col2, Col3 FROM T1 WHERE Col2 = 9999
Prepared 1 40960 0x06000F... (@1 smallint)SELECT [ColID],[Col2],[Col3] FROM [T1] WHERE [Col2]=@1

Как мы видим - "мухи отдельно", и котлеты, соответственно, тоже. Proc-планы не используют готовых Adhoc/Prepared-планов и наоборот.

Параметризация ручная, на стороне клиента.

Как известно, в непростом вопросе доступа клиентов к данным на сервере существует две основных "генеральных линии":

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

То есть клиент или "дергает" хранимые процедуры, или "осыпает" сервер самим им придуманными adhoc запросами. Разумеется, в реальности все сложнее, и существует куча вариаций и под-вариаций, а так же прорва их сочетаний, но упрощая можно сказать, что для отдельного действия с данными клиент или вызывает ХП, или шлет свою собственную инструкцию. Что лучше мы обсуждать не будем (хотя процедуры значительно лучше ), а примем как факт: существуют оба подхода. Что мы можем сказать о них в разрезе параметризации запросов? Если для клиента предусмотрительно заготовлены все потребные ему процедуры, то добавить к сказанному выше решительно нечего, вопрос параметризации решается SQL-программистом в момент создания каждой такой процедуры. А вот если процедур нету...

Если их нет, то у клиента, в свою очередь, тоже появляются две "генеральных линии". Первая из них предлагает никакой этой вашей параметризацией не морочиться, а просто слать нужные в данной точке кода клиента запросы как они есть и дело с концом. Это, понятно, будут самые обычные adhoc запросы и помочь в деле параметризации сможет (если сможет, и это очень большое "если") лишь автоматические ее разновидности. Возьмем, для примера, ADO.NET как платформу доступа к данным, а C# как язык общения с этой платформой. На этом самом C# набросаем вот такого примитивного консольного клиента, без всякого интерфейса пользователя, способного лишь послать два статичных запроса к серверу (вам, скорей всего, придется подредактировать строку подключения для ваших условий):

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
27
28
29
30
31
32

using System;
using System.Text;
using System.Data.SqlClient;

namespace ClientParameterization
{
  class Program
  {
  static string conn="Network Library=dbmslpcn;Data Source=WINR2;Initial Catalog=~DB~;Integrated Security=sspi";
  static string cmd1="SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab'";
  static string cmd2="SELECT ColID, Col2, Col3 FROM T1 WHERE Col2=5555";

  static void Main()
  {
  using(SqlConnection connection=new SqlConnection(conn))
  {
  try
  {
connection.Open();
SqlCommand sqlCmd1=new SqlCommand(cmd1, connection);
SqlCommand sqlCmd2=new SqlCommand(cmd2, connection);
sqlCmd1.ExecuteNonQuery();
sqlCmd2.ExecuteNonQuery();
  }
  catch(Exception ex)
  {
Console.WriteLine("Error detected: "+ex.Message.ToString());
  }
  }
  }
  }
}

Что вернет показанная пара запросов ни нам, ни нашему клиенту не интересно, поэтому результат просто отбрасывается, но сервер, конечно же, должен отработать каждый запрос "по полной". Так вот сначала командой DBCC FREEPROCCACHE на сервере очистим кэш, затем запустим нашего консольного клиента, а затем, нашим стандартным "запросом кэша" посмотрим что в этом самом кэше окажется. А окажется там вот что (учитывайте, что целевая база обоих запросов ~DB~ находится в режиме простой авто-параметризации):

objtype usecounts plan_handle text parameterized_plan_handle
Adhoc 1 0x06000F0056... SELECT ... WHERE Col2=5555 0x06000F00DC...
Prepared 1 0x06000F00DC... (@1 smallint)SELECT ... WHERE [Col2]=@1 NULL
Adhoc 1 0x06000F0053... SELECT ... WHERE Col3='ab' NULL

То есть, наш примитивнейший из клиентов ничем не хуже клиента "взрослого", SQL Server Management Studio. Запросы удовлетворяющие условиям простой параметризации - параметризуются (а их текст нормализуется, во всеми вытекающими). Запросы не прошедшие через тот же фильтр не претерпевают абсолютно никаких изменений. Все это мы уже видели и знаем из первой и второй частей статьи.

Ну а вторая линия поведения возможная на клиенте исходит из того, что программист его возьмет на себя дополнительный труд и явно укажет, что запрос подлежит параметризированию и даже "объяснит" серверу в каком месте. Труд этот (в случае платформы ADO.NET) определенно не является чем-то неподъемным, но "клацанья по клавишам" нашему программисту точно добавится, судите сами:

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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ClientParameterization
{
  class Program
  {
  static string conn="Network Library=dbmslpcn;Data Source=WINR2;Initial Catalog=~DB~;Integrated Security=sspi";
  static string cmd1="SELECT ColID, Col2, Col3 FROM T1 WHERE Col3=@my_param1";
  static string cmd2="SELECT ColID, Col2, Col3 FROM T1 WHERE Col2 IN (@my_param2, @my_param3)";

  static void Main()
  {
  using(SqlConnection connection=new SqlConnection(conn))
  {
  try
  {
connection.Open();
SqlCommand sqlCmd1=new SqlCommand(cmd1, connection);
SqlParameter param1=new SqlParameter("@my_param1", SqlDbType.VarChar);
param1.Value="ab";
sqlCmd1.Parameters.Add(param1);
SqlCommand sqlCmd2=new SqlCommand(cmd2, connection);
SqlParameter param2=new SqlParameter("@my_param2", SqlDbType.SmallInt);
param2.Value=5555;
sqlCmd2.Parameters.Add(param2);
SqlParameter param3=new SqlParameter("@my_param3", SqlDbType.SmallInt);
param3.Value=1111;
sqlCmd2.Parameters.Add(param3);
sqlCmd1.ExecuteNonQuery();
sqlCmd2.ExecuteNonQuery();
param1.Value="cd";
sqlCmd1.ExecuteNonQuery();
  }
  catch(Exception ex)
  {
Console.WriteLine("Error detected: "+ex.Message.ToString());
  }
  }
  }
  }
}

Но зато и результат будет куда как роскошнее:

objtype usecounts text
Prepared 1 (@my_param2 smallint,@my_param3 smallint)SELECT ... WHERE Col2 IN (@my_param2, @my_param3)
Prepared 2 (@my_param1 varchar(2))SELECT ... WHERE Col3=@my_param1

Идеально! Все на своих местах и с правильными типами! Стоит обратить внимание, что такая клиентская параметризация вновь обходится без услуг shell-планов, и вы правильно догадываетесь: стоит в коде клиента вместо string cmd1="SELECT ColID,..." написать string cmd1="select ColID,..." как вся работа псу под хвост - абсолютно тот же самый запрос строит для себя отдельный план полностью дублирующий уже имеющийся. Так что если вы считаете, что все эти разговоры о красивом (или правильном) форматировании кода ведут те кому в обеденный перерыв заняться нечем, срочно меняйте свою точку зрения. Это как раз та красота, что не ради "глянец навести", а ради целей исключительно практичных и прагматичных.

Вместо заключения или за что боролись?

Выдохнув после освоения материала статьи столь внушительного объема задумаемся - а ради чего все это? Ясно, что мы очень хотим, что бы параметризация имела место везде, где она уместна - но зачем? Какова мета-задача? Это как раз тот момент, когда из-за отдельных деревьев не видят всего леса, и хотя мы момент этот уже освещали, но внимания на нем не заостряли, а оно того стоит!

Итак, может показаться, что целью параметризации является "сжатие" кэша планов. Ну то есть если 10 запросов пользуются одним общим планом, то в кэше лежит он один. А могло бы лежать 10. Логично? Логично! Но не до конца. Например, параметризация adhoc запросов в автоматическом режиме приводит к тому, что в кэше вместо одного плана появляются сразу два (shell и "нормальный"). Которые в сумме занимают место уж точно не меньше чем единственный план. Если новый adhoc запрос не может быть сопоставлен имеющемуся плану, он вновь генерит два новых плана, а если может, но текст его отличается хотя бы регистром одной буквы, то генерится еще один shell plan. То есть выясняется, что вопросом экономии RAM параметризация не очень-то и заморачивается. И на то, опять же, есть свой резон.

Пока у нас в системе свободного RAM "море разливанное" все выглядит логично. Нужны оптимизатору два плана вместо одного? Да хоть пять, памяти "залейся"! Когда же RAM начинает "поджимать", то тоже ничего катастрофического не происходит. Сначала отбрасываются shell-планы. Если давление нехватки RAM будет ощутимым, но умеренным, и "ведущий" план на который все эти shell-ы ссылаются в памяти сохранится (а движок приложит к тому все усилия), то пересоздать именно и только shell - реально пара пустяков. Истинную ценность представляет лишь Prepared-план, а shell он что... "оболочка дешевая". Но, предположим, что и после удаления всех shell-ов давление на RAM не только не спало, но и возрасло. Ну вот такая пиковая нагрузка на сервер пошла. Тогда, ничего не попишешь, придется начать выкидывать полноценные планы, что совсем нехорошо, но если память нужна под иные нужды... Итого, в смысле экономии RAM, движок запросто обходится безо всякой параметризации, просто по мере необходимости удаляя планы от более "дешевых" к более "дорогим" (и тем высвобождая RAM), и пересоздавая их заново, когда свободной памяти вновь станет много.

На самом деле главная, основная и практически единственная "фишка" параметризации - избежать компиляции плана уже имеющегося в кэше. То есть не строить план заново, а сделать то, что по-английски называется коротко и ясно: re-use plan. Вот вокруг этого все и крутится. Тут надо себе четко представлять, что оптимизатор SQL Server потрясающе сложная вещь. Несомненно, это один из самых головоломных программных компонентов, реализованных кем-либо, когда-либо и на любом языке программирования. Может и не TOP1 в этом списке, но где-то в районе призовых мест. Процесс оптимизации/компиляции плана действительно сложного запроса запросто "дает прикурить" даже современным и многоядерным CPU. Можно и по другому - запрос простой, но сразу 100. И разных. CPU тоже скучать не будет. В общем, "битва" идет за уменьшение нагрузки на CPU путем пропуска указанного "тяжелого" процесса. Вот это и есть та самая мета-задача, на которой мы хотели заострить свое внимание.

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

500 пользователей и сервер. Все пользователи работают с одним и тем же клиентом. Программисты оного шлют исключительно adhoc запросы и делают это довольно интенсивно. Не исключено, что сам термин "параметризация" не говорит этим программистам ровным счетом ничего;
целевая база всех этих многочисленных adhoc запросов в данный момент находится в режиме PARAMETERIZATION SIMPLE. Вы являетесь DBA описываемой системы и замечаете, что при означенных условиях пиковая загрузка CPU достигает 90-95%. Клиент, после нажатия на кнопку "запросить данные", "задумывается" на вполне ощутимый промежуток времени, особенно если это нажатие сделать в середине рабочего дня;
вы, конечно же, могли бы поднять бучу на тему "а давайте еще 500GB RAM-а купим, а еще 8 CPU!". Однако, вместо этого, вы "легким движением руки" меняете настройки все той же целевой базы на PARAMETERIZATION FORCED. Немедленно после этого, и на том же самом оборудовании, нагрузка на CPU падает до... 5-10%!! Клиент начинает просто "летать" в любое время дня и ночи.

Если вы думаете, что описанный сценарий слишком хорош и больше похож на сказку, то уверяю вас, что именно его, и именно с указанным диапазоном нагрузки на процессор, разными словами пересказывало слишком много людей, в том числе слишком известных в SQL-сообществе что бы им еще искать себе дешевой популярности. Автор верит им безоговорочно, просто потому, что и сам был свидетелем подобного случая, когда на реальной системе (именно на продакшене) нагрузка упала от ~80% до ~25% после смены однойопции базы. Поймите еще раз: сделать "с нуля" план для среднего запроса - это тяжело, реально тяжело. По мере роста сложностей запросов эта тяжесть так же растет. И когда его, план этот, делать не нужно нагрузка на систему заметно и ощутимо падает. Конечно, один, даже экстра-сложный запрос, значительные просадки в производительности готового решения не вызовет (либо она будет измерятся парой секунд в худшем случае). Но вот если пусть и не такие сложные запросы, но "сыпятся" на ваш сервер без остановки...

Вы, конечно, можете заметить, что клиент может просто "по жизни" слать абсолютно несовпадающие запросы, для каждого из которых нужен свой план. Это не исключено, хотя обычно пользователи выбирают пару-тройку сотен "любимых" их запросов и варьируют лишь их параметры, а запросы в эту сотню не входящие "всплывают" лишь эпизодически и погоды не делают. Но если каждый запрос клиента действительно уникален PARAMETERIZATION FORCED вам не поможет, это да. Так же будет бессильна эта опция, если запросы составлены таким хитрым образом, что их не способен параметризовать даже принудительный авто-режим. Однако "средне-типичный" запрос обыкновенно подвержен параметризации в этом режиме и не может быть параметризирован в режиме альтернативном.

Отсюда вы задаете автору второй вопрос - если PARAMETERIZATION FORCED настолько "крут" и является чуть ли не волшебной кнопкой "работай быстрее", то зачем существует режим альтернативный? Да и более того, зачем этот альтернативный (простой) режим является опцией по умолчанию? И это - хороший вопрос. Действительно, почему бы не выкинуть простой режим авто-параметризации как класс? А вот тут дело уже не в самих этих режимах, а в параметризации как таковой, особенно в последствиях которые нам "грозят" в случае ее успешной реализации. Факт заключается в том, что параметризированный и запасенный "на будущее" готовый план это не только хорошо, это еще и... плохо. Да, автор вообще рекомендовал бы изучение SQL Server на всяких этих философских факультетах в ВУЗах, как ярчайший технический пример единства и борьбы противоположностей. Возможно часть читателей удивится, а часть знает и так, но иногда, и не так уж редко, мы будем предпринимать "контр-параметризационные" меры, дабы механизм который мы изучали на протяжении всей данной статьи не срабатывал. И что бы готовый план из кэша, сколь удивительным это не покажется, не использовался бы повторно. И простой режим авто-параметризации это одна из таких контр-мер, а вообще их несколько, от очень простых до умеренно сложных. Но это уже будет совсем иная история. И своя статья.

Ссылки по теме