Параметризация запросов

Tsql теория > Параметризация запросов
29.11.2019 11:59:07



Статья:

Термины и определения

  • Параметризация запросов - замена литеральных констант запроса на типизированные параметры.
  • Полный план запроса - совокупность последовательностей доступа к таблицам / представлениям и методов извлечения данных из них. Для создания требует предварительный анализ запроса и компиляцию, нагружающие CPU.
  • Shell-план (оболочка плана) - список параметров, их значений и ссылка на полный параметризованный план запроса. В разы меньше полного плана. Значительно экономит объем кэша планов.
  • Stub-план (корешок плана) - служебная информация для установки факта исполнения запроса. На порядки меньше полного плана и практически не увеличивает общий объем кэша планов.
  • Кэш планов - системные таблицы, хранящие информацию об исполнявшихся запросах, а также их планы выполнения. Используется для снижения нагрузки на CPU при повторном исполнении одинаковых запросов.
  • Adhoc-запрос - запрос, не прошедший через параметризацию. Как правило, это непараметризованные запросы исполняющиеся как есть.
  • Preapared-запрос - запрос, прошедший через полную или частичную параметризацию. Как правило, это параметризованный запрос с полным планом, на который ссылаются shell-планы других запросов.

Параметризация запросов

Данная процедура значительно влияет на производительность - уменьшает нагрузку на CPU и уменьшает объем кэша планов.

Для примера возьмём следующий простой запрос

Пример запроса, подходящего для параметризации
1
2
3
SELECT *
FROM drType t (NOLOCK)
WHERE t.idType = 123 OR t.sCode = 'ABC';    //в данном запросе две литеральных константы (123 и 'ABC') которые можно параметризовать

Исполнение данного запроса с различными литералами будет создавать adhoc-запросы с полными планами в кэше. В результате имеем разрастание кэша и лишнюю нагрузку на CPU.

Виды параметризации и её особенности

Все виды параметризации будем рассматривать на запросе, приведённом в пункте "Параметризация запросов".

Ручная параметризация

В случае ручной параметризации программист сам должен опредиться с литералами, подлежащими замене и предоставить оптимизатору запросов MS SQL уже параметризованный запрос. Подразумевается, что данные параметры объявлены и заданы до фактического выполнения запроса или же являются входными параметрами хранимой процедуры, в которой находится наш запрос.

Пример параметризованного запроса
1
2
3
SELECT *
FROM drType t (NOLOCK)
WHERE t.idType = @idType OR t.sCode = @sCode;   //константные литералы заменены на параметры @idType и @sCode

Первое исполнение данного запроса создаст 2 записи в кэше:

  1. Prepared-запрос с параметризацией и полным планом.
  2. Adhoc-запрос с shell-планом, ссылающимся на полный план параметризованного prepared-запроса, и содержащий только значения параметров.

Каждое последующее исполнение запроса с новыми параметрами будет создавать только adhoc-запрос c shell-планом, ссылающимся на параметризованный prepared-запрос.

Данный механизм имеет сразу 2 преимущества:

  1. Значительно уменьшает размер кэша т.к. shell-планы в разы меньше полных планов.
  2. Убирает лишнюю нагрузку на CPU т.к. при построении shell-плана не требуется его компиляция, а просходит только сохрание значений параметров.

Параметризация через встроенные функции

Существует вариант параметризации через встроенные функции MS SQL, дающий результат, аналогичный предыдущему пункту, но полученный непосредственно от оптимизатора запросов и считающийся максимально безопасным и верным с точки зрения оптимизатора.

Данная параметризация производится при помощи системной функции sys.sp_get_query_template

Параметризация через встроенные функции
1
2
3
4
5
6
7
8
9
10
11
12
DECLARE @query NVARCHAR(MAX)
        ,@template NVARCHAR(MAX)
        ,@params NVARCHAR(MAX);
 
SET @query = N'SELECT *
            FROM drType t (NOLOCK)
            WHERE t.idType = 123 OR t.sCode = ''ABC'';'
 
EXEC sys.sp_get_query_template
    @query          //текст непараметризованного запроса
    ,@template OUT  //выходной параметр: текст параметризованного запроса
    ,@params OUT;   //выходной параметр: текст с объявлением использованных параметров и их типов

В результате получим следующие данные:

Выходные параметры
1
2
3
4
5
// значение переменной @template
select from drType t ( NOLOCK ) where t . idType = @0 or t . sCode = @1
 
// значение переменной @params
@0 int,@1 varchar(8000)

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

  1. Запрос получен непосредственно от оптимизатора и считается безопасным и потенциально безпроблемным для исполнения.
  2. Запрос проходит нормализацию и форматирование - регистр элеменов приводится к одному виду, убираются лишние пробелы, переносы строк и пр. Данный процесс необходим, так как оптимизатор запросов регистрозависим при построении планов и для двух идентичных запросов, написанных разным регистром сформируются 2 разных плана.

