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

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

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

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

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

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

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

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

Замечания

Установка для параметра optimize for ad hoc workloads значения 1 влияет только на новые планы; планы, которые уже находятся в кэше планов, не затрагиваются.

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

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

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

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

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

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

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

  • Функция хранилище запросов, представленная в 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;

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

  • Last updated on