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

Параметризация автоматическая, простая.

Собственно, как было отмечено во вступлении, не менее 90% реальных систем (а более технически корректно будет сказать - баз данных, ибо данная опция устанавливается именно на уровне БД) счастливо пребывают именно в этом состоянии - простой авто-параметризации. Наша тестовая база [~DB~] благодаря строке

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

• ключевое слово IN в клаузуле WHERE, например ...WHERE Col3 IN ('ab','zz');
• ключевое слово UNION;
• ключевое слово DISTINCT;
• ключевое слово TOP;
• клаузулу GROUP BY;
• любой подзапрос;
• любой хинт запроса;

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

Итак, наиболее частой и непреодолимой преградой на пути простой авто-параметризации становятся:

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

Помните, как в вводной части статьи у нас параметризация не работала с индексом iT1 на колонке Col3, и включалась лишь когда мы этот индекс запрещали? Это и было прямое следствие последнего пункта. А происходит вот что:

• если индекс iT1 включен и запрос пытается фильтровать строки по значению колонки Col3, то у оптимизатора появляется выбор: требуемые строки можно или извлечь путем поиска в указанном индексе (Index Seek) с последующим до-извлечением значений колонок ColID/Col2 из кластерного индекса, или же просто приступить к сканированию кластерного индекса (Index Scan), выбирая все три колонки из него и вообще игнорируя услуги не кластерного индекса iT1. Как известно читателям другой статьи блога, Density, Selectivity, Cardinality или о чем "думает" оптимизатор, грамотный выбор между этими вариантами сервер обеспечивает себе сам, предусмотрительно и заранее создав такую вещь как статистику. Для нас же важно, что альтернатива есть, и зависит она от конкретного значения литерала, он же потенциальный будущий параметр. Стало быть простой авто-параметризации вход сюда заказан;
• после отключения индекса альтернатива исчезает, остается только сканирование, без вариантов. Авто-параметризация наконец-то получает шанс себя проявить!

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

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

Нельзя не отметить довольно забавные "грабли" связанные с типом параметров тех запросов, коим удалось "прорваться" сквозь суровый "просев" простой авто-параметризации. Как хорошо видно из примеров в вводном разделе статьи, при параметризации литералов имеющих строковую природу оптимизатор ведет себя вполне разумно. Скажем все эти наши 'ab', 'ef', 'zz' имеют подразумеваемый тип char(2) (ну или varchar(2) - не важно). Тем не менее, в процессе параметризации оптимизатор запросов "не мелочится", а берет размер строкового параметра максимальный, varchar(8000). Это очень правильно, ибо подавляющее большинство будущих потенциальных значений этого параметра, очевидно, "влезут" в означенные границы. А значит можно будет просто создать новый shell-план, "пристыковать" его к имеющемуся плану и так далее. Кстати, если бы наши символы были юникодными, то тип параметра был бы nvarchar(4000), можете проверить этот факт сами.

Все меняется когда мы переходим к числам. Возьмем, для примера, колонку Col2 все той же таблицы T1. Она имеет подходящий тип -smallint, а благодаря отсутствию каких либо индексов на этой колонке запросы ведущие фильтрацию по ней будут подвергаться простой авто-параметризации в любом случае. Составим такой набор инструкций:

Ясно, что все четыре команды будут параметризированы, однако какого типа окажется параметр? Видя, как разумно ведет себя оптимизатор в случае строк, очень хочется верить, что встретив первый же литерал (4) оптимизатор организует параметр типа int (если неbigint). "Про запас", так сказать. Ну что же, выполним показанный набор инструкций, а затем нашим "запросом кэша" (его модифицированным вариантом, с двумя добавочными колонками) посмотрим, что же в этом самом кэше окажется. А окажется там вот что:

Да уж... То есть, события развиваются таким образом:

• оптимизатор берет первый литерал, 4, и преобразует его в параметр с минимальным (по размеру данных) типом tinyint способным этот литерал "принять";
• создается план для параметризированной версии запроса и shell-план на него ссылающийся;
• следующий литерал, 200, все еще "проходит" в тип tinyint, а значит можно создать новый shell-план и сослаться им на существующий план;
• третий литерал, 5690, в tinyint уже не умещается, то есть - "начинай все сначала". Новый план, с новым типом параметра, новый shell-план к нему...;
• четвертый литерал не умещается уже и в smallint, а поэтому... да, "заход на третий круг". 