Автопараметризация

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

Простая параметризация ( PARAMETERIZATION SIMPLE )

При создании новой базы данных на ней всегда будет активирован режим PARAMETERIZATION SIMPLE - режим простой параметризации, если специально не указывать принудительную.

Включить данный режим параметризации можно следующей командой

Включение простой параметризации
1
ALTER DATABASE [database_name] SET PARAMETERIZATION SIMPLE

Простая параметризация при выполнении adhoc-запросов и соблюдении некоторых благоприятных условий будет предлагать оптимизатору запросов параметризованную версию запроса. Вот только благоприятные условия с точки зрения оптимизатора складываются "практически никогда". 

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

  • ключевое слово IN в предложении WHERE
  • ключевое слово UNION
  • ключевое слово DISTINCT
  • ключевое слово TOP
  • предложение GROUP BY
  • наличие подзапроса
  • наличие указания (хинта) запроса
  • ключевое слово JOIN в предложении FROM
  • использование поля индекса в предложении WHERE для фильтрации данных

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

Принудительная параметризация ( PARAMETERIZATION FORCE )

Включить данный режим параметризации можно командой

Включение принудительной параметризации
1
ALTER DATABASE [database_name] SET PARAMETERIZATION FORCE

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

  • не подлежат принудительной автопараметризации запросы, содержащиеся внутри хранимых процедур и функций, однако сами эти программные модули как единое целое прекрасно параметризуются в proc-запросы
  • не подлежат принудительной автопараметризации запросы, которые уже были параметризированы на стороне клиента (параметризованные adhoc-запросы)
  • не подлежат принудительной автопараметризации запросы, ссылающиеся на ранее декларированные переменные

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

Параметризация структурами планов

В ядре MS SQL существует возможность применения простой / принудительной параметризации для определённого класса запросов в обход глобального параметра БД PARAMETERIZATION. То есть можно на базе с включенной простой параметризацией для определённого класса запросов применять принудительную параметризацию и наоборот. 

Данную возможность даёт системная функция sys.sp_create_plan_guide, создающая структуру плана запроса, подлежащего оптимизации

Создание структуры плана параметризации
1
2
3
4
5
6
7
EXEC sys.sp_create_plan_guide  
    N'template_guide_name',                                                         //имя структуры плана (задаётся произвольно)
    N'select * from drType t ( NOLOCK ) where t . idType = @0 or t . sCode = @1',   //параметризованный запрос, для которого требуется создание структуры плана
    N'TEMPLATE',                                                                    //тип сущности, в которой отображается запрос (OBJECT | SQL | TEMPLATE). Нам нужен TEMPLATE
    NULL,                                                                           //имя сущности, в которой отображается запрос (объект БД или просто большой запрос. Можно указать NULL для проверки по всем сущностям)
    N'@0 int,@1 varchar(8000)',                                                     //список всех параметров параметризованного запроса
    N'OPTION(PARAMETERIZATION FORCED)';                                             //применяемые хинты запроса (в данном случае - принудительная параметризация)

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

Просмотр созданных структур планов
1
SELECT FROM sys.plan_guides pg (NOLOCK);

Манипуляции производятся с помощью системной функции sp_control_plan_guide:

Манипуляции со структурами планов
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
//удаление структуры плана
EXEC sys.sp_control_plan_guide N'DROP', N'template_guide_name';
 
//отключение структуры плана
EXEC sys.sp_control_plan_guide N'DISABLE', N'template_guide_name';
 
//включение структуры плана
EXEC sys.sp_control_plan_guide N'ENABLE', N'template_guide_name';
 
//удаление всех структур планов
EXEC sys.sp_control_plan_guide N'DROP ALL';
 
//отключение всех структур планов
EXEC sys.sp_control_plan_guide N'DISABLE ALL';
 
//включение всех структур планов
EXEC sys.sp_control_plan_guide N'ENABLE ALL';

Комбинация параметризаций (системные функции + структуры)

Также возможно комбинирование параметризации встроенными функциями и параметризации структурами планов и это рекомендованный Microsoft вариант создания структур:

Комбинация параметризаций
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE @query NVARCHAR(MAX)
        ,@template NVARCHAR(MAX)
        ,@params NVARCHAR(MAX);
 
SET @query = N'SELECT *
            FROM drType t (NOLOCK)
            WHERE t.idType = 123 OR t.sCode = ''ABC'';'
 
EXEC sys.sp_get_query_template
    @query
    ,@template OUT
    ,@params OUT;
 
