Принудительное применение оптимизированного плана с помощью хранилища запросов

Применимо к: SQL Server 2022 (16.x) База данных SQL AzureSQL база данных в Microsoft Fabric

Оптимизация запросов — это многоэтапный процесс создания "достаточно хорошего" плана выполнения запроса. В некоторых случаях компиляция запросов, которая является частью оптимизации запросов, может занимать большую долю общего времени выполнения запроса и потреблять значительное количество системных ресурсов. Оптимизированное принудительное применение планов является частью семейства функций интеллектуальной обработки запросов. Оптимизированное принудительное применение плана снижает затраты на компиляцию для повторно выполняемых запросов с принудительно применяемым планом и требует, чтобы хранилище запросов был включен и находился в режиме «чтение-запись». После создания плана выполнения запроса этапы компиляции сохраняются для повторного использования в виде сценария воспроизведения оптимизации. Сценарий воспроизведения оптимизации хранится как часть сжатого XML-файла Showplan в хранилище запросов в скрытом атрибуте OptimizationReplay.

Реализация принудительного применения оптимизированного плана

Когда запрос сначала проходит процесс компиляции, пороговое значение на основе оценки времени, затраченного на оптимизацию (на основе дерева ввода оптимизатора запросов), определяет, создается ли скрипт воспроизведения оптимизации.

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

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

Considerations

Когда включена функция принудительного применения оптимизированного плана, критерии применимости для принудительного применения оптимизированного плана следующие:

1. Допустимы только планы запросов, которые проходят полную оптимизацию, что можно проверить наличием свойства StatementOptmLevel="FULL".

2. Инструкции с подсказкой RECOMPILE и распределённые запросы не поддерживаются.

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

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

Включение и отключение принудительного применения оптимизированного плана

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

Включение или отключение принудительного применения оптимизированного плана для базы данных

Оптимизированное принудительное применение плана включено по умолчанию для новых баз данных, созданных в SQL Server 2022 (16.x) и более поздних версиях. Для всех баз данных, в которых используется принудительное применение оптимизированного плана, необходимо включить хранилище запросов. Для обновленных экземпляров с существующими базами данных, а также базами данных, восстановленными из более ранней версии SQL Server, по умолчанию включено оптимизированное принудительное применение плана.

Чтобы включить принудительное применение оптимизированного плана на уровне базы данных, используйте конфигурацию уровня базы данных ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Необходимо включить хранилище запросов, если оно еще не включено. Пример кода можно найти в Примере А. Либо изучите дополнительные сведения о хранилище запросов в статье Мониторинг производительности с помощью хранилища запросов.

Чтобы отключить принудительное применение оптимизированного плана на уровне базы данных, используйте конфигурацию с областью базы данных ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF.

Отключение принудительного применения оптимизированного плана с помощью указания запроса

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

Пример применения этой подсказки запроса можно найти в примере E.

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

Процедура sp_query_store_force_plan включает disable_optimized_plan_forcing параметр. Чтобы использовать этот параметр, дополнительный параметр требуется хранимой процедурой sp_query_store_force_plan . Дополнительный параметр называется @replica_group_id. По умолчанию основной @replica_group_id имеет значение, равное единице (1), даже если вторичные реплики не настроены.

Найдите пример применения соответствующих параметров к хранимой процедуре sp_query_store_force_plan в примере C.

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

Examples

Примеры кода в этой статье используют базу данных образца AdventureWorks2025 или AdventureWorksDW2025, которую можно скачать с домашней страницы образцов и проектов сообщества Microsoft SQL Server и.

A. Включить Хранилище запросов и принудительное применение оптимизированного плана для базы данных

Следующий код включает Хранилище запросов для базы данных, а затем включает для этой базы данных оптимизированное принудительное применение планов. Узнайте больше о параметрах, позволяющих включить хранилище запросов в ALTER DATABASE SET параметрах.

Перед выполнением кода подключитесь к соответствующей пользовательской базе данных.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
(
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Выбор всех запросов, имеющих сценарий воспроизведения оптимизации

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

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Принудительное применение плана и отключение принудительного применения оптимизированного плана в хранилище запросов

Следующий код принудительно применяет план в хранилище запросов, но отключает принудительное применение оптимизированного плана. Перед выполнением следующего кода замените @query_id и @plan_id комбинацией, подходящей для вашего экземпляра. Хранимая процедура sp_query_store_force_plan ожидает, что параметр @replica_group_id передается в качестве третьего параметра при попытке отключить принудительное применение оптимизированного плана в хранилище запросов. Это можно использовать для отключения принудительного применения оптимизированного плана для определённого принудительно заданного плана на определённой реплике. Значение @replica_group_id = 1 используется для отключения функции на первичной реплике.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

Дополнительные сведения см. в sp_query_store_force_plan.

D. Выбор всех запросов, в которых принудительное применение оптимизированного плана отключено хранилищем запросов

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

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Отключение принудительного применения оптимизированного плана для запроса

В следующем примере отключается оптимизированное выполнение плана для запроса с помощью DISABLE_OPTIMIZED_PLAN_FORCINGуказания запроса.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

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

• sys.query_store_plan (Transact-SQL)
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• Интеллектуальная обработка запросов в базах данных SQL