Чего именно пытается выгадать оптимизатор отрезая память для параметра в час по чайной ложке, на чем сэкономить, известно, по видимому, лишь программистам движка сервера. Главный "прикол" заключается в том, что если вы "перевернете" последний пакет инструкций так, чтобы SELECT с литералом 1000000 был первым, а с литералом 4 - последним, очистите кэш и повторите опыт с самого начала, то результат будет... полностью идентичен! То есть имея уже готовый план с параметром типа int привести 4 к этому типу никак нельзя. То есть ни разу. Надо создать новый план, новый shell-план к нему, но таки добиться, что бы параметр имел тип "оптимального" размера! Это, конечно, и есть работа оптимизатора запросов - оптимизировать все подряд, но иногда создается впечатление что данной "умной машинке" (оптимизатор SQL Server и вправду чрезвычайно умен, здесь нет никакого сарказма) забыли прикрутить педаль тормоза.

Воспользуемся случаем, и предложим столь любимую постоянными читателями блога задачку на сообразительность (в основном) и знание языка T-SQL (чуть-чуть). Разумеется с приложением верного ответа.

Задание: отредактировать текст четырех обсуждаемых команд таким образом, что бы функциональность каждой осталось без изменений, а оптимизатор все же создал четыре shell-плана ссылающихся на единственный полный план.

Наш "гранд-тотал": простая авто-параметризация - это очень просто. Только иногда странно.

Количественная оценка простой авто-параметризации.

Вполне возможно, что вы зададитесь вопросом - насколько интенсивно работает конкретно в ваших условиях изучаемый нами механизм? Пытается ли он параметризировать два запроса в час или сто тысяч в секунду? В основном такая параметризация успешна или нет? Ну и прочие вопросы подобного толка. По счастью, ответить на них весьма несложно, вот только для интерпретации полученных значений нужно четко понимать: процесс простой авто-параметризации есть процесс двухфазный, о чем мы уже рассказали. Да, на первой фазе LPE сообщает о формальной возможности провести параметризацию запроса, на второй фазе QP оценивает запрос или как "безопасный" (safe) и утверждает предложенный вариант параметризации, или как "небезопасный" (unsafe) и оставляет запрос в оригинальном тексте, с константами. Правила по которым каждый запрос попадает в первую или вторую категорию так же были отмечены выше, правда в очень сокращенном варианте.

Если описанная механика нами осознана, то остальное - чистая техника. Открываем наш любимый Performance Monitor содержащий, как всем хорошо известно, прорву счетчиков. Из этой прорвы нас интересуют всего пять, и все пять от объекта SQL Server:SQL Server Statistics. Вот они:

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

Показания счетчиков будут такими (поскольку все и каждый счетчик меряет ту или иную характеристику "в секунду" я не буду упоминать данный временной отрезок в каждом пункте списка, однако держите этот факт в голове):

• Auto-Param Attempts/sec=4 - сколько попыток параметризации предпринимает компонент LPE. Счетчик принимает во вниманиетолько простую авто-параметризацию. Принудительная авто-параметризация "обслуживается" иным счетчиком, см. далее. В нашем случае единственный запрос для которого LPE даже не пытался провести процедуру параметризации - 4-й. Это потому, что у негоWHERE с IN. Для остальных четырех запросов попытка предпринималась;
• Failed Auto-Params/sec=1 - сколько из таких попыток закончилось неудачей , то есть LPE не смог произвести замену константы на параметр. В нашем случае единственный (из четырех предыдущего пункта) запрос где это не получилось - запрос номер 5. А вот "корни" этой неудачи (а она была предопределена) мы рассмотрим не сейчас, а в следующем разделе;
• Forced Parameterizations/sec=0 - как счетчик из первого пункта, но только для принудительной авто-параметризации. В нашем случае, понятно, таких попыток не было;
• Safe Auto-Params/sec=1 - сколько из предложений поступивших от LPE QP оценил как "безопасные" и, таким образом, утвердил параметризацию. В нашел случае "прокатил" всего один запрос - номер 2. Причины по которым этот запрос оценен именно так мы уже разбирали;
• Unsafe Auto-Params/sec=2 - противоположно предыдущему, QP оценил предложения LPE как "небезопасные" и, таким образом, "зарезал" параметризацию. В нашем случае таких "резанных" два - запросы 1 и 3. Причины тому опять же разбирались выше.

Наконец замечу, что для простой авто-параметризации действует вот такая забавная арифметика: Auto-Param Attempts=Failed Auto-Params+Safe Auto-Params+Unsafe Auto-Params. Это потому, что если попытка параметризации вообще происходит, то либо LPE не справляется с поставленной задачей, либо QP утверждает ее, либо он же отвергает ее. Все, иных путей нет.

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

