Принудительное применение оптимизированного плана с помощью хранилища запросов
Область применения: SQL Server 2022 (16.x)
Оптимизация запросов — это многоэтапный процесс создания "достаточно хорошего" плана выполнения запроса. В некоторых случаях компиляция запросов, которая является частью оптимизации запросов, может занимать большую долю общего времени выполнения запроса и потреблять значительное количество системных ресурсов. Принудительную обработку оптимизированных планов является частью семейства функций интеллектуальной обработки запросов. Принудительная настройка плана снижает затраты на компиляцию для повторения принудительных запросов и требует включения хранилища запросов и в режиме чтения записи. После создания плана выполнения запроса этапы компиляции сохраняются для повторного использования в виде сценария воспроизведения оптимизации. Сценарий воспроизведения оптимизации хранится как часть сжатого XML-файла Showplan в хранилище запросов в скрытом атрибуте OptimizationReplay.
Реализация принудительного применения оптимизированного плана
Когда запрос впервые проходит процесс компиляции, пороговое значение, основанное на оценке времени, затраченного на оптимизацию (на основе дерева входных данных оптимизатора запросов), определяет, будет ли создан сценарий воспроизведения оптимизации.
После завершения компиляции несколько метрик среды выполнения становятся доступными для оценки правильности предыдущей оценки. Если будет подтверждено, что пороговое значение превышено, сценарий воспроизведения оптимизации получает право на сохранение. Эти метрики среды выполнения включают количество объектов, к которым осуществляется доступ, количество соединений, количество задач оптимизации, выполненных во время оптимизации, и фактическое время оптимизации.
Потенциальное преимущество использования сценария воспроизведения оптимизации также сравнивается с затратами на его хранение. Оценка относительного времени воспроизведения сценария воспроизведения оптимизации сравнивается со временем, затраченным на выполнение нормального процесса оптимизации, на основе количества задач оптимизации, хранящихся в сценарии, и количества задач оптимизации, выполняемых во время обычной компиляции. Если при повторном воспроизведении сценария воспроизведения оптимизации значительно снижается время компиляции, он сохраняется.
Рекомендации
Критерии для принудительного применения оптимизированного плана, когда эта функция включена:
- Допустимы только планы запросов, которые проходят полную оптимизацию, что можно проверить наличием свойства
StatementOptmLevel="FULL". - Инструкции с указанием 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).
Примеры
О. Включение хранилища запросов и принудительного применения оптимизированного плана для базы данных
Следующий код включает хранилище запросов и принудительное применение оптимизированного плана для базы данных. Дополнительные сведения о включении хранилище запросов см. в параметрах ALTER DATABASE SET (Transact-SQL).
Перед выполнением кода подключитесь к соответствующей пользовательской базе данных.
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 параметр передается в качестве третьего значения параметра при попытке отключить оптимизированный план принудительно в хранилище запросов. Это можно использовать для отключения оптимизированного плана для конкретного принудительного плана в определенной реплике. Значение 1 — @replica_group_id=1 будет использоваться для отключения функции на первичной реплике.
EXEC 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 (Transact-SQL).
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. В примере используется образец базы данных AdventureWorks.
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
Далее
Дополнительные сведения о хранилище запросов и принудительном применении оптимизированного плана см. в следующих статьях:
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по