SQL-Ex blog

В этой первой части после краткого введения я рассматриваю влияние простой параметризации на кэш планов.

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

Почти всегда лучше явно параметризовать операторы, а не полагаться на сервер. Это дает вам полный контроль над всеми аспектами процесса параметризации, включая то, где используются параметры, какие точные типы использовались и когда повторно использовались планы.

Большинство клиентов и драйверов предоставляют конкретные способы для использования явной параметризации. Имеется также варианты типа sp_executesql, хранимых процедур и функций.

Я не собираюсь затрагивать связанные проблемы прослушивания параметра или SQL-инъекции, поскольку, несмотря на их важность, они не являются целью этой серии. Тем не менее, вы должны писать код, имея в виду и то, и другое.

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

Планы оболочки

Когда в SQL Server 2005 была введена принудительная параметризация (Forced Parameterization), имеющаяся функция авто-параметризации была переименована в простую параметризацию (Simple Parameterization). Несмотря на изменение терминологии, простая параметризация работает так же, как всегда работала авто-параметризация: SQL Server пытается заменить значения литерала константы в операторах ad hoc на маркеры параметров. Целью является уменьшение числа компиляций за счет увеличения повторного использования кэшированных планов.

Давайте рассмотрим пример, использующий базу данных Stack Overflow 2010 на SQL Server 2019 CU 14. Уровень совместимости базы данных установлен в 150, а стоимостный порог параллелизма - в 50, чтобы избежать пока параллельного выполнения:

EXECUTE sys.sp_configure

    @configname = 'show advanced options',

    @configvalue = 1;

RECONFIGURE;

GO

EXECUTE sys.sp_configure

    @configname = 'cost threshold for parallelism',

    @configvalue = 50;

RECONFIGURE;

Код примера:

-- Чистка кэша планов для этой базы данных

ALTER DATABASE SCOPED CONFIGURATION 

    CLEAR PROCEDURE_CACHE;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2521;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2827;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3144;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3151;

GO

Эти операторы содержат предикаты, которые отличаются только значениями своих литеральных констант. SQL Server успешно применяет простую параметризацию, что приводит к параметризованному плану. План простой параметризации используется четыре раза, как можно увидеть, запросив кэш планов:

SELECT

    CP.usecounts,

    CP.cacheobjtype,

    CP.objtype,

    CP.size_in_bytes,

    ST.[text],

    QP.query_plan

FROM sys.dm_exec_cached_plans AS CP

OUTER APPLY sys.dm_exec_sql_text (CP.plan_handle) AS ST

OUTER APPLY sys.dm_exec_query_plan (CP.plan_handle) AS QP

WHERE 

    ST.[text] NOT LIKE '%dm_exec_cached_plans%'

    AND ST.[text] LIKE '%DisplayName%Users%'

ORDER BY 

    CP.usecounts ASC;

Результаты показывают запись из кэша планов для Adhoc на каждый исходный оператор и единственный подготовленный план (Prepared):



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

Обратите внимание, что SQL Server кэширует как исходный текст, так и его параметризованную форму. При успешной простой параметризации план, связанный с исходным текстом является Adhoc и не содержит полного плана выполнения. Напротив, кэшированный план является оболочкой, которая является ничем иным как указателем на подготовленный параметризованный план.

XML-представление планов оболочки содержит примерно такой текст:

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4188.2">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple
StatementText="SELECT U.DisplayName FROM dbo.Users AS U WHERE U.Reputation = 3151"
StatementId="1"
StatementCompId="1"
StatementType="SELECT"
RetrievedFromCache="true" ParameterizedPlanHandle="0x0600050090C8321CE04B4B079E01000001000000000000000000000000000000000000000000000000000000"
ParameterizedText="(@1 smallint)SELECT [U].[DisplayName] FROM [dbo].[Users] [U] WHERE [U].[Reputation]=@1" />
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Это весь план. ParameterizedPlanHandle указывает из оболочки Adhoc на полный параметризованный план. Значение handle одно и то же для всех четырех планов оболочки.

Заглушки плана

Планы оболочки меньше, чем полный скомпилированный план - 16Кб по сравнению с 40Кб в этом примере. Это все же может существенно увеличить затраты памяти, если у вас много операторов, использующих простую параметризацию или множество различных значений параметра. Большинство экземпляров SQL Server не настолько богаты памятью, чтобы позволить себе подобные траты. Планы оболочки рассматриваются SQL server как одноразовые, но их обнаружение и удаление потребляет ресурсы и может стать кричным моментом.