Как было показано в предыдущем разделе, существует целый "айсберг" причин (в тексте статьи была показана только его "верхушка"), по которым может "обломаться" простая авто-параметризация . Тут вам и потенциальная двойственность плана в зависимости от значения параметра, и JOIN, и WHERE вместе с IN... Все это не позволяет развернутся параметризации запросов во всю ширь, однако, с другой стороны, делает указанный процесс чрезвычайно надежным, то есть если запрос был параметризирован в "простом" режиме, то на 99.99% мы получим прирост производительности, но никак не падение.

Иногда складываются ситуации, когда мы можем захотеть несколько понизить упомянутую надежность, но взамен получить гораздо более обширное "покрытие" запросов, попавших под воздействие параметризации. Такое увеличение числа параметризированных запросов потенциально (только потенциально! никаких гарантий) ведет к улучшению (иногда огромному, в буквальном смысле) производительности системы и гораздо более быстрому отклику на запросы пользователей. Мы кратко опишем чего можно ожидать от внедрения подобного подхода в заключительной части статьи.

Так вот принудительная параметризация принимает на вооружение именно эту концепцию - параметризировать если не 100% запросов, то уж определенно число запросов максимально близкое к этой отметке. Запросы не поддающиеся параметризации остаются и в этом режиме, но их число, можно сказать, исчезающе мало. Например, как вы помните, в начале статьи мы исполняли скрипт из пяти SELECT-ов при включенном индексе iT1. Последний фактор мешал простой параметризации (а чем он мешал мы тоже теперь уже знаем - двойственностью плана), и оптимизатор делал для этих пяти команд три полностью независимых плана, без всякой их параметризации. Давайте переведем нашу базу в режим принудительной параметризации:

и выполним тот же самый скрипт, без каких либо модификаций, в этом новом режиме и при включенном индексе. Результаты будут такими:

То есть параметризация в чистом виде, и никакие множественные планы на нашем пути больше не стоят! Вы можете сами придумать запросы с JOIN, WHERE/IN, TOP и прочими "страшными" для простой авто-параметризации словами и убедиться - все прекрасно параметризируется. То есть в этом режиме QP становится куда как более агрессивным, и бросается "аки коршун" на любой запрос хоть мало-мальски подверженный параметризации.

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

Итак, практически все препоны стоящие на пути простой авто-параметризации убираются в альтернативном режиме. А какие преграды все же остаются? Да, список таковых имеет место быть, но число пунктов в нем в разы короче по сравнению с аналогичным списком простого режима. На взгляд автора внимания заслуживают всего три причины:

• не подлежат принудительной авто-параметризации запросы содержащиеся внутри хранимых процедур и функций, однако сами эти программные модули как единое целое прекрасно параметризуются. Можно сказать, что это и не ограничение вовсе, а разумное отключение автоматической параметризации в виду принятия этой нагрузки на себя SQL-программистом. В рамках данной монографии будет отдельный раздел посвященный параметризации хранимых процедур, в котором мы вернемся к данному моменту;
• не подлежат принудительной авто-параметризации запросы которые уже были параметризированы на стороне клиента. Это, опять-таки, скорее не ограничение, а практичный подход к вопросу. И мы так же рассмотрим клиентскую параметризацию в соответствующем разделе;
• так же не подлежат принудительной авто-параметризации запросы ссылающиеся на ранее декларированные переменные. Пример такого запроса вы вскоре увидите.

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

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

• не параметризуется любой литерал после ключевого слова TOP, что не препятствует параметризации клаузулы WHERE. Пример:SELECT TOP 2 ColID FROM T1 WHERE Col3='ef'. Константа 'ef' параметризируется, но 2 остается как есть;
• аналогично для предложения TABLESAMPLE: SELECT ColID FROM T1 TABLESAMPLE (10 PERCENT) WHERE Col3='ef'. 'ef' - да, 10 - нет;
• аналогично для клаузулы ORDER BY: SELECT ColID, Col2, Col3 FROM T1 WHERE Col3='ef' ORDER BY 3. 'ef' - да, 3 - нет;
• аналогично для клаузул HAVING, GROUP BY;
• так же не параметризуется любой литерал в списке колонок (select list) команды SELECT: SELECT ColID, 444 AS Num FROM T1 WHERE Col3='ef'. 'ef' - да, 444 - нет. Этот пункт, кстати говоря, объясняет почему пятый SELECT из раздела о количественной оценки простой авто-параметризации приводит нас к срабатыванию счетчика Failed Auto-Params/sec. LPE действительно не смог в том запросе заменить литерал 444 на параметр, но лишь потому, что ему явно (и без вариантов) запрещено это делать.

