оптимизация для нерегламентированных рабочих нагрузок (параметр конфигурации сервера)

  • Статья

Применяется к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Аналитические платформенные системы (PDW)

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

Заглушка скомпилированного плана позволяет ядро СУБД распознавать, что этот нерегламентированный пакет был скомпилирован ранее, и сохраняет только заглушку скомпилированного плана. При повторном вызове этого пакета (компиляции или выполнения) ядро СУБД компилирует пакет, удаляет заглушку скомпилированного плана из кэша планов и добавляет полный скомпилированный план в кэш планов.

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

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

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

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

Замечания

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

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

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

Старайтесь, чтобы в кэше планов не находилось много планов разового применения. Наиболее вероятные причины:

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

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

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

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

  • Функция хранилище запросов, представленная в SQL Server 2016 (13.x), помогает быстро находить различия в производительности, вызванные изменениями плана запросов. хранилище запросов включен по умолчанию для новых баз данных в SQL Server 2022 (16.x) и более поздних версиях.

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

    Принудительное параметризация может привести к проблемам с производительностью из-за конфиденциальности параметров. Дополнительные сведения см. в разделе "Изучение и устранение проблем с учетом параметров". Для SQL Server 2022 (16.x) и более поздних версий можно также включить оптимизацию конфиденциального плана параметров.

Примеры

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

SELECT objtype,
    cacheobjtype,
    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;

Связанный контент