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

  • Статья

Область применения: SQL Server 2022 (16.x) База данных SQL Azure

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

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

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

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

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

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

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

  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 указания запроса.

Пример применения этого указания запроса в можно найти в Примере Д.

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

Процедура 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.

Примеры

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

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

Следующий код включает хранилище запросов и принудительное применение оптимизированного плана для базы данных. Дополнительные сведения о включении хранилище запросов в параметрах 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

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

В следующем примере отключается оптимизированное выполнение плана для запроса с помощью 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

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