Так что повторю: по большому счету весь сыр-бор идет вокруг констант именно клаузулы WHERE. С другой стороны 99% "почти одинаковых" запросов могущих извлечь максимальную пользу из повторного использования планов, отличаются значением литералов именно в этой клаузуле, что делает ее такое "выпячивание" вполне резонным. И, конечно же, показанные ограничения на параметризацию различных частей запроса работают совершенно аналогично как в простой, так и в принудительной авто-параметризациях.

Помимо более агрессивной политики по отношению к запросам потенциально пригодных к параметризации, принудительный режим гораздо разумнее ведет себя и по отношению к типам параметров. Помните тест предыдущего раздела с параметрами типаtinyint/smallint/int? И как мы ломали голову в поисках варианта по принуждению оптимизатора считать все целые числа одинаковыми по типу? Отрадно отметить, что в обсуждаемом режиме абсолютно тот же скрипт дает, причем без всякого "тюнинга", такие результаты:

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

Количественная оценка принудительной авто-параметризации.

Как мы уже знаем, есть пять счетчиков рапортующих нам о трудовых буднях параметризации. Однако, если база находится в режиме принудительной авто-параметризации, то актуальными из пяти остаются лишь два. Дело в том, что в отличии от двухфазной природы авто-параметризации в простом режиме (что было обсуждено нами в предыдущем разделе), тот же процесс в режиме принудительном становится, по сути, однофазным. Либо у LPE вообще не получается предложить QP параметризированную версию запроса (это может произойти по причинам как объективным, например у очень сложного запроса столь огромное число констант, что LPE просто не способен "переварить" такое их количество; так и по причинам субъективным, например константа находится не в клаузуле WHERE, а в списке колонок, трогать который LPE попросту запрещено) и тогда срабатывает счетчик Failed Auto-Params/sec. Либо у LPE все получилось и QP радостно и без всяких дальнейших рассуждений бросается на предложенный вариант, а срабатывает счетчик Forced Parameterizations/sec. Три оставшихся счетчика актуальны только для простого режима работы.

Давайте посмотрим на работу этих двух счетчиков на примере вот такого запроса:

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

• инструкции 1,2,3 являются самыми стандартными с точки зрения параметризации. В принудительном режиме ничто не может помешать LPE создать их параметризированные версии, а QP - принять эти версии и строить планы для них, а не для оригинальных запросов. Каждая из трех команд увеличивает значение счетчика Forced Parameterizations/sec на 1;
• инструкция 4 является примером, когда LPE "хотел бы, но не может". Ему запрещено трогать литералы в списке колонок. Но с формальной точки зрения, в оригинальном запросе литерал есть, а предложенного LPE варианта без него (но с параметром) - нету. Это, опять же формально, считается "фэйлом" параметризации, плюсуем Failed Auto-Params/sec;
• инструкция 5 на самом деле является аналогом предыдущей. Снова LPE очень хотел бы заменить 'ab' на параметр, но ему запрещено это делать, потому что именно сам запрос содержит не литерал, а переменную с литералом "внутри". Забавно, но чисто визуально разбираемая инструкция кажется идеальным кандидатом на параметризацию - практически все сделано программистом, не нужно даже "выцеплять" подходящий литерал сразу ясно, что переменная @b является "почти" им. Но засада в том (и большинство DBA успешно об этом забывают), что скомпилированный план пакета инструкций содержит все инструкции пакета. А поэтому факт присвоения переменной @b значения 'ab' будет жестко "зашит" в готовый план, а значит при повторном использовании того же плана та же переменная получит непременно тоже самое значение, без вариантов. А как следствие - "заморачиваться" параметризацией при таких раскладах резона нет вообще никакого. По итогу в кэш попадает план для оригинального запроса, а счетчик Failed Auto-Params/sec вновь плюсуется;
• инструкция 6 близка по характеру к двум предыдущим, но имеет одно важное отличие. На первый взгляд, если была успешно параметризована инструкция 1, то почему бы не проделать тоже самое с разбираемой инструкцией? Однако - о чем нам говорит хинт RECOMPILE? О том, что готовый план для данной инструкции вообще не следует сохранять в кэше, а нужно отбросить его сразу же по окончании исполнения. О каком повторном использовании плана (а это и есть мета-задача параметризации) может идти речь? Верно - ни о каком! Так вот LPE "соображает" все это даже быстрее чем мы с вами, и даже не включает свои алгоритмы для поиска "точек параметризации" запроса, то есть вообще пропускает эту стадию анализа. А зачем она? И, таким образом, никакие разбираемые счетчики не плюсуются - для данного запроса теряет смысл сам термин "параметризация".

Итого, если вы решали задачку самостоятельно, то верный ответ таков: после выполнения всех шести инструкций значениями счетчиковFailed Auto-Params/sec и Forced Parameterizations/sec будут цифры 2 и 3 соответственно.