Мы можем уменьшить общее потребление памяти для планов оболочки, включив опцию optimize for ad hoc workloads (оптимизировать для рабочих нагрузок ad hoc.

EXECUTE sys.sp_configure

    @configname = 'show advanced options',

    @configvalue = 1;

RECONFIGURE;

GO

EXECUTE sys.sp_configure

    @configname = 'optimize for ad hoc workloads',

    @configvalue = 1;

RECONFIGURE;

Это кэширует небольшие заглушки, когда оператор ad hoc встречается впервые, вместо оболочки. Заглушки служат в роли закладок, поэтому сервер может помнить, что он видел точный текст инструкции раньше.
Когда тот же текст встречается во второй раз, выполняется компиляция и кэширование так, как будто опция optimize for ad hoc workloads не была включена.

Повторное выполнение примера с включенной опцией optimize for ad hoc workloads показывает влияние на кэш планов.



Ни один из планов для операторов ad hoc не был кэширован, а только заглушка. Тут нет указателя ParameterizedPlanHandle на подготовленный план, хотя полный параметризованный план кэширован.

Повторное выполнение тестовых пакетов (без очистки кэша планов) дает тот же вывод, как будто optimize for ad hoc workloads не была включена - четыре плана оболочки Adhoc указывают на подготовленный план.

Прежде чем продолжить, сбросим установку optimize for ad hoc workloads в нуль:

EXECUTE sys.sp_configure

    @configname = 'optimize for ad hoc workloads',

    @configvalue = 0;

RECONFIGURE;

Пределы размера кэша планов

Используются ли оболочки плана или заглушки плана, все эти записи кэша для Adhoc имеют недостатки. Я уже упоминал об общем использовании памяти, но кэш каждого плана также имеет максимальное число записей. Даже там, где общее использование памяти не имеет значения, может иметь значение это количество.

Эти пределы могут быть увеличены с помощью документированного флага трассировки 174 (число записей) и href="https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql#trace-flags">флага трассировки 8032 (общий размер). В зависимости от рабочей нагрузки и других требований к памяти это может оказаться лучшим решением. Кроме всего прочего, это просто означает кэширование большего числа малозначимых Adhoc-планов, отнимая память у других нужд.

Кэширование только подготовленных планов

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

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

Для рабочих нагрузок, которые получают преимущество от простой параметризации, но не от кэширования записей ad-hoc, есть пара вариантов.

Недокументированный флаг трассировки

Первый вариант - это включение недокументированного флага трассировки 253. Он полностью предотвращает кэширование планов Adhoc. Он не просто ограничивает число таких планов или препятствует им "оставаться" в кэше, как можно было бы предположить.

Флаг трассировки 253 можно включить на уровне сессии - его влияние распространяется только в данном подключении - или на более широком глобальном уровне как флаг при запуске. Он также функционирует как хинт запроса, но его использование препятствует простой параметризации, что может оказаться непродуктивным здесь. Имеется неполный список вещей, которые препятствуют простой параметризации, в Microsoft Technical Paper, Plan Caching and Recompilation in SQL Server 2012.

При активном флаге трассировки до компиляции пакета кэшируются только подготовленные (Prepared) операторы.

ALTER DATABASE SCOPED CONFIGURATION 

    CLEAR PROCEDURE_CACHE;

GO

-- Не кэшировать ad-hoc планы

DBCC TRACEON (253);

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2521;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2827;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3144;

GO

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3151;

GO

-- Снова кэшировать ad-hoc планы

DBCC TRACEOFF (253);

GO

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

Некэшируемый пакет

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

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

Два достаточно конфиденциальных оператора и пример использования показаны ниже (теперь с тестовыми операторами в едином пакете):

ALTER DATABASE SCOPED CONFIGURATION 

    CLEAR PROCEDURE_CACHE;

GO

-- Предотвращает кэширование всех операторов в этом пакете.

-- Ни KEY, ни CERTIFICATE не обязательно должны существовать.

-- Не требуется специальных разрешений.

-- GOTO гарантирует, что операторы не выполняются.

GOTO Start

    OPEN SYMMETRIC KEY Banana 

        DECRYPTION BY CERTIFICATE Banana;

Start:

/* Другой способ достичь того же эффекта без GOTO

IF 1 = 0

BEGIN

    CREATE APPLICATION ROLE Banana 

    WITH PASSWORD = '';

END;

*/

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2521;

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 2827;

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3144;

SELECT U.DisplayName

FROM dbo.Users AS U 

WHERE U.Reputation = 3151;

GO

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



Ни одно из решений не является идеальным, но пока Microsoft не предоставит документированного и поддерживаемого решения этой проблемы, это - лучшие варианты, я полагаю.

Конец первой части

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