Параметр конфигурации сервера "Оптимизировать для нерегламентированной рабочей нагрузки"

Применимо к: SQL Server Azure SQL DatabaseУправляемый экземпляр SQL AzureAnalytics Platform System (PDW)

Параметр optimize for ad hoc workloads используется для повышения эффективности кэширования планов рабочих нагрузок, содержащих много отдельных нерегламентированных пакетов. Если этот параметр имеет значение 1, то при первой сборке пакета ядро СУБД сохраняет в кэше планов небольшую скомпилированную "заглушку" плана, а не полный план. Это может уменьшить нагрузку на память, чтобы кэш не наполнялся скомпилированными планами, которые не будут использоваться повторно. Однако включение этого параметра может затруднить устранение неполадок с планами, используемыми однократно.

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

Чтобы найти скомпилированные заглушки плана, запросите представление каталога sys.dm_exec_cached_plans и найдите "Скомпилированный план" в столбце cacheobjtype. Заглушка имеет уникальный дескриптор plan_handle. Скомпилированная заглушка плана не имеет связанного плана выполнения, а запрос по дескриптору плана не возвращает данных XML Showplan.

Флаг трассировки 8032 восстанавливает параметры лимита кэша из версии SQL Server 2005 (9.x) RTM, которые обычно допускают кэши большего размера. Используйте этот параметр, когда многократно используемые записи не помещаются в кэш и параметр Оптимизировать для нерегламентированной рабочей нагрузки не решает проблему с кэшем планов.

Предупреждение

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

Примечания

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

Чтобы немедленно применить параметр к уже кэшированным планам запросов, необходимо очистить кэш планов с помощью инструкции ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE или перезапустить SQL Server.

Рекомендации

Старайтесь, чтобы в кэше планов не находилось много планов разового применения. Такая проблема часто возникает при несогласованных определениях типов данных параметров запроса. Это относится в первую очередь к длине строк, но может случаться с любыми типами данных, имеющими максимальную длину, точность или масштаб. Например, если параметр с именем @Greeting передается как nvarchar(10) в одном вызове и как nvarchar(20) в следующем вызове, для каждого размера параметра создаются отдельные планы.

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

Если число одноразовых планов занимает существенную часть памяти ядра СУБД SQL Server на сервере OLTP и эти планы являются нерегламентированными, используйте данный параметр сервера для уменьшения объема памяти, потребляемой этими объектами.

Пример

Чтобы найти количество одноразовых кэшированных планов, выполните следующий запрос:

SELECT objtype, cacheobjtype,
    AVG(usecounts) AS Avg_UseCount,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS bigint))/1024/1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' AND usecounts = 1
GROUP BY objtype, cacheobjtype;

См. также раздел