EXEC sp_create_plan_guide  
    N'template_guide_name',  
    @template,  
    N'TEMPLATE',  
    NULL,  
    @params,  
    N'OPTION(PARAMETERIZATION FORCED)';

Важные особенности параметризации

В случае автопараметризации или через функцию sys.sp_get_query_template оптимизатор параметризует только литералы, находящиеся в предложении WHERE. Для всех остальных частей запроса (список SELECT, предложение FROM и пр.) оптимизатору параметризация запрещена на уровне ядра. Поэтому, если необходима параметризация литералов в списке SELECT и прочих, находящихся не в предложении WHERE - возможно применение исключительно ручной параметризации.

Рекомендации к применению

  • параметризация эффективна в тех случаях, когда в кэш планов попадает много однотипных adhoc-запросов с полными планами - их можно успешно заменить на один параметризованный. Чаще всего это происходит при работе с динамическим SQL - конструирование запроса как строки и непосредственное выполнение.
  • в случае необходимости параметризации чего-либо в запросе вне предложения WHERE, единственный вариант - ручная параметризация (см. "Важные особенности параметризации") или параметризация системными функциями с последующей ручной обработкой литералов вне предложения WHERE
  • ручную параметризацию следует применять только при невозможности использования других видов параметризации, во всех остальных случаях лучше довериться оптимизатору и его вариантам параметризации
  • параметризацию структурами планов рекомендуется всегда комбинировать с параметризацией системными функциями - так оптимизатору будет легче работать
  • крайне не рекомендуется включать принудительную параметризацию на уровне базы данных (включайте только если точно знаете, что делаете)
  • в случае большого количества разнородных adhoc-запросов в кэше планов параметризация неэффективна (см. "Методы борьбы с одноразовыми adhoc-запросами")

Методы борьбы с одноразовыми adhoc-запросами

Исполнение непараметризованных запросов с константными литералами создаёт множество "одноразовых" полных планов выполнения (см. раздел "Параметризация запросов") в случае невозможности автопараметризации. Как уже было сказано выше, это вызывает разрастание кэша планов и повышенную нагрузку на CPU.

Замена полных планов на stub-планы

В MS SQL существует возможность включения опции "optimize for ad hoc workloads" на уровне сервера для автоматической замены полных планов на stub-планы:

Включение замены планов
1
2
3
4
5
6
7
8
sp_configure 'show advanced options',1
GO
RECONFIGURE
GO 
sp_configure 'optimize for ad hoc workloads',1  //допустимые значения: 1 - вкл, 0 - выкл
GO
RECONFIGURE
GO

По результатам выполнения на уровне сервера для всех непараметризованных adhoc-запросов будет происходить сохранение в кэш stub-планов вместо полных планов. При повторном запуске одного и того же запроса stub-план будет заменён на полный план.

Данная опция может значительно уменьшить объем кэша планов и снизить нагрузку на CPU. Но обратите внимание что это не серебряная пуля - данная функциональность тем эффективнее, чем больше в кэше "одноразовых" запросов.

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

Работа с кэшем запросов

Просмотр кэша

Очистка кэша планов
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT TOP 100
    cp.objtype,                                                                                                     //тип объекта запроса (для параметризации нас интересуют значения 'Adhoc' и 'Prepared')
    cp.usecounts,                                                                                                   //количество повторений поиска объета кэша
    cp.size_in_bytes,                                                                                               //размер плана в байтах (чем меньше - тем лучше)
    cp.plan_handle,                                                                                                 //хэндл плана (системный идентификатор)
    st.text,                                                                                                        //текст оригинального запроса          
    parameterized_plan_handle = query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle''NVARCHAR(128)'),    //хэндл параметризованного плана (значение будет только для shell-планов, у остальных - NULL)
    parameterized_text = query_plan.value('(//StmtSimple)[1]/@ParameterizedText''NVARCHAR(MAX)'),                 //текст параметризованного плана (значение будет только для shell-планов, у остальных - NULL)
    qp.query_plan                                                                                                   //план запроса (для stub-планов - NULL)
    FROM sys.dm_exec_cached_plans cp (NOLOCK)
    OUTER APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    OUTER APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    WHERE st.text NOT LIKE '%sys.dm_exec_cached_plans%';

Для поиска конкретных запросов можно фильтровать запрос по st.text LIKE c указанием части тела запроса.

Очистка кэша

Очистка кэша планов
1
2
3
4
5
6
7
8
9
//очистка кэша планов конкретной базы данных
USE [database_name];
GO
DECLARE @id INT = DB_ID();
DBCC FLUSHPROCINDB (@id);   //использовать именно через параметр, указать сразу DB_ID() нельзя
GO
 
//очистка планов всех баз экземпляра MS SQL
DBCC FREEPROCCACHE