- Другие части статьи:
- 1
- 2
Способы противодействия параметризации.
Итак, как вы уже поняли, сейчас нам предстоит изучить несколько «контр-параметризационных» методик. Обратите внимание, что на них можно смотреть и с обратной стороны — если целевая колонка фильтрации имеет хорошее и равномерное распределение данных, и, как следствие, вы ни разу не против параметризации а очень даже за нее, то вам следует всеми силами избегать упоминаемых далее приемов. Ведь они «рубят» параметризацию не задаваясь вопросом о ее уместности в данных и конкретных обстоятельствах. Так что все как обычно: решение принимаем мы, движок сервера его послушно исполняет.
Применение локальных переменных для передачи значения параметра.
Одним из простейших частично (и это замечание огромной важности, внимательно читайте далее и особое внимание уделяйте ближайшим примерам кода) работающих приемов является передача значения параметра не в виде константного числа/статичной строки символов, а его «обертывание» в переменную и передача в запрос уже ее. Обратимся к примеру автоматической (а не ручной) параметризации:
1 2 3 4 5 | USE [~DB~] GO DBCC FREEPROCCACHE GO SELECT * FROM T1 WHERE Vendor='IBM' |
Поскольку наша тестовая база находится в режиме FORCED-параметризации, то наличие альтернатив в вопросе извлечения требуемых строк никоим образом не может стать препятствием на пути параметризации данного запроса. Иными словами наличие индекса IDX_Vendor ничуть не смущает оптимизатор и он запросто параметризирует такой запрос. Если кто-то из читателей задается сейчас вопросом «а почему указанный индекс мог бы смутить оптимизатор?», то ему следует начать изучение материала не с текущей статьи, а со статьи открывающий цикл. Там же, кстати, вы найдете ответ на вопрос «а как я могу быть уверен(-а) что параметризация данного запроса именно что случилась?». Если же оба вопроса для вас очевидны — все отлично, у вас хорошая база (только не данных, а знаний), и мы продолжаем.
Итак, параметризация запроса случилась. Однако помимо параметризации произошла и еще одна важная вещь, а именно тот самый parameter sniffing. Почему эта вещь так важна? Потому, что из двух (в данном случае) альтернативных планов был выбран тот, что оптимален именно для значения параметра IBM:
А ведь, согласитесь, оптимизатор мог бы «зарядить» и сканирование кластерного индекса! Но — нет, значение параметра было оценено и был сделан верный выбор. Точнее он будет верным, пока не придет наш «DELL-поклонник» и не начнет получать свои 99% записей через поиск по не кластерному индексу. Ну теперь-то вы и сами в курсе об этом «проклятье» параметризации.
Так вот, согласно поставленной нами самими перед собой же задаче, мы не хотим что бы параметризация имела место. Даже если наша база находится в FORCED-режиме. Как это не покажется странным, но код
1 2 3 | DECLARE @v varchar(5) SET @v='IBM' SELECT * FROM T1 WHERE Vendor=@v |
поставленную задачу решает идеально. Однако не забывайте о предупреждении насчет «частичного решения» и дочитайте данный раздел до конца.
Опять же, для читателей статьи открывающей цикл тут нет никаких откровений. Из части второй упомянутого труда они прекрасно знают, что параметризация пакета (и автор напоминает, что план строится именно для пакета, а не для отдельных его инструкций) где значение параметра передается через переменную лишено какого-либо практического смысла — параметризация и не случается. Задача решена? Отчасти.
Во-первых, и это почему то часто забывается, отмена параметризации вовсе не эквивалентна отмене кэширования готового плана. То есть не параметризированный план для трех последних инструкций прекрасно кэшируется и готов к исполнению, вздумай мы запустить тот же самый пакет во второй, третий и так далее разы. А во-вторых — какой это будет план? Может показаться, что параметризирован он или нет, а сам план должен быть идентичным тому что на последней иллюстрации — нет разве? В том-то и дело что нет:
То есть, когда фильтрация производилась по значению IBM и это последнее было указано как литеральная строка, мы имели поиск по не-кластерному индексу. Все абсолютно тоже самое, но только IBM берется из переменной — сканирование кластерного индекса. Более того, если мы посмотрим на свойство Parameter List оператора SELECT последнего плана то увидим там... занятное:
Иными словами куда-то начисто испарилось «под-свойство» Parameter Compiled Value. 
То есть оптимизатор нам явно намекает, что он совсем-совсем не в курсе какое значение имел параметр @v в момент компиляции, а поэтому, уж конечно, не способен провести «вынюхивание» этого самого значения.
Проверьте свои знания SQL Server. Как уже было отмечено, движку (и оптимизатору) поступает на разбор и построение единого плана сразу три инструкции последнего скрипта. Кажется нет ни единой причины почему оптимизатор не способен «увидеть», что в данном пакете @v='IBM'. И все-таки такое простое равенство остается ему недоступным. Пояснить причину.
А к чему приводит отмена «вынюхивания параметра»? А к тому, что оптимизатор при анализе и выборе плана опирается не на конкретную, а усредненную статистику по целевой таблице. В данном случае «умный» оптимизатор рассуждает так: «я не знаю конкретного значения параметра @v и не могу посмотреть точную статистику. Однако — сколько строк в таблице T1 всего? Правильно, 122тыс. А сколько различных значений содержит колонка Vendor? Ага, вижу - два (DELL и IBM, если быть конкретным, но это не требуется, требуется просто цифра). Разбираемый запрос пытается отфильтровать строки таблицы T1 по одному конкретному (и снова не важно какому, важно что одному) значению. Тогда в среднем(!) такой запрос сколько будет возвращать строк? Так ведь 122тыс./2=61тыс.! Вот, значит в выходном резалт-сете будет такое число строк, теперь под это количество планчик подберем...». Ну а то что в выходном резалт-сете по факту оказывается не 61тыс., а в 60 раз меньше строк это уже судьба, к оптимизатору какие вопросы?
Итого, отменив параметризацию мы так же отменили и «привязку» плана к первому значению. По сути это и есть решение нашей задачи. Ведь чего мы добиваемся? Мы добиваемся что бы или a)план запроса не кэшировался вовсе, а создавался каждый раз заново исходя из анализа значения параметра, или b)уж если план кэшируется, то что бы это был хотя бы «усредненный» план нормально (об оптимальности, понятно, речи для «перекошенной» колонки нет) срабатывающий для большинства (желательно — для всех) потенциальных значений параметра. Мы только что решили задачу путем (b). А если ее же путем (a)? Да легко, в общем-то:
1 2 3 | DECLARE @v varchar(5) SET @v='IBM' SELECT * FROM T1 WHERE Vendor=@v OPTION (RECOMPILE) |
Дабы нам не свалится в совсем уж дикие дебри внутреннего устройства движка считайте так: с опцией RECOMPILE оптимизатор компилирует план пакета по прежнему не зная значения для @v, однако он не пытается скомпилировать именно инструкцию помеченную указанной опцией. И передает такой «полуфабрикатный» план на исполнение query execution engine, и тот начинает его исполнять, но исполнив первые две команды «притормаживает» и «спрашивает» у оптимизатора «что дальше?». Оптимизатор извлекает ставшее известным (к этой точке времени) значение для параметра @v и базируясь на нем «достраивает» план для третьей инструкции и передает одну эту «достройку» вновь на исполнение. В общем, по итогу — вуа-ля и перед нами снова наш «идеальный» план для значения IBM:
Еще раз подчеркну, что предыдущий абзац лишь приблизительно (но достаточно для общего понимания) описывает то, что происходит в недрах движка при задействованной опции RECOMPILE когда она применена к ad-hoc запросу входящему в пакет. Впрочем об этой опции у нас будет разговор особый и отдельный, просто продолжайте чтение статьи.
Не забудьте, что показанное только что решение полностью отменяет кэширование плана как таковое! В этом случае, понятно, сами термины «параметризация» и «вынюхивание параметра» теряют свою актуальность. То есть, предлагая движку для исполнения последний скрипт с обсуждаемой опцией во второй и последующие разы мы просто будем проходить ту самую «ресурсоемкую цепочку» о которой говорилось в начале статьи от первого до последнего звена.
Теперь — опробуем как работает выбранная нами «контр-параметризационная» технология в случае ручной параметризации:
1 2 3 4 5 6 7 8 9 10 | DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) DECLARE @v varchar(5) SET @v='IBM' SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = @v |
Понятно, что это довольно странно: с одной стороны мы применяем системную хранимую процедуру sp_executesql чья задача всемерно способствовать делу параметризации и всячески продвигать ее в жизнь. С другой — мы перед собой ставим задачу ровно противоположную. Однако как было заявлено автором в статье открывающий цикл, SQL Server это вообще ярчайший технический пример единства и борьбы противоположностей и такая «борьба нанайских мальчиков», то есть нас самих с нами же самими, встречается в нем не так уж редко. Так что ситуация вполне жизненная, уверяю вас.
Так вот — произойдет ли параметризация в этом случае автор у вас не спрашивает, согласно самому синтаксису sp_executesql параметры явно выделяются программистом и параметризация происходит, фигурально выражаясь, на уровне исходного кода. А вот над чем стоит задуматься — какой именно план будет выбран для показанного запроса? То есть случится ли parameter sniffing или нет? Ведь мы бьёмся над тем, что бы оптимизатор не «затачивал» план под конкретное значение параметра, а он может это сделать только «вынюхав» значение этого самого параметра.
После практически аналогичного примера с автоматической параметризацией и данных к нему пояснений ответ кажется очевидным: поскольку оптимизатор на момент компиляции не знает (и не может знать даже теоретически) о реальном значении переменной @v то ни о каком parameter sniffing речи нет. Возможно вы уже устали удивляться нечеловеческой изворотливости и непредсказуемости SQL Server но приведенный ответ «по аналогии» таки неверен. План будет вновь «заточен» под значение IBM, а «под-свойство» Parameter Compiled Value займет свое исконное место. То есть тот самый parameter sniffing о котором еще предложение назад и речи быть не могло отработает по полной программе. Ну а кто обещал что легко будет? 
Разбираемся — что у нас на этот раз? Почему два максимально близких друг-другу на логическом уровне кода (собственно на этом уровне они на 100% идентичны) на уровне физическом не имеют ничего общего? Загвоздка, как легко предположить (но от этого ничуть не легче пояснить), заключается в применении хранимой процедуры sp_executesql. Благодаря ее наличию события компиляции-исполнения последнего нашего скрипта обретают такие формы и последовательности:
- указанный скрипт поступает на разбор и построение плана целиком, как пакет;
- для всех инструкций пакета кроме последней генерируются операторы плана которые вы и ожидаете;
- для последней же инструкции генерируются не набор операторов выполняющих запрос SELECT * FROM... (как мы того ждем интуитивно), а единственный оператор EXECUTE PROC. Фактически оптимизатор лишь помечает место в плане где предстоит дополнительная (и пока что неизвестная) работа;
- и вот такой план и поступает на вход query execution, и начинает им исполняться;
- дойдя по метки «дополнительная работа» исполнитель возвращает оптимизатору реальное ее содержимое (как раз ту самую строку кода SELECT * FROM... ) а так же все параметры этой работы, включая и известные к этому моменту их значения;
- оптимизатор отдельно компилирует строку SELECT * FROM..., создает только для нее отдельный план и отдельно же этот план кэширует. То есть по завершению данного пункта списка в кэше окажутся два плана: один для всего пакета и один только для строки динамического SQL;
- ясно, что в силу двух предыдущих пунктов parameter sniffing становится не только возможным теоретически, но и, по факту, неизбежным событием;
- итого, этот отдельный и заточенный под определенное значение параметра план возвращается исполнителю и «доисполняется» последним;
- для незнающего всей описанной только что кухни стороннего наблюдателя создается полное впечатление что имел место обычный двухфазный рабочий процесс: компиляция всего - исполнение всего. То что эти две фазы затевают такую хитрую «перепасовку» между собой догадаться почти невозможно, нужно просто знать.
Для закрепления знаний почерпнутых из последнего списка:
1 2 3 4 5 | CREATE PROC p2 AS BEGIN ... END |
При выполнении показанной команды будет построен план исполнения для хранимой процедуры p2? Правильно, не будет! Что вы говорите? Что слышали/читали о «пред-компиляции» кода хранимых процедур? Так срочно избавляйтесь от этого мифа, никакой «пред-компиляции» нет и быть не может по одному очень простому, но от того не менее весомому, доводу: создание процедуры p2 не гарантирует ее хотя бы однократное исполнение. Ясно, что в 99.9 процентов случаев раз процедуру создали, то уж наверно ей воспользуются хотя бы однажды. Однако «почти всегда» не эквивалентно «в 100 процентах случаев». Зачем мы будем тратить ресурсы сервера на создание плана, а RAM на его кэширование если может сработать тот самый 0.1 процент? Вам не кажется что разумнее дождаться именно факта первого исполнения и уж тогда...? Продолжим:
1 2 3 4 5 6 | CREATE PROC p1 AS BEGIN ... EXEC p2 END |
Теперь вы все знаете — ни плана для p1, ни плана для p2 у нас нет и быть не может. А вот теперь:
1 | EXEC p1 |
Будут ли планы существовать для последней строчки кода? Вопрос с большим подвохом! На момент начала исполнения этой строки в нашем распоряжении по прежнему не будет ни одного плана, на момент окончания исполнения той же строки оба плана будут не только готовы, а уже даже надежно «засажены» в кэш. Ну и вопрос на «пять плюс»: а между двумя означенными моментами времени оба плана возникнут единомоментно или один будет всегда опережать по «дате рождения» другой? Правильно, план для p1 будет всегда на несколько микро-/милли-секунд старше своего коллеги для p2. И все это поясняется одним простым правилом:
Так вот в том пакете последовательность компиляции/исполнения которого мы расписали столь подробно чуть выше, он сам будет ближайшим аналогом процедуры p1, а строка динамического SQL-кода SELECT * FROM... внутри него — аналогом процедуры p2. Улавливаете параллели?
Итого — какой практический вывод можно сделать о применимости технологии отмены параметризации через использование локальных переменных? Вывод такой, что для ad-hoc запросов и автоматической параметризации подход вполне себе рабочий. Мы запросто подавляем parameter sniffing и, как следствие, подавляем создание «узкоспециализированных» планов. Тот же подход будет так же четко работать для части методик ручной параметризации, например для отдельной хранимой процедуры:
1 2 3 4 5 6 7 | CREATE PROC p3 @prm varchar(5) AS BEGIN DECLARE @t varchar(5) SET @t=@prm SELECT * FROM T1 WHERE Vendor=@t END |
Тут тоже все отлично, parameter sniffing отменен, строится «усредненный» план. Однако этот же подход «обламывается» как только исполнитель (query execution engine) делает «обратный звонок» своему «боссу» (оптимизатору, то бишь) и этот последний дает уточняющие указания по уже (казалось бы) готовому и полностью статичному плану исполнения. Две наиболее типичных и распространенных конструкций кода когда такой «обратный звонок» случается: хранимая процедура вложенная в другую хранимую процедуру и системная хранимая процедура sp_executesql как одна из инструкций пакета.
Явное указание «типичного» значения параметра.
Альтернативный подход к борьбе с параметризацией заключается в явном указании «типичного» значения параметра. Что есть «типичный»? Допустим, все та же таблица T1 содержит не 2 поставщика (колонка Vendor), а 50. При этом 49 из них имеют крайне незначительное число записей в этой таблице, максимум 1% для отдельного поставщика, обычно меньше. В эту категорию попадает и наш условный поставщик IBM. И лишь один из пятидесяти поставщиков явно доминирует в таблице занимая, допустим, 70-80% всех записей. Ближайший аналог в нашем примере — DELL, разумеется. Мы по опыту знаем, что пользователи системы запрашивают данные из таблицы T1 и проводят фильтрацию по ее колонке Vendor практически равномерно, то есть все поставщики эквивалентно интересны нашим пользователям. В указанном смысле DELL, IBM и остальные 48 поставщиков совершенно «равноправны». В таком сценарии — какое значение параметра будет «типичным»? Да уж не DELL, как легко догадаться... Иными словами «типичное» значение параметра представляет «типичное» же распределение данных в целевой таблице. И вот тогда метода к рассмотрению которой мы переходим, позволяет выделить такое значение и ткнуть в него оптимизатор «носом», дабы последний «заточил» план под это значение, а не под фактически переданное в запрос.
Технически подход реализуется весьма несложно, нужно просто прописать подсказку запроса OPTIMIZE FOR. Допустим для простейшего случая ad-hoc запроса и нашей таблицы T1 можно было бы написать:
1 2 3 | DECLARE @v varchar(5) SET @v='DELL' SELECT * FROM T1 WHERE Vendor=@v OPTION (OPTIMIZE FOR (@v='IBM')) |
При таком синтаксисе не важно что запрос, на самом деле, будет выполнен для значения параметра DELL. Более того, не важно и то, что (как нам хорошо известно из предыдущего под-раздела) в случае локальной переменной примененной к ad-hoc запросу без опции RECOMPILE оптимизатор, по идее, должен пользоваться не конкретной, а усредненной статистикой. Все эти факторы для опции OPTIMIZE FOR просто отменяются и мы имеем вот что:
Как видите — совершенно полная аналогия как если бы произошло «вынюхивание» значение параметра IBM, даже под-свойство Parameter Compiled Value вернулось на место. К сожалению, для именно ad-hoc запросов и автоматической параметризации показанный подход имеет разве что академический интерес. Потому что после исполнения, допустим, предыдущего участка кода оптимальный (как мы рассудили) план для фильтрации по колонке Vendor был закэширован и готов к повторному использованию. Да вот только как ту повторность реализовать? Ведь для re-use кэшированного плана должен полностью совпадать текст всего пакета, а значит если мы сразу вслед за предыдущим запросом засылаем на сервер что-то вроде
1 2 3 | DECLARE @v varchar(5) SET @v='IBM' SELECT * FROM T1 WHERE Vendor=@v OPTION (OPTIMIZE FOR (@v='IBM')) |
то никакого re-use не случается. Это другой пакет. Со своим текстом. И уж конечно со своим планом. Вот если бы мы написали
1 | SELECT * FROM T1 WHERE Vendor='DELL' OPTION (OPTIMIZE FOR ('IBM')) |
а сразу вслед за этим
1 | SELECT * FROM T1 WHERE Vendor='IBM' OPTION (OPTIMIZE FOR ('IBM')) |
то благодаря автоматически случившейся параметризации первого запроса все вместе это была бы «тема»! Однако два последних запроса не более чем теоретизирование, потому что опция OPTIMIZE FOR требует указания не только значения параметра «под» которое будет оптимизирован запрос, но и имени этого параметра. Так что эти последние строки кода даже не пройдут проверку синтаксиса. И по факту мы намертво привязываем себя к единственному значению параметра (тому которое мы помещаем в переменную @v), что в свою очередь сводит на нет всю идею параметризации.
А вот при попытке применить описываемую технологию к вариантам ручной параметризации «профит» виден невооруженным глазом. Вот, к примеру, как все может происходить при использовании системной хранимой процедуры sp_executesql:
1 2 3 4 5 6 7 | DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param OPTION (OPTIMIZE FOR (@my_param=''IBM'')) ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'DELL' |
Запрос выполняется со значением параметра DELL, а план его оптимизируется для значения IBM. Вы можете даже смотреть на последнюю иллюстрацию, ибо она абсолютно точно показывает что случится после выполнения данного отрывка кода. Далее мы можем сколько угодно повторно исполнять строку кода EXEC sp_executesql @query,... с любыми значениями параметра @my_param, а использоваться будет именно «план под IBM».
Ручная параметризация через пользовательскую хранимую процедуру так же весьма успешно «покрывается» данной методой. Вот как все красиво происходит:
1 2 3 4 5 6 | CREATE PROC p1 @prm varchar(5) AS BEGIN SELECT * FROM T1 WHERE Vendor=@prm OPTION (OPTIMIZE FOR (@prm='IBM')) END |
И исполняем ее:
1 | EXEC p1 'DELL' |
И снова смотрим на последнюю иллюстрацию, ибо вновь аналогия полная. Далее выполняем
1 | EXEC p1 'IBM' |
(строго говоря значение параметра может быть любым, хоть ABC123) и отрабатывает «план под IBM», взятый, само-собой, из кэша.
Ну и уж полноты картины для, упомянем что SQL Server версии 2008 представил особую разновидность обсуждаемого «хинта», OPTIMIZE FOR UNKNOWN. Для примера перепишем нашу хранимую процедуру:
1 2 3 4 5 6 | CREATE PROC p1 @prm varchar(5) AS BEGIN SELECT * FROM T1 WHERE Vendor=@prm OPTION (OPTIMIZE FOR UNKNOWN) END |
И выполним ее:
EXEC p1 'IBM'Проконтролировав использованный для последней инструкции план
убеждаемся — эффект абсолютно тот же что и в предыдущем подразделе когда мы «прятали» истинное значение параметра за «ширмой» локальной переменной. Для того его, UNKNOWN этот, и вводили, что бы в случае невозможности выделить из таблицы «типичное» значение, мы бы, без всяких «посредников», а напрямую сказали бы оптимизатору: «а давай нам план по усредненной статистике, ну вот не можем мы подобрать типичное значение, все они у нас такие, средние». По сути это «цивилизованный» метод подавления «вынюхивания». И гораздо более короткий и красивый чем декларация переменной (причем формальной), инициализация ее некоторым значением, приравнивание ее к параметру... А так написали UNKNOWN — и всем все понятно (по крайней мере всем регулярно читающим блог sqlCMD.ru 
). Маленький недостаток, напомню — не работает на серверах 2005-й версии и более ранних. А вот OPTIMIZE FOR под конкретное значение работает в любой версии.
Итого: подсказка OPTIMIZE FOR [UNKNOWN] является отличной «контр-параметризационной» технологией, но только если сама параметризация проводится одним из ручных способов.
Отмена кэширования плана и повторная оптимизация каждого исполнения запроса.
Это самый «топорный» способ, зато по универсальности и надежности в достижении результата с ним не сможет конкурировать ни одна альтернатива:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SELECT * FROM T1 WHERE Vendor='IBM' OPTION (RECOMPILE) --- DECLARE @v varchar(5) SET @v='IBM' SELECT * FROM T1 WHERE Vendor=@v OPTION (RECOMPILE) --- DECLARE @query nvarchar(200) DECLARE @param nvarchar(200) SET @query = N' SELECT * FROM T1 WHERE Vendor=@my_param OPTION (RECOMPILE) ' SET @param = N'@my_param varchar(5)' EXEC sp_executesql @query, @param, @my_param = 'IBM' GO --- CREATE PROC p1 @prm varchar(5) WITH RECOMPILE AS BEGIN SELECT * FROM T1 WHERE Vendor=@prm END --- EXEC p1 'IBM' |
Опция RECOMPILE работает всегда, работает везде и просто «обречена на успех». Ведь после исполнения только что показанного скрипта в процедурном кэше не появится ни одного нового плана! А значит ошибиться при повторном исполнении той же самой хранимой процедуры/инструкции и вытащить для нее из кэша суб-оптимальный план невозможно чисто технически. Да, факт отмены самого процесса кэширования готового плана (а RECOMPILE предписывает именно отбросить готовый план после его однократного исполнения) снимает кучу вопросов и, в частности, ультимативно решает проблему «плохой» параметризации. Ибо в данном случае этот последний термин просто теряет смысл, каждое новое исполнение будет с «чистого листа» и каждый план будет оптимизирован под это конкретное и единственное (с точки зрения плана) исполнение процедуры/инструкции. Более того, как мы видели чуть ранее, данная опция позволяет оптимизатору создавать наилучший план даже для таких программных конструкций, в которых, на первый взгляд, это сделать вообще невозможно. Я говорю, разумеется, о «вынюхивании» значения параметра, «обернутого» в локальную переменную. Например план для второго SELECT-а последнего скрипта будет «заточен» именно под IBM, а не просто под среднее значение колонки Vendor.
И только одну головную боль несет с собой эта опция. Правда боль эта, заметим для справедливости, запросто перекрывает всю ту простоту и изящество с которыми обсуждаемая опция одним махом «разрулила» все казавшиеся сложными вопросы. Вы уже знаете источник этой боли — «ресурсоемкая цепочка» требуемая для получения плана готового к исполнению. Она будет «прогоняться» движком сервера для каждой программной конструкции помеченной опцией RECOMPILE снова, и снова, и снова... Что, конечно, большого энтузиазма вызвать у нас с вами не может. Тем не менее, на практике встречаются ситуации (и значительно чаще, чем это можно себе представить), когда описанный и явно неэффективный подход к решению проблемы оказывается именно что оптимальнейшим из вариантов для ускорения суммарного времени исполнения конкретного запроса, неоднократно передаваемого на исполнение движку сервера с различными значениями параметра! Да, как и любая сложная сущность SQL Server полон парадоксов, а вы как думали?
Явный выбор оптимального плана методом «структуры плана» (plan guide).
SQL Server 2005 представил, а SQL Server 2008 развил идею структур плана (plan guide). Это довольно интересная технология, безусловно заслуживающая отдельного рассказа (который значится у автора в списке потенциальных топиков ближайших статей) и самостоятельного, глубокого анализа. Поскольку статья текущая ни разу не про «план-гайды», то у автора и нет намерения пытаться что-то объяснять и иллюстрировать такие объяснения релевантными примерами кода. Всему свое время и место. А поэтому, только для тех читателей, что столкнулись со словосочетанием plan guide впервые, и только в рамках одного абзаца опишем самую базовую идею, чьей реализацией стала указанная технология.
Итак, plan guide дает нам возможность:
- для отдельной инструкции/процедуры указать тот ее вариант плана исполнения, что мы сами находим наиболее оптимальным, вне зависимости что там по этому поводу думает оптимизатор. Код будет исполнятся согласно тому плану что укажем мы и точка;
- для отдельной инструкции/процедуры указать любой хинт запроса, вне зависимости предусматривал ли его оригинальный программист данного кода или нет;
А что самое замечательное в указанной технологии так это то, что для реализации обоих пунктов данного списка доступ к T-SQL коду инструкции/процедуры нам не нужен. То есть совсем. Что делает указанную технологию просто «must have» инструментом в ситуации, когда мы обслуживаем базу данных принимающую запросы от некоторого клиента купленного нашим предприятием у третьей стороны. Разумеется, правка кода такого клиента не только не предполагается в виду полного отсутствия у нас его исходников, но и, чаще всего, будет прямым нарушением лицензионного соглашения. Но нам очень-очень сильно надо, что бы запрос формируемый таким клиентом был оптимизирован под значение параметра IBM. А он, как на зло, оптимизируется под DELL. Вот тогда мы «расчехляем» этот самый plan guide, и...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE PROC p1 @prm varchar(5) AS BEGIN SELECT * FROM T1 WHERE Vendor=@prm END --- EXEC sp_create_plan_guide @name = N'planGD1', @stmt = N'SELECT * FROM T1 WHERE Vendor=@prm', @type = N'OBJECT', @module_or_batch = N'dbo.p1', @params = NULL, @hints = N'OPTION (OPTIMIZE FOR (@prm=''IBM''))' --- EXEC p1 'DELL' |
Разумеется, все самое интересное происходит в момент вызова системной хранимой процедуры sp_create_plan_guide. Однако ни ее саму, ни назначение ее опций мы, согласно нашим договоренностям, в рамках данной статьи не рассматриваем. А просто констатируем свершившийся факт: после отработки указанной процедуры в базе появился еще один объект типа plan guide и по имени planGD1. А этот последний говорит движку сервера, что если в рамках пользовательской процедуры dbo.p1 (параметр @module_or_batch) встречается указанный SELECT (параметр @stmt), то этот последний безусловно получает «в нагрузку» хинт из параметра @hints. По итогу, выполнение процедуры p1 приводит вот к таким результатам:
То есть эффект абсолютно тот же, как если бы мы поправили «руку» исходного программиста в тот момент когда он набирал код процедуры dbo.p1. Однако, как легко заметить, сам этот код не изменился ни на один бит. Что прекрасно само по себе и снимает все «неудобные» вопросы связанные с лицензиями на софт-продукты. Так же заметим, что установить факт «помощи» со стороны план-гайда при выполнении той или иной инструкции (либо, соответственно, установить факт отсутствия такой помощи) очень просто: достаточно снова посмотреть на список свойств оператора SELECT актуального плана исполнения (впрочем и оценочный план будет в этом моменте ничем не хуже): если в указанном списке фигурируют два свойства, а именно PlanGuideDB / PlanGuideName, то помощь оказывалась. Значения этой пары свойств укажут какой именно из «план-гайдов» выступил в роли «саппорта». Отсутствие той же пары свойств говорит нам что был сгенерирован самый обычный план и оптимизатор ни на какие такие «гайды» внимания не обращал.
Все работает и в случае одной из двух возможных автоматических параметризаций, но приходится быть хитрее. В таком сценарии придется создать сразу два «план-гайда», один типа TEMPLATE и еще один типа SQL:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | DECLARE @stmt nvarchar(max); DECLARE @params nvarchar(max); EXEC sp_get_query_template N'SELECT * FROM T1 WHERE Vendor=''IBM''', @stmt OUTPUT, @params OUTPUT; EXEC sp_create_plan_guide @name = N'planGD2templ', @stmt = @stmt, @type = N'TEMPLATE', @module_or_batch = NULL, @params = @params, @hints = N'OPTION(PARAMETERIZATION FORCED)' EXEC sp_create_plan_guide @name = N'planGD2sql', @stmt = @stmt, @type = N'SQL', @module_or_batch = NULL, @params = @params, @hints = N'OPTION (RECOMPILE)' |
Начиная с этого момента, все запросы вида
1 | SELECT * FROM T1 WHERE Vendor='...' |
(конкретное значение фильтра не играет роли) будут использовать тот хинт, что вы укажете в параметре @hints «план-гайда» planGD2sql. Для примера я выбрал опцию RECOMPILE, но доступна любая из известных движку сервера. Разумеется изменять T-SQL код самих запросов вам вновь нужды нет.
По итогу мы имеем в нашем распоряжении еще один универсальный инструмент для борьбы с параметризацией, причем в ряде сценариев данный инструмент даже не будет иметь себе альтернатив!
Заключение.
Итак, мы завершили разбор «темной стороны» параметризации. К текущей точке материала у вас должно сложится четкое понимание тех ситуаций когда параметризация будет вашим «другом», а когда — «врагом». Вы так же должны знать, что первому случаю следует всемерно содействовать и создавать режим наибольшего благоприятствования, о чем мы и говорили в первой статье цикла. Ну а со вторым вариантом нужно бороться и всякие такие «нездоровые вывихи» оптимизатора подавлять, о чем, собственно, шла речь в статье текущей. Вы узнали о нескольких методиках такого «подавления», причем одна из них даже не требует доступа к исходному коду процедуры/инструкции! Дело за малым: применить полученные знания на практике... 
Автор благодарит всех своих читателей за их внимание и участие в жизни блога и желает всем оптимальных запросов и их эффективной параметризации. До будущих встреч! 
- Другие части статьи:
- 1
- 2