Добрый день, уважаемые читатели блога sqlCMD.ru. Читая ваши вопросы и отзывы, приходящие в изрядном количестве после каждой новой публикации, автор получает богатый материал по поиску тех SQL-вопросов, которые вполне заслуживают отдельной статьи, если не цикла. Если просьба "описать тему X" повторяется регулярно, и если по ней же систематически задают вопросы слушатели SQL-курсов читаемых тем же автором, сомнений нет - "тема животрепещет", как выражается один из заезженных журналистских штампов. Одну из таких "горячих", с точки зрения автора, тем мы с вами сегодня и разберем. Ну, то есть как - разберем, но ровно наполовину. А все потому, что вопрос параметризации запросов (а речь, как вы уже догадались, пойдет именно о ней) зело обширен, многогранен и полон внутренних противоречий. Сложная в общем тема, чего уж там скрывать. А потому, для лучшего и постепенного восприятия материала будет разумно выбрать формат именно цикла статей, и в начальной стадии (то есть в рамках статьи текущей) рассмотреть "светлые стороны" указанного непростого процесса. Стороны же отрицательные, как и борьба с оными, остаются для будущих статей цикла.
Итак, план нашей учебы будет таков:
- понять общую идею параметризации и ответить на вопрос "а профит в чем?";
- научиться отделять зерна от плевел, а так же запросы прошедшие параметризацию от запросов счастливо(??) избежавших таковую;
- понять, какие вообще есть способы для практического осуществления параметризации запросов и можно ли часть этой нагрузки (или всю ее) переложить на сам сервер;
- обсудить реальные примеры из практики автора и его коллег отвечающие на вопрос: а насколько велик может быть тот самый профит из первого пункта? Может ли параметризация принести реальные, ощутимые плоды, очевидные как для конечных пользователей решения на базе SQL Server (и в первую очередь для них, разумеется), так и для DBA ответственного за ту же систему?
Приступим.
Общая идея параметризации.
Как известно, все в этом мире вообще и в IT-индустрии в частности начинается с идеи. Начнем наше повествование с нее же - как вообще зародилась мысль что запросы можно параметризировать и кому/зачем это было надо? Для понимания этой самой идеи давайте смоделируем несложную табличку:
|
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 |
USE master go CREATE DATABASE [~DB~] ON PRIMARY ( NAME = '~DB~_Data', FILENAME = 'c:\sqlCMD.ru\~DB~.mdf', SIZE = 15 MB, MAXSIZE = 15 MB ) LOG ON ( NAME = '~DB~_Log', FILENAME = 'c:\sqlCMD.ru\~DB~.ldf', SIZE = 15 MB, MAXSIZE = 15 MB ) GO ALTER DATABASE [~DB~] SET PARAMETERIZATION SIMPLE GO USE [~DB~] GO create table T1 (ColID int identity, Col2 smallint, Col3 nchar(2), CONSTRAINT PK_T1 PRIMARY KEY (ColID)) go insert T1 VALUES (5555, 'ab') insert T1 VALUES (7777, 'cd') insert T1 VALUES (9999, 'ef') create nonclustered index iT1 on T1 (Col3) |
Тут, надо полагать, никаких комментариев не требуется: табличка из трех строк и трех же колонок, по первой из которых организован кластерный и уникальный индекс, а по последней не-кластерный и не-уникальный. Вопрос может вызвать разве что строка устанавливающая опцию PARAMETERIZATION на уровне базы данных, но как раз эту опцию мы, в числе прочего, будем подробно разбирать по ходу развития нашего сюжета. На текущий момент достаточно знать, что возможных значений у этой опции ровно две -SIMPLE и FORCED. Или, в переводе, параметризация простая и она же принудительная. Намного чаще (автор полагает, что никак не меньше 90% всех рабочих систем) эта опция находится в значении SIMPLE, что вообще является значением по умолчанию для нее. Так что если вы не меняли обсуждаемую опцию у системной базы model то, строго говоря, эта строка скрипта является лишней - наша тестовая база ~DB~ и без того будет в режиме простой параметризации, но для надежности все-таки оставьте ее.
Вот с этой табличкой мы и будем ставить различные эксперименты первый из которых имеет вид:
|
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 |
USE [~DB~] go DBCC FREEPROCCACHE GO SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ef' go SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='zz' go SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go SELECT cp.objtype, cp.usecounts, cp.size_in_bytes, cp.plan_handle, st.text, 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%' GO |
А вот этот код уже заслуживает пары комментариев. Начинаем мы с того, что командой DBCC полностью очищаем кэш планов (plan cache). Для наших опытов это очень хорошо, ибо исследуемая нами параметризация запросов имеет прямое отношение к кэшированию последних, и мы хотим наблюдать за данным процессом каждый раз с "чистого листа". Несложно догадаться, что идея запустить ту же команду на промышленной системе с которой работают реальные пользователи будет "не очень", мягко говоря. Но ведь у вас (как и у автора) для "полевых" испытаний и тестов предусмотрены не менее тестовые же стенды, не так ли? Вот на этих стендах данная команда очень даже уместна.
Следующие пять SELECT-ов обозначают некоторую нагрузку на тестовую таблицу T1 и, надо думать, ведут к генерации некоего числа планов, которые непременно будут "складированы" в том участке RAM компьютера, что и называется кэш планов. Несложно заметить, что у части из этих SELECT-ов значение литерала с которым сопоставляется третья колонка таблицы совпадает, а у некоторых - отличается. Предусмотрен и такой литерал (zz), что вернет нам пустой резалт-сет. Если вы полагаете, что уже уверенно владеете вопросом параметризации попробуйте предсказать - сколько из этих пяти запросов получится различных планов? Сравнить свой ответ с правильным вы сможете через пару абзацев.
Наконец, завершающий SELECT обсуждаемого скрипта является центральным в исследовании параметризации в частности да и вообще всего, что касается кэширования планов. Наша "рабочая лошадка" - динамическое административное представлениеsys.dm_exec_cached_plans. Это последнее содержит ровно по одной строке для каждого плана попавшего в кэш и "кушающего" там некоторый объем RAM. Мы хотим видеть только те из этих строк, что имеют отношение к нашей тест-таблице T1, а поэтому отбрасываем все запросы не имеющие в своем тексте ссылок на первую колонку нашей таблицы. Так же мы отбрасываем план представляющий сам финальный SELECT (вы же не думаете, что выборка из sys.dm_exec_cached_plans происходит "как-то вот эдак", вообще без всяких планов?). Если вам кажется, что фильтр LIKE '%ColID%' уже отсечет все лишнее и последнее AND-условие является избыточным, то еще раз внимательно прочтите текст запроса последнего SELECT-а - а не упоминается ли и в нем тоже колонка ColID? 
Хорошо, со строками что вернет нам завершающий SELECT мы разобрались: одна строка - один закэшированный план имеющий какое-либо отношение к таблице T1. Теперь давайте по колонкам того же SELECT-а:
- objtype - тип того объекта, для которого был сгенерирован данный план. Всего в этой колонке может появиться порядка десяти различных типов, но нам, для понимания общей идеи параметризации, следует сосредоточится всего на двух:
- Adhoc - это значение обсуждаемой колонки говорит нам, что план был сгенерирован в результате прихода на сервер нерегламентированного (иначе - произвольного) запроса. В контексте обсуждаемой проблемы лучше представить себе это так: клиент может послать на сервер "сырую" команду (SELECT, UPDATE и т.д.), а может вызвать готовую процедуру на сервере. Так вот значение Adhoc колонки objtype говорит нам, что имел место первый случай, но никак не второй. Скажем все наши пять "нагрузочных" SELECT-ов обращающихся к таблице T1 относятся как раз к этому типу - произвольные запросы;
- Prepared - это значение говорит о том, что план был подготовлен после того, как с текстом исходного запроса случилась та самая процедура вокруг которой весь наш сегодняшний переполох, то есть процедура параметризации запроса. Если вы сложив в уме предыдущий пункт списка и этот делаете заключение "если план помечен как Adhoc, то он не был параметризирован, все просто!" - не спешите. Дальнейшие наши объяснения в который уже раз докажут, что SQL Server можно изучать бесконечно, а дьявол, как ему и положено, скрывается в нюансах.
- usecounts - счетчик использования данного плана. Когда запрос приходит серверу впервые для него генерируется план, заносится в кэш, выполняется, а в обсуждаемое нами в текущую минуту представление sys.dm_exec_cached_plans заносится новая строка. Колонка usecounts этой новой строки имеет значение 1 - ведь свежий план только что был выполнен один раз, не так ли? Если в дальнейшем серверу поступает "точно такой же" или "похожий" запрос (дальнейший материал статьи показывает разницу в этих двух терминах), то план для него не генерируется, а берется уже имеющийся, из кэша. Соответственно, и новая строка в sys.dm_exec_cached_plans не добавляется, но зато значение колонки usecounts той строки чей план был использован повторно увеличивается на 1;
- size_in_bytes - сколько данный план требует байт оперативной памяти для своего хранения;
- plan_handle - указатель на данный план в памяти, его ID. Благодаря этому значению мы можем попросить вспомогательную функциюsys.dm_exec_sql_text показать нам точный текст запроса, а не менее вспомогательная функция sys.dm_exec_query_plan возвращает нам полный текст плана исполнения (не запроса, а его плана!) в XML-формате;
- text - текст запроса для которого был кэширован данный план, см. колонку plan_handle;
- query_plan - сам кэшированный план, см. колонку plan_handle. В панели результатов Management Studio представлен в виде ссылки, по щелчку на которой открывается отдельная вкладка редактора содержащая описание плана в XML-формате.
Экономя бумажно/экранное пространство, и с этой целью не показывая каждый раз в отрывках кода этот довольно многословный SELECT сообщающий нам о состоянии кэша, автор будет просто ссылаться на него как на "запрос кэша", подразумевая именно показанный и только что подробно разобранный SELECT со всеми его колонками, фильтрами по колонке ColID и т.д.
Выполнение всего последнего скрипта со всеми его командами дает нам такой результат (показан лишь резалт-сет от "запроса кэша", колонки plan_handle и query_plan опущены):
Adhoc 1 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='zz'
Adhoc 3 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab'
Adhoc 1 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ef'
Итак, если на вопрос поставленный чуть выше "сколько из этих пяти запросов получится планов" вы ответили "три" то были совершенно правы именно три независимых плана находятся в данный момент в кэше. То, что планы именно обособлены показывают различные значения их идентификаторов (проанализируйте опущенную в показе последнего резалт-сета колонку plan_handle). Два из трех планов были использованы по разу, один - три раза. То, что один из планов не возвращает ни одной строки на факте его кэширования не сказывается совершенно - пустой результат это результат ничем не хуже (а по многим факторам намного лучше), чем результат из миллиона строк. Тексты запросов (колонка text) всех трех планов имеют совпадение с текстом оригинальных запросов буквально буква-в-букву. Каждый из планов занимает в памяти 24.5 килобайт. Если вам кажется, что для столь примитивных запросов это довольно-таки внушительный объем - так и есть. Держать в памяти планы то еще удовольствие, как "сжигатели RAM" это просто идеальные объекты. Разумеется, планы запросов состоящие не из одной строки (как у нас), а вольготно разместившие свой код на паре экранов потребуют в разы большего пространства памяти. И, как вы уже поняли, любое изменение строкового литерала сравниваемого с колонкой Col3 будет приводить к генерации нового плана, его кэшированию и... прощайте очередные 24.5Kb "оперативки".
Не складывается ли у вас неодолимого ощущения что описанный процесс "нарезки" памяти 24-х килобайтовыми кусками можно несколько оптимизировать? Что если так: кэшируем один план, без конкретного значения литерала с которым следует сопоставить третью колонку, а затем вызываем постоянно только его, указывая каждый раз нужный нам литерал? А что - это идея!
Параметризацию можно проводить вручную, поясняя SQL Server "вот тут у нас параметр, а вот тут - нет", а можно пустить дело на самотек и надеяться, что оптимизатор запросов сервера сам разберется с вопросом "кто тут что". Этот второй подход называют автоматической параметризацией. Последующий материал статьи рассматривает и ручной, и автоматический варианты (а их еще, по каждому направлению, больше одного под-вида), но сейчас мы быстро посмотрим автоматическую параметризацию в действии. Для этого выключим мешающий ей проявить себя индекс:
|
1 2 3 |
USE [~DB~] GO ALTER INDEX iT1 ON T1 DISABLE |
Чем "провинился" ни в чем не виноватый индекс iT1 и почему автоматическая параметризация его "невзлюбила" мы выясним в соответствующем разделе далее, пока просто примем этот факт как данность. С выключенным индексом выполним тот же самый скрипт, полностью и с самого начала. На этот раз результаты будут такими:
|
1 2 3 4 5 |
objtype usecounts size_in_bytes text Adhoc 1 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='zz' Adhoc 3 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' Adhoc 1 24576 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ef' Prepared 3 81920 (@1 varchar(8000))SELECT [ColID],[Col2],[Col3] FROM [T1] WHERE [Col3]=@1 |
На первый взгляд никаких кардинальных изменений не произошло, как было три плана, так они и остались, только еще зачем-то четвертый добавили... 
А между тем изменения носят самый драматический характер, но для их понимания нужно обратиться к опущенной в двух последних резалт-сетах колонке query_plan. Если мы взяв для определенности план построенный для литерала abщелкнем по его плану-ссылке в первом случае (когда индекс был включен, а авто-параметризация еще не работала) то увидим вот что:
То есть вполне себе обычный, полноценный execution plan. А вот что мы видим после такого же щелчка во втором случае, после отключения индекса:
То есть ядро всей функциональности плана, а именно сканирование кластерного индекса - как "корова языком". Остался формальный SELECT "вываливающий" клиенту готовый резалт-сет, вот только непонятно - а кто его готовить будет? Все становится гораздо яснее, если мы учтем, что те три строки последнего резалт-сета что помечены как Adhoc не являются планами. По крайней мере, они не являются полноценными планами, это уж определенно. А являются они, на самом деле, лишь оболочками реальных планов (так называемые shell plans), или, если хотите, ссылками на план реальный. То, что такая связь между shell-планом и планом реальным существует мы убедимся в самом ближайшем будущем, а пока давайте попробуем представить, как "рассуждал" оптимизатор запросов выполняя наш экспериментальный скрипт в случае с отключенным индексом:
- сначала скрипт, как мы уже знаем, очищает кэш планов, и тут к оптимизатору никаких вопросов нет;
- далее следует первый из пяти аналогичных SELECT-ов с литералом поиска ef. Оптимизатор пытается найти аналогичный запрос с уже готовым планом, ничего не находит (в пустом-то кэше), но замечает, что предложенный запрос очень хорошо подходит для авто-параметризации. Тогда он оставляет оригинальный запрос как shell-план, заменяет в нем литерал на параметр (это, напомню, и называется параметризацией как таковой), строит план для этого (то есть параметризированного) запроса, и устанавливает связь между обоими планами. После чего исполняет реальный план, а в счетчик использований (колонка usecounts последнего резалт-сета) ставит единички обоим планам. Заслуживает внимания тот факт, что в параметризированной версии запроса (это, как вы уже поняли, последняя строка последнего резалт-сета, помеченная типом объекта Prepared) литерал заменен на параметр, причем в данном случае имя этого параметра очень простое - @1. Однако и сам текст запроса не полностью совпадает с оригинальным: запрос начинается с круглых скобок в которых перечислены по именам все "участвующие в пьесе" параметры. Кроме того, указан тип каждого из них. А еще сверх того, проведена так называемая нормализация текста запроса, то есть выкинуты все несущественные пробелы/переводы строк, названия всех объектов взяты в квадратные скобки и т.д. Может показаться, что указанная нормализация текста не шибко и важна, однако как покажут наши ближайшие расследования мелочей в такой сложной штуке как SQL Server просто не бывает;
- затем у нас в скрипте идет второй SELECT, с литералом ab. Сначала оптимизатор пытается обнаружить абсолютно точный текст запроса, что у него, разумеется, не получается. В кэше имеется план для запроса с текстом очень близким, но не совпадающим. Тогда оптимизатор проводит точно такую же параметризацию и этого второго запроса и спрашивает сам себя - "ну может у нас есть в кэше план этой версии запроса, параметризированной"? Да, вот такой план находится, и тогда оригинальный запрос с ab вновь сохраняется как shell-план и устанавливается связь между ним и реальным (и уже существующим!) планом. Реальную работу выполняет, понятное дело, последний. Для нового shell-плана значение колонки usecounts устанавливается в 1, а та же колонка реального плана увеличивает свое значение на 1 и достигает двойки;
- с третьим SELECT-ом (литерал zz) все повторяется как это было с предыдущим литералом: новый shell-план, связь между ним и параметризированным планом, 1 в usecounts для нового shell-плана, плюс 1 для той же колонки реального плана. Итого, согласно показаниям колонки usecounts к текущему моменту три исполненных SELECT-а по разу использовали свои shell-планы, а реальный план уже был использован трижды;
- два завершающих SELECT-а имеют свою специфику: они не только полностью идентичны сами по себе, но они и полностью идентичны запросу уже выполненному ранее! В этом случае оптимизатор сходу обнаруживает соответствующий shell-план и ему не нужно заниматься параметризацией запроса и не нужно проводить в кэше второй поиск для этого параметризированного варианта. Обе указанные процедуры не являются такими уж "мега-задачами", но они определенно требуют к себе внимания оптимизатора, его времени, ну и, опять же, ресурсов сервера каких-никаких... В общем, прикинув так и эдак, создатели SQL Server сделали однозначный выбор - "дешевле" держать в памяти эти странноватые "недо-планы" и благодаря им изредка (а может и довольно часто - это уж как получится в каждом конкретном случае) избегать двух показанных задач. Так вот, обнаружив полное совпадение с shell-планом и зная, что у того имеется "линк" к плану реальному, оптимизатор прекращает свою деятельность прямо в этой точке. Ну а что, нужно просто "пихнуть" shell-план и сказать тому - "выполнись-ка еще разок"!
- ситуация с счетчиком usecounts, сложившаяся после выполнения двух заключительных SELECT-ов, заслуживает отдельного разбирательства. Как вы можете видеть из последнего резалт-сета, два финальных запроса увеличили обсуждаемый счетчик у shell-плана, но не сделали того же самого с счетчиком плана реального. Дело тут в том, что счетчик последнего считает не исполнения, а число shell-планов содержащих ссылку на него. И если вас интересует реальное число использований параметризированного плана из кэша вы должны суммировать счетчики usecounts всех shell-планов на него ссылающихся (найти их в представленииsys.dm_exec_cached_plans проще пареной репы, как вы вскоре убедитесь). В нашем случае сложив 1+3+1 мы получим совершенно верный ответ, пять раз наш единственный реальный план отправлялся на исполнение. Да, вы правы - все непросто, а местами и нелогично.
Но вот так вот оно все и работает...
Думаю, вы уже начали улавливать те преимущества, что можно извлечь из запросов успешно прошедшего процедуру параметризации. Однако такие "успешные" запросы предлагают нам, помимо "пряников" явных, и еще один скрытый плюс. Давайте рассмотрим вот такие две команды, а точнее два пакета команд по одной в каждой:
|
1 2 3 4 |
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go select ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go |
Разница, как легко видеть, минимальнейшая и сводится к регистру букв ключевого слова. Как знают читатели статьи Регистрозависим ли язык T-SQL? с точки зрения "чистого синтаксиса" мы имеем полное право писать SELECT, select и даже SeLeCt - это все едино. Вот только "едино" все это с точки зрения парсера запросов. Оптимизатор же запросов имеет на сей счет отдельное мнение.
Посмотрим на кэширование двух приведенных команд (а точнее их планов) при успешной и неуспешной их параметризации. Сначала включим индекс iT1
|
1 |
ALTER INDEX iT1 ON T1 REBUILD |
и, тем самым, сделаем параметризацию невозможной. В этом случае наш "запрос кэша" сообщает что:
Adhoc 1 0x06000F00EF1BA90340416988010000000000000000000000 select ColID, Col2, Col3 FROM T1 WHERE Col3='ab'
Adhoc 1 0x06000F008D91381E4001D887010000000000000000000000 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab'
Видите? Это два разных запроса. И для них сгенерированы два отдельных (см. колонку plan_handle), хоть и абсолютно идентичных, как подсказывает здравый смысл, плана. Теперь снова выключим индекс iT1,
|
1 |
ALTER INDEX iT1 ON T1 DISABLE |
дав таким образом "добро" на параметризацию тех же запросов, и повторим эксперимент:
Adhoc 1 0x06000F00EF1BA90340814A88010000000000000000000000 select ColID, Col2, Col3 FROM T1 WHERE Col3='ab'
Adhoc 1 0x06000F008D91381E4061D682010000000000000000000000 SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab'
Prepared 2 0x06000F00A9D9A71140213F84010000000000000000000000 (@1 varchar(8000))SELECT ... WHERE [Col3]=@1
А вот теперь это два shell-плана и один план полноценный. Счетчик usecounts сомнений не оставляет - оба запроса есть лишь две ссылки на один и тот же план. Сами как думаете, что проще "родить": еще один shell к имеющемуся плану уже готовому к исполнению или еще один полноценный план?
Однако - не впадайте в эйфорию. Упомянутая чуть выше нормализация текста запроса (а именно она и дает нам обсуждаемый в настоящий момент скрытый плюс) много лучше чем ничего, но она отнюдь не всесильна. Стоит поменять две тестовых команды на
|
1 2 3 4 |
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' go SELECT ColID, Col2, Col3 FROM t1 WHERE Col3='ab' go |
то есть изменить регистр не ключевого слова, а объекта (таблицы T1 в данном случае) и даже включенная параметризация помочь не в силах:
Adhoc 1 0x06000F007316270340818785010000000000000000000000 SELECT ... FROM t1 WHERE Col3='ab'
Prepared 1 0x06000F0063D416354041AA85010000000000000000000000 (@1 varchar(8000))SELECT ... FROM [t1] WHERE [Col3]=@1
Adhoc 1 0x06000F008D91381E40417A85010000000000000000000000 SELECT ... FROM T1 WHERE Col3='ab'
Prepared 1 0x06000F00A9D9A7114061A187010000000000000000000000 (@1 varchar(8000))SELECT ... FROM [T1] WHERE [Col3]=@1
Да, вы все поняли правильно: две ссылки, но каждая для своего полноценного плана. А это все потому, что SELECT, как его не "перекручивай" селектом и останется. А вот T1 и t1, как это снова известно читателям статьи упомянутой чуть выше, могут означать как один и тот же объект, так и два разных.
Стало быть, запрос успешно прошедший параметризацию предохраняет вас (ну не вас лично, а ресурсы того сервера за который вы отвечаете) от падения производительности системы за счет снятия с нее "дурацкой" нагрузки вроде "выштамповки" двух (и хорошо если только двух, а не двух сотен) абсолютно идентичных планов для двух совершенно функционально идентичных запросов. Да, предохранение это весьма ограничено по своим масштабам и подчас "лажает", но от пары-тройки вовремя не прожатых SQL-программистом SHIFT-ов все-таки убережет. Конечно, рассматривайте этот плюс как приятное, но совершенно не главное преимущество параметризации. Как указывал автор в заключении ко все той же статье Регистрозависим ли язык T-SQL? стабильность в стилистике (именно стилистике) кода по прежнему является лучшей защитой от множества напастей и от описанной только что в том числе.
Итак, мы описали (причем весьма кратко, уж поверьте 
) общую идею стоящую за процессом параметризации запросов и те выгоды на которые мы можем рассчитывать от ее реализации. Можно было бы к вопросам той самой реализации и переходить, тем более что вариантов тут не один, и не два и даже краткое их описание потребует от автора усидчивости. Однако, прежде давайте выясним вопрос куда как более простой: а как нам вообще можно со стопроцентной уверенностью сказать - был данный запрос параметризирован оптимизатором или нет? Если вы полагаете что это "и так понятно", то вспомните, что "дьявол - в нюансах" и обязательно загляните в следующий раздел.
Установление факта параметризации запроса.
Как и любая большая тема вопрос параметризации тоже полон мифов, преданий и неправильных советов. Дабы предостеречь читателя от таких вредных советов и показать ему, что "не все йогурты одинаково полезны", рассмотрим сначала способы неверные, но широко разошедшиеся по сетевой и бумажной SQL-литературе.
Способ 1, ошибочный.
Данный способ советует нам сделать следующее:
- "заказать" в Management Studio актуальный план выполнения (Ctrl+M) и выполнить целевой запрос;
- в актуальном плане выбрать итоговый SELECT (крайний левый оператор) и нажать F4, то есть вызвать окно свойств этого оператора;
- если в открывшемся окне мы наблюдаем присутствие свойства Parameter List то запрос прошел параметризацию.
Иными словами мы пытаемся обнаружить у итогового SELECT-а вот что:
На первый взгляд все законно - параметр, имя его, значение... Вот только сделан снимок экрана для плана запроса
|
1 |
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' |
в тот момент, когда индекс на таблице T1 включен. А значит никакой параметризации и быть не может, в принципе. Одним словом - мимо.
Способ 2, снова ошибочный.
Теперь нам советуют в том же окне свойств оператора SELECT смотреть на то из них, что имеет название Statement. Точнее смотреть следует не на факт его присутствия в окне (он там будет всегда), а на значение этого свойства. Рассматриваемый способ говорит нам о том, что если в значении этого свойства литералы заменены на параметры - параметризация уже имело место. Можно и чуть упростить этот способ: значение того же свойства выводится в заголовке панели отображающей сам план:
В данном случае доказательство представляется вообще "убойным" - литерал явно заменен параметром! Не знаю удивитесь вы или нет, но и этот снимок экрана сделан для того же запроса и тех же условий включенного индекса. То есть - снова мимо, без сомнений. На ваш законный вопрос "а зачем нам так изощренно голову морочат, подсовывая свойства и операторы доказывающие то, чего не было?" автор может дать такое объяснение.
Как обычно, в жизни все несколько сложнее чем это описывается даже в самых сложных и подробных статьях вроде той, что вы читаете в данную минуту. Так и тут. Текст запроса не сразу поступает на обработку процессору запросов (query processor, QP).
Так вот, сначала текст запроса пред-обрабатывается иным компонентом, а именно компонентом языковой обработки и исполнения (language processing and execution, LPE). И за формальную (начальную) параметризацию запроса отвечает именно LPE. То есть это именно его обязанности найти такие "точки" запроса, где литералы могут быть по, хотя бы, формальным признакам заменены на параметры. После такой замены LPE "обращается" к QP и "говорит" последнему: вот смотри, оригинальный запрос был таким, однако имеется возможность переписать его вот эдак, с параметрами - выбирай! После чего QP возможно (только возможно!) утвердит параметризированную версию запроса, если посчитает такую замену безопасной (подробнее об этом в дальнейших разделах статьи). А возможно и скажет параметризации "нет" и примется строить план для оригинального текста, с литералами. И "фишка" в том, что оба просмотренных нами совета смотрят на деятельность компонента LPE! Который честно говорит: запрос имеет потенциал к параметризации. Да иметь-то он его имеет, а будет ли тот потенциал реализован - вот наше главное беспокойство. И как раз о реализации потенциала оба способа нам не говорят ни-че-го.
Способ 3, верный.
В этом способе нам снова нужен актуальный план, а в этом плане нам нужен тот его оператор для которого литерал, чью возможную замену на параметр мы хотим исследовать, является предикатом. В случае нашего тест-запроса выбор невелик, у нас ровно один оператор выполняющий реальную работу - Clustered Index Scan. Обнаружив таковой нужно "завесить" над ним мышиный курсор, а в появившейся подсказке прочесть раздел Predicate. Вариант того же самого: открыть окно свойств того же оператора и посмотреть значение одноименного свойства. Вот как это выглядит в нашем случае, слева подсказка когда индекс включен (а параметризация наоборот), справа - когда он выключен:
Вот теперь все "честно": справа параметризация была, слева ничего подобного не было.
Способ 4, верный и более удобный.
Мы уже знаем из вводной, "идейной" части, что при случившейся параметризации (более технически корректно - случившейся авто-параметризации; у параметризации ручной свои правила) мы получаем не один, а два плана: "недо-план" и "нормальный" план. Однако вне зависимости от "нормальности" каждый объект кэша должен иметь свой идентификатор и в качестве такового выступает его ID, или "хэндл". Помните про колонку plan_handle нашего "запроса кэша"? Это он и есть. Такой хэндл, повторю, будет у абсолютно любого плана в кэше без исключений. НО! Наш "недо-план", очевидно и судя по всему, должен иметь в своем тексте ссылку на хэндл нормального, параметризированного плана, логично? Иначе как и кому этот shell-план реальную работу "отфутболит"? А если такой ссылки нет? А вот тогда никакой это не shell-план, а план полноценный и параметризации не предполагающий. Одним словом:
- выполняем наш "запрос кэша", находим в нем строчку того запроса, что, как мы подозреваем, мог быть параметризирован (а мог и нет), и в колонке query_plan этой строки щелкаем по ссылке для открытия XML-текста плана. Если студия откроет план в графическом виде, переключаемся на XML-просмотр;
- после этого достаточно беглого взгляда на план: если весь его текст умещается в три строки и, обыкновенно, состоит из единственного тега StmtSimple - то это на 99% shell-план не могущий не иметь сопутствующей ему полноценной и, самое главное,параметризированной версии плана. Что бы убрать все сомнения делаем поиск по строке ParameterizedPlanHandle. Если такой атрибут найден то все сомнения сняты - это и есть ссылка на хэндл параметризированной версии плана. Если же такой атрибут не найден, то либо LPE вообще не увидел в запросе "точек параметризации", либо увидел, но QP наложил на них вето.
Опять же, опробуем совет на практике. Если открыть из резалт-сета "запроса кэша" план для инструкции
|
1 |
SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' |
в тот момент когда индекс включен, то мы увидим самый обычный план, без всякого намека на XML-атрибуты ParameterizedPlanHandle. Если сделать тоже самое для отключенного индекса, то мы увидим:
<Statements>
<StmtSimple StatementText="SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ab' ... ParameterizedPlanHandle="0x06000F00A9D9A71140E1A385010000000000000000000000" ... />
</Statements>
...
Что характерно - приведен практически весь план, целиком! Убраны пара строк с родительскими тегами и пяток атрибутов. Да, shell-планы они такие...
Способ 5, верный и еще более удобный.
Мы можем усовершенствовать наш "запрос кэша" таким образом, что бы тут же подтверждать/отвергать факт параметризации для каждого запроса в кэше, без поиска по XML-тексту. Фактически, это ничто иное, как автоматизация предыдущего способа и программное "вытягивание" значений атрибута ParameterizedPlanHandle. Вот наша усовершенствованная версия запроса:
|
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%' |
Как видите, к имеющимся и подробно нами рассмотренными ранее шести колонкам добавляются еще две:
- parameterized_plan_handle - все просто: если для данного плана (то есть строки резалт-сета обсуждаемого "запроса кэша") значение этой колонки равно NULL - этот план является "сам по себе", никакой параметризации не было. Иначе параметризация была, строка резалт-сета представляет shell-план, а значение рассматриваемой колонки есть хэндл (то есть ссылка) на параметризированную версию плана;
- parameterized_text - "бонус-колонка". Отображает "переписанный" текст запроса. То есть тот его вариант, где литералы заменены на параметры.
Давайте вновь запустим наш тестовый скрипт но на этот раз с усовершенствованным "запросом кэша". В случае включенного индекса/выключенной параметризации мы наблюдаем (избранные колонки резалт-сета):
SELECT...WHERE Col3='zz' NULL (@1 varchar(8000))SELECT...WHERE [Col3]=@1
SELECT...WHERE Col3='ab' NULL (@1 varchar(8000))SELECT...WHERE [Col3]=@1
SELECT...WHERE Col3='ef' NULL (@1 varchar(8000))SELECT...WHERE [Col3]=@1
Обратите внимание, что наличие некоего текста в колонке parameterized_text не говорит нам ни о чем (разве что о потенциале данного запроса к параметризации), значение NULL в колонке parameterized_plan_handle говорит нам обо всем. Помните о "разделении работ" между LPE и QP!
Включив же параметризацию через отключение индекса мы увидим:
SELECT...WHERE Col3='zz' 0x06000F00A9D9A7114041D983010000000000000000000000 (@1 varchar(8000))SELECT...WHERE [Col3]=@1
SELECT...WHERE Col3='ab' 0x06000F00A9D9A7114041D983010000000000000000000000 (@1 varchar(8000))SELECT...WHERE [Col3]=@1
SELECT...WHERE Col3='ef' 0x06000F00A9D9A7114041D983010000000000000000000000 (@1 varchar(8000))SELECT...WHERE [Col3]=@1
(@1 varchar(8000))SELECT...WHERE [Col3]=@1 NULL NULL
Вполне логично, что значение колонки parameterized_text для shell-планов полностью совпадает с колонкой text соответствующего им "полноценного" плана. Небезынтересно будет взглянуть и на другой набор избранных колонок последнего резалт-сета:
0x06000F007C6BB70840019C85010000000000000000000000 SELECT...WHERE Col3='zz' 0x06000F00A9D9A7114041D983010000000000000000000000
0x06000F00B83F933140E19D85010000000000000000000000 SELECT...WHERE Col3='ab' 0x06000F00A9D9A7114041D983010000000000000000000000
0x06000F00D78B910340215584010000000000000000000000 SELECT...WHERE Col3='ef' 0x06000F00A9D9A7114041D983010000000000000000000000
0x06000F00A9D9A7114041D983010000000000000000000000 (@1 varchar(8000))... NULL
Как видите - все четыре плана являются самостоятельными объектами кэша, у них свои ID/хэндлы (колонка plan_handle). Однако все три shell-плана дружно посылают нас к хэндлу единственного в данной четверке полноценного плана (колонка parameterized_plan_handle).
И лишь теперь, вооруженные четкими критериями подтверждения успешности процесса параметризации запросов, мы можем двинутся по тернистому и разветвленному пути реализации этого самого процесса.