Оптимизация базы данных для нерегламентированных запросов

Автор: Joe Sack, Database scoped optimizing for ad hoc workloads

SQL Server предоставляет параметр оптимизации для нерегламентированных рабочих нагрузок (ad-hoc workloads), действующий в рамках всего сервера, который используется для уменьшения объёма памяти, занимаемого одиночными ad-hoc пакетами и связанными с ними планами. Когда этот параметр включён на уровне экземпляра SQL Server, при первом выполнении пакета ad-hoc для любой базы данных на экземпляре сохраняется «заглушка» скомпилированного плана с уменьшенным потреблением памяти. Эта опция сервера OPTIMIZE_FOR_AD_HOC_WORKLOADS доступна начиная с SQL Server 2019, и имет область действия на уровне базы данных.

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

SELECT configuration_id, name, value
FROM sys.database_scoped_configurations
WHERE name = N'OPTIMIZE_FOR_AD_HOC_WORKLOADS';

Значение конфигурации по умолчанию — «0» (отключено). Когда она включена, вы увидите значение «1».

Чтобы включить «оптимизацию для рабочих нагрузок ad-hoc» для вашей базы данных, выполните следующую команду в контексте базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Примечание: Как и при любом вызове ALTER DATABASE SCOPED CONFIGURATION, выполнение этой команды очистит кэш процедур для записей текущей базы данных.

Чтобы отключить «оптимизацию для рабочих нагрузок ad-hoc» для вашей базы данных, выполните следующую команду:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = OFF;

Заглушки планов имеют меньший объём памяти по сравнению с полным скомпилированным планом. Ниже приведён пример различий в размере плана в байтах для одноразового запроса ad-hoc:

Запрос	OPTIMIZE_FOR_AD_HOC_WORKLOADS OFF	OPTIMIZE_FOR_AD_HOC_WORKLOADS ON
SELECT ProductModelID, Summary FROM SalesLT.vProductModelCatalogDescription;	425 984 байт	432 байта

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