Итак, блог sqlCMD.ru продолжает цикл публикаций посвященный такой непростой теме, как параметризация запросов. В статье открывающей данный цикл, Параметризация запросов. Ваш лучший друг? мы выяснили что грамотно и к месту примененный данный механизм способен дать выигрыш в итоговой производительности решения столь значительный, что даже подтвержденный сухими цифрами «до» и «после» он все-равно продолжает выглядеть сказочно-невероятным. Были указали и причины такой «сказочности»: анализ запроса, построение нескольких альтернативных планов исполнения для него, выбор из этих возможных кандидатов «лучшего из лучших» — все это чрезвычайно ресурсоемкие задачи, и даже для современных серверов с их мультипроцессорными «фишками». А поэтому пропуск этих задач целиком дает такой прирост в скорости выполнения запроса, какой невозможно обеспечить никаким наращиванием железа (мы, разумеется, говорим о потенциальном увеличении числа/качества CPU сервера; вложения в, допустим, апгрейд дисковой подсистемы помочь именно в вопросах быстрейшей оптимизации запросов не могут никак просто по определению). Так вот чем покупать коробку новых/дополнительных CPU и загружать их работой, выгоднее (с любой точки зрения) не покупать ничего, а воспользоваться работой уже готовой, в смысле результатами такой предварительной работы. Именно так и поступает параметризация, значительно повышая наши (и наших пользователей) шансы взять «готовую работу» из кэша планов (plan cache), вместо осуществления полной и очень трудоемкой цепочки, где первым звеном является запрос на языке T-SQL, а звеном финальным — идеальный (или близкий к таковому) план исполнения, помещаемый, к слову сказать, опять же в тот же самый кэш планов.
Однако указанная статья открывающая цикл завершалась довольно интригующе. В ее заключительном абзаце было сказано, что параметризация это не только удивительно хорошо, но и в тоже самое время... плохо! 
Обещалось продолжение, в котором должна была проясниться такая двойственность одного и того же процесса, а так же должны были появиться пояснения, почему мы подчас захотим бороться не за параметризацию, а решительно против нее. Ну и описание методов такой «борьбы против» так же были анонсированы. Автор рад сообщить постоянным и новым своим читателям, что такое продолжение — перед вами. Оно, как представляется автору, имеет и самостоятельную ценность, однако крайне и настойчиво рекомендуется к изучению именно как продолжение упомянутой выше статьи. По крайней мере писалось продолжение с тем расчетом, что все идеи, концепции, принципы и примеры кода приведенные в статье стартовой известны читателю и поняты им в полном объеме. Приятного и познавательного чтения, не забудьте открыть в соседнем окне SQL Server Management Studio. 
Темная сторона параметризации.
Давайте сразу обратимся к коду и посмотрим на ситуацию, когда параметризация превращается, выражаясь языком не столь отдаленных времен отечественной истории, из вашего «верного друга и соратника» в «агента всех империалистических разведок». 
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 | USE master go CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 75 MB, MAXSIZE = 75 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 100 MB, MAXSIZE = 100 MB ) GO ALTER DATABASE [~DB~] SET PARAMETERIZATION FORCED GO USE [~DB~] GO create table T1 (ColID int IDENTITY CONSTRAINT PK_id PRIMARY KEY, Vendor varchar(5) not null, EquipType varchar(10) not null, Phone varchar(32) not null, FAX varchar(32) not null, eMail varchar(15) not null, Comment char(300) not null CONSTRAINT DEF_Comment DEFAULT 'This is а place for comment...') GO SET NOCOUNT ON BEGIN TRANSACTION DECLARE @i int SET @i=1 WHILE @i<=122000 BEGIN INSERT T1 (Vendor, EquipType, Phone, FAX, eMail) VALUES (CASE WHEN @i%100=0 THEN 'IBM' ELSE 'DELL' END, CASE WHEN @i%15=0 THEN 'Notebook' ELSE 'Desktop' END, CASE WHEN @i%100=0 THEN '1-800-CALL-TO-IBM' ELSE '1-800-CALL-TO-DELL' END, CASE WHEN @i%100=0 THEN '1-800-FAX-TO-IBM' ELSE '1-800-FAX-TO-DELL' END, CASE WHEN @i%100=0 THEN 'ibm@corp.com' ELSE 'dell@corp.com' END) SET @i=@i+1 END COMMIT TRANSACTION CREATE NONCLUSTERED INDEX IDX_Vendor ON T1(Vendor) GO SELECT Vendor, COUNT(*) AS Cnt FROM T1 GROUP BY Vendor |
Что мы имеем «на входе»? Некая табличка T1 по учету компьютерной техники на складе/в офисе. Порядка 120тыс. записей сгенерированных таким образом, что фирма DELL становится нашим генеральным поставщиком (99% нашего оборудования получено от нее). С фирмой же IBM мы якобы сотрудничаем по остаточному принципу (лишь 1% записей). Для горячих поклонников оборудования последней фирмы готовых обидеться на подобный дисбаланс, автор предупреждает: все герои его повествования вымышлены, а совпадения с реально существующими фирмами/персонами случайны. 
Тем не менее стартовая диспозиция такова как она есть 99-к-1. Оба вендора поставляют нам и десктоп-машины, и ноутбуки, но доля последних значительно меньше первых. Так же таблица T1 имеет два индекса: кластерный по колонке первичного ключа и не кластерный по колонке как раз таки Vendor. Сама тестовая база данных переведена в режим принудительной авто-параметризации, однако мы для нашего теста выберем не ее, а параметризацию ручную, причем из трех вариантов последней остановимся на системной хранимой процедуре sp_executesql. Поскольку пользователи нашей системы обожают фильтровать свои запросы по колонке Vendor именно она становится целью нашей параметризации:
1 2 3 4 5 6 7 8 9 10 11 12 | USE [~DB~] GO DBCC FREEPROCCACHE GO set statistics io ON DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'DELL' |
Как обычно, для начала очищаем кэш планов — каждый наш опыт должен начинаться с «чистого листа». Далее заказываем отображение сведений о том как каждая из команд нагружает HDD, такая информация нам вскоре пригодится. Затем следует наш параметризированный запрос, причем реализовано все с помощью sp_executesql, как мы и наметили. Если опираться на информацию только из открывающей статьи цикла, то мы потрудились на славу — план будет создан (и связанные с этим процессом многочисленные ресурсы будут потрачены) лишь однажды, в момент первого обращения первого клиента предложившему серверу такой запрос. Все прочие обращения того же или последующих клиентов с тем же запросом будут пользоваться готовым планом полностью пропуская ту самую «ресурсоемкую цепочку». Причем что особенно приятно (по крайней мере это кажется нам приятным к текущей точке повествования) такой пропуск будет осуществлен вне зависимости от значения параметра для фильтрации колонки Vendor, главное что бы сам запрос оставался тем же самым.
Итак, пока мы видим сплошные плюсы от проделанной нами работы. Однако позволим пользователям поработать с нашим «идеальным» запросом. Пусть первый из них запросит оборудование поставленное нашим генеральным партнером, то есть фирмой DELL (последняя строчка последнего скрипта). Для анализа «хорошести» получившегося плана нам нужны 3 вещи:
- отчет по числу логических считываний. В условиях реального анализа вы, вполне возможно, захотите анализировать и физические чтения тоже. В нашем случае почти наверняка вся база будет в памяти вашей тест-машины после скрипта ее создающего и наполняющего данными. А поэтому для нас этот параметр ничего не даст, он (с вероятностью близкой к 100%) будет равен нулю. Но вот логические чтения — это другое дело, они действительно покажут нам степень (не)оптимальности того или иного запроса;
- сам план в графическом виде, причем мы предпочтем его актуальную, а не предполагаемую разновидность;
- так же мы захотим открыть окно свойств (клавиша F4) крайнего левого оператора плана (SELECT). А в этом окне нас будет интересовать свойство Parameter List, а более точно два его «под-свойства»:
- Parameter Compiled Value — значение параметра с которым план был создан, то есть скомпилирован;
- Parameter Runtime Value — значение параметра с которым план был исполнен;
Итак, для запроса выбирающего 99% всех строк (фирма DELL) число логических чтений составит 5833, а план/свойство Parameter List будут такими:
Быстрая «прикидка в уме» говорит нам что оптимизатор повел себя зело разумно, не захотев заморочиться с индексом по колонке Vendor. И то сказать: извлеки сначала значения первичных ключей для каждой строки удовлетворяющей фильтру, потом с этими ключами «сгоняй» в кластерный индекс и извлеки значения всех прочих столбцов кроме Vendor и ColID... И это зная, что вернуть нужно весь кластерный индекс за вычетом 1%! Ну так не проще просто отбросить из последнего лишние строки и вернуть оставшиеся? Так и сделано, мы видим полный скан по индексу PK_id (кластерный), что в данном случае, пожалуй, оптимально. Что же до параметра (точнее — до его значения) то пока все очевидно — с DELL план создался, с ним же и исполнился.
Теперь, на сервер заходит второй пользователь, и разбираемый нами запрос уже находится в кэше (и чистить его мы, разумеется, не хотим — иначе зачем нужна параметризация вообще?). Этому пользователю нужен фильтр прямо противоположный, по фирме IBM (1% всех записей), и он так и пишет:
1 2 3 4 5 6 7 8 | set statistics io ON DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'IBM' |
На этот раз мы имеем все те же 5833 логических чтений, а так же:
Тут нас уже могут начать терзать смутные подозрения... С учетом, что нам нужно-то всего 1% строк — не разумнее ли зайти именно со стороны индекса по колонке Vendor? Это, конечно, потребует дополнительного обращения к кластерному индексу, но зато «база» для финального резалт-сета будет извлечена практически мгновенно. А так мы пробегаем по всем строкам 99% которых нам просто не нужны... Впрочем, параметризация затевается что бы использовать тот же самый план, из кэша. Мы получили то к чему стремились, не так ли? Значения свойства Parameter List лишь подчеркивают этот факт: запрос был скомпилирован для/под параметр DELL, а исполнен для параметра IBM. Самое главное, мы миновали «ресурсоемкую цепочку», а число логических чтений даже если оно и является суб-оптимальным все еще находится в границах вменяемых значений для запроса подобного толка.
Теперь повторим эксперимент с нуля (то есть начнем вновь с чистого кэша), но только два пользователя посетят наш сервер в обратном порядке. Как ни удивительно, но порядок их посещения имеет колоссальное и даже катастрофическое (для пользователя номер 2) значение. Итак, в этот раз на сервер первым успевает «любитель IBM»:
1 2 3 4 5 6 7 8 9 10 11 12 | USE [~DB~] GO DBCC FREEPROCCACHE GO set statistics io ON DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'IBM' |
В этом случае с планом/Parameter List-ом у нас дела такие:
Так мы и предполагали — для столь незначительного числа извлекаемых строк выгоднее «заход» со стороны IDX_Vendor плюс «добор» информации из кластерного индекса. Число логических чтений так же понижается до 3750 — это, конечно, не на порядок меньше, но и раза в полтора тоже неплохо...
Далее приходит опоздавший «поклонник DELL»:
1 2 3 4 5 6 7 8 | set statistics io ON DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'DELL' |
Вы уже догадываетесь что его ждет:
Однако мало кто может предположить всего масштаба постигшей нашего бедолагу катастрофы: вместо 5833 логических чтений его запрос теперь требует... 370124!! 
Это настолько «круто», что разница во времени фильтрации по параметру DELL в первом эксперименте и сейчас видна на достаточно мощной тестовой машине просто визуально! И это при том, что наша тест-таблица содержит 120тыс. строк (что в масштабах SQL Server еще даже не есть «много»), а запрос ими оперирующий является «мега-элементарным». Можете легко себе домыслить как это все безобразие будет смотреться на таблице из 50 млн. строк с запросом на десяток-другой джойнов.
Теперь понимаете где столь дружелюбная (на первый взгляд) параметризация раскладывает «грабли» с очень даже жестким «черенком»? Правильно, в том самом месте, где один и тот же запрос (более точно — план такого запроса) идеально работающий с одним набором данных страшно «лажает» с другим. Кстати говоря, «вьювер» графических планов исполнения студии несколько усугубляет эти самые «грабли», и вместо того что бы помочь вам разобраться с ними успешно «затуманивает» картину. Для рассеивания этого «тумана» исполним две команды второго эксперимента в той же последовательности, но одним пакетом (batch):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | USE [~DB~] GO DBCC FREEPROCCACHE GO set statistics io ON DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'IBM' EXEC sp_executesql @query, @param, @my_param = 'DELL' set statistics io OFF |
Мы уже знаем, что вторая команда при таком раскладе будет столь чудовищно неэффективна, что говорить о каком-то равенстве по этому показателю с командой первой просто смешно. Это даже не учитывая того простого факта, что первая команда извлекает ~1тыс. строк, а вторая ~120тыс, что само по себе исключает любые рассуждения о равенстве, а тут еще сверх того план «заточенный» под IBM... Тем не менее студия бодро рапортует:
То есть вроде как цена/время исполнения двух показанных команд будут приблизительно равны, в то время как на самом деле если вам нужен пример двух команд не имеющих ничего общего по таким показателям, то вы их нашли. Это все проистекает из того, что цифры подчеркнутые на последней иллюстрации вычисляются очень просто. У каждого оператора плана каждой команды входящей в пакет есть показатель Estimated Subtree Cost — сколько «стоит» выполнение этого оператора плюс всех прочих операторов расположенных правее данного в графическом представлении плана. Если мы возьмем указанный показатель для оператора SELECT (самого левого на плане) то это и будет итоговая «стоимость» данной команды пакета. Если команд в пакете всего две (как в нашем случае), то показатель для оператора SELECT первой можно обозначить как ESC1, а для того же оператора второй — ESC2. Тогда первая подчеркнутая на иллюстрации цифра вычисляется так: ESC1*100%/(ESC1+ESC2). А вторая, соответственно, ESC2*100%/(ESC1+ESC2). В данном случае оптимизатор (и студия вслед за ним) «думают», что ESC1=ESC2.
Проверьте свое понимание материала: Пояснить, почему оптимизатор делает такое совершенно ошибочное предположение.
И, соответственно, все честно рассчитав по формуле «выкатывают» нам показанные выше цифры — 50% там, и столько же здесь. Ага, мы «верим»... Отсюда — вывод:
Проверьте свое понимание материала: Параметризация, как известно из статьи предыдущей, бывает двух классов(автоматическая/ручная), а каждый класс еще дополнительно делится на под-классы в зависимости от конкретной реализации данного механизма (простая/принудительная и sp_executesql/хранимая процедура/на стороне клиента соответственно). «Большая проблема» параметризации описанная в данном разделе статьи имеет универсальную природу или от нее страдают лишь некоторые классы/под-классы? Если вы считаете, что верен второй вариант составьте два списка: механизмов параметризации не подверженных указанной проблеме и механизмов попадающих в зависимость от нее.
Итак, что мы с вами установили доподлинно к текущей точке материала? А то, что значение параметра передаваемого при первом выполнении параметризированного запроса имеет решающее влияние на дальнейшую судьбу этого запроса, а так же на состояние нервной системы пользователя/пользователей обращающихся с тем же запросом (но с иным значением параметра) во второй, третий и все последующие разы. И это все потому, что оптимизатор не просто строит план запроса, а «затачивает» его под это самое конкретное «первое значение». То есть оптимизатору не просто интересно это самое первое значение, а это именно то чем оптимизатор интересуется чуть ли не в первые микросекунды после получения команды от клиента. То есть это попросту одна из важнейших характеристик всего запроса! И вот такая «замороченность» оптимизатора «первым значением» получила отдельное название — «вынюхивание параметра», это если в дословном переводе (на языке оригинала это будет parameter sniffing). Название довольно точно и емко отразило существующее положение дел в буквально паре слов. Действительно, движок (а точнее один из главных его компонентов — оптимизатор) «набрасывается» на каждый новый запрос «аки гончая» и начинает «обнюхивать» его со всех сторон (и особенно со стороны параметров) пытаясь сообразить «как бы нам с ним, с запросом вашим, половчее тут...».
Теперь слова статьи открывающий данный цикл о том, что иногда вы захотите предпринимать «контр-параметризационные» меры с целью недопущения последней, не должны представляться вам нонсенсом. Если подобных мер не предпринимать, то мы попадаем в зависимость от порядка запросов, как в нашем примере с таблицей T1. Причем даже не важно какой из запросов будет выполнен первым. Или, иначе говоря, не важно какое значение параметра запроса будет предложено на «обнюхивание» серверу первым. Факт в том, что как минимум часть последующих идентичных запросов имеющих иное значение параметра будут исполнены неоптимальным образом, может отличаться лишь степень этой неоптимальности, начиная от «не айс, но сойдет» и заканчивая «это просто ужас».
Давайте резюмируем полученные знания. Мы поняли и осознали:
- почему мы можем захотеть бороться с параметризацией. Потому что план очень хорошо работающий для значения 'ab' параметра @p может быть никуда не годным при смене значения того же параметра на 'cd';
- когда мы можем захотеть бороться с параметризацией. Когда селективность одного значения в колонке фильтрации резко отличается (причем не важно в большую или меньшую сторону) от селективности иных значений в той же колонке. Формальное определение селективности можно найти в части 3-й статьи Density, Selectivity, Cardinality или о чем «думает» оптимизатор. А если сказать тоже самое без «большой науки», то мы можем ожидать проблем когда у нас в целевой таблице строк со значением 'ab' в колонке Col1 две штуки, а со значением 'cd' в той же колонке много и много больше. Обратите внимание, что если перекос в распределении данных присутствует, но «перекошенная» колонка не участвует в фильтрации запросов пользователей — все отлично, нет причин для беспокойства. Однако нам несомненно будут встречаться колонки типа Vendor из нашей тестовой таблицы и прилагающиеся к ней соответствующие запросы.
Осталось «немного» — узнать как именно осуществлять эту самую «борьбу против». А это, на самом деле, вопрос преизрядный. Сервер очень хорошо усвоил аксиому «параметризация — наше все» и делает все возможное что бы она таки имела место. Однако «поправить» наш сервер с его излишним рвением все же возможно, и в заключительной части текущей статьи мы только этим и будем заниматься, причем снова несколькими способами.