Два дополнительных элемента управления при включённой автоматической коррекции планов

Автор: Erin Stellato (MICROSOFT), Two additional controls with Automatic Plan Correction enabled;

Несколько недель назад я опубликовала свой первый «Пятничный отзыв» о Хранилище запросов (Query Store), и один из ответов ссылался на хранимую процедуру, которую я раньше не использовала: sp_configure_automatic_tuning. Было высказано предположение, что эта хранимая процедура не документирована и работает не так, как ожидалось. Зная, как сильно я люблю Хранилище запросов, автоматическую коррекцию планов и документацию, я отправилась на поиски. Если вы не знакомы с этой хранимой процедурой, читайте дальше.

Краткое содержание

Хорошие новости! Хранимая процедура sp_configure_automatic_tuning полностью документирована и поддерживается.

Что делает sp_configure_automatic_tuning?

Если вы включили автоматическую коррекцию планов (Automatic Plan Correction, APC) для своей базы данных, sp_configure_automatic_tuning даёт вам детальный контроль на уровне отдельных запросов. Не уверены, включена ли APC для вашей базы данных? Выполните:

USE [dbname];
GO

SELECT *
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN'
                AND actual_state = 1;
GO

С включённой APC вы можете использовать sp_configure_automatic_tuning для:

• Исключения конкретных запросов из мониторинга APC
• Применения расширенной, основанной на времени проверки регрессии плана для конкретных запросов

Зачем могут понадобиться эти параметры

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

FORCE_LAST_GOOD_PLAN OFF

У вас может быть сложный запрос (или небольшой набор запросов), который имеет достаточную вариативность при выполнении, что заставляет APC обнаружить регрессию и принудительно зафиксировать план. После принудительной фиксации APC отслеживает производительность этого плана и может обнаружить, что план регрессирует (из-за вариативности выполнения), и затем отменить его фиксацию. Повторяющиеся действия по фиксации и отмене фиксации могут быть более проблематичными, чем просто позволить происходить естественной вариативности выполнения.

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

Чтобы отключить APC для данного плана, получив query_id (например, 422), просто выполните:

EXECUTE sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN', 'QUERY', 422, 'OFF';

FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK ON

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

Этот сценарий более подробно описан в статье Дерека от апреля: Query plan regressions got you down? Here's how Automatic Plan Correction can turn it around, и если вы используете APC, я рекомендую найти время, чтобы прочитать его (возможно, даже дважды, потому что там так много отличной информации).

Чтобы расширить проверку для данного запроса (например, 537), выполните:

EXECUTE sys.sp_configure_automatic_tuning 'FORCE_LAST_GOOD_PLAN_EXTENDED_CHECK', 'QUERY', 537, 'ON';

Вы также можете включить эту возможность глобально с помощью флага трассировки 12656 (SQL 2022 CU4 и новее).

Проверка запросов с любой включённой опцией

Чтобы увидеть, для каких запросов включена та или иная опция, используйте представление sys.database_automatic_tuning_configurations:

SELECT * FROM sys.database_automatic_tuning_configurations;

На всякий случай, если вы не посетили документацию 😉

Хранимая процедура sp_configure_automatic_tuning доступна в:

• SQL Server 2022 и новее
• Azure SQL Database
• Azure SQL Managed Instance
• База данных SQL в Microsoft Fabric

Призыв к обратной связи

Если мы не встречались раньше 👋, ранее я была менеджером по продукту для SQL Server Management Studio (SSMS), и в этой роли я регулярно напоминала людям в статьях блога о необходимости оставлять отзывы для SSMS. Хотя я сменила команду, никого не удивит, что я буду просить людей оставлять отзывы о Хранилище запросов, автоматической коррекции планов и обо всём, что связано с обработкой запросов, на сайте обратной связи по SQL. Если вы отфильтруете элементы в группе Хранилища запросов, вы увидите несколько моих недавних комментариев. К сожалению, на сайте обратной связи нет хорошего способа отмечать тех, кто изначально опубликовал сообщение или оставил комментарий. Я намерена найти лучшее решение (пожалуйста, наберитесь терпения), но пока, если вы что-то публикуете, пожалуйста, старайтесь периодически заглядывать, чтобы проверить, нет ли дополнительных вопросов. И, как всегда, не стесняйтесь находить время, чтобы просмотреть существующие запросы и голосовать за те, которые вам близки. Мы хотим услышать вас... о Хранилище запросов, APC и о том, что ещё создаёт проблемы в области ядра SQL. Спасибо за чтение!