SQL-Ex blog

Под капотом

Первое, важно понимать, что автоматическая корректировка плана использует хранилище запросов (Query Store), поэтому оно должно быть доступно для базы данных. Второе, автоматическая корректировка плана есть просто автоматическое навязывание плана. В то время как Query Store подается как регистратор "разбора полетов" для вашей базы данных, который отслеживает текст запроса, планы, статистику выполнения и статистику ожидания, он также позволяет вам навязать план запросу с целью обеспечить лучшую производительность. Автоматическая корректировка плана представляет собой навязывание плана без вашего участия.

Включение Automatic Plan Correction

Как уже говорилось, сначала следует включить Query Store для пользовательской базы данных. Это можно сделать в SSMS, с помощью T-SQL или с помощью REST API для Azure SQL DB. Заметим, что Query Store включено по умолчанию для баз данных в Azure и доступно с 2016 Q4.

Включение Query Store в SSMS

USE [master];

GO

ALTER DATABASE [WideWorldImporters] 

	SET QUERY_STORE = ON;

GO

ALTER DATABASE [WideWorldImporters] 

	SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

GO

Включение QUERY STORE с помощью T-SQL

Вышеприведенный код - это код T-SQL, полученный скриптованием по умолчанию в SSMS. В Azure SQL Database оператор USE не выполняется. Если вы хотите изменить любую из опций, принимаемых по умолчанию, прочитайте об этих опциях в моей статье Query Store Settings.

Когда QUERY STORE включено, для включения Automatic Plan Correction в Azure SQL Database вы можете использовать Azure Portal, T-SQL, или REST API (также работает C# и PowerShell). В SQL Server 2017 доступен только вариант с T-SQL.

Включение Automatic Plan Correction на Azure Portal

ALTER DATABASE [WideWorldImporters] 

	SET AUTOMATIC_TUNING (

		FORCE_LAST_GOOD_PLAN = ON

	);

GO

Включение Automatic Plan Correction на T-SQL

Заметим, что автоматическая корректировка плана будет включена по умолчанию для новых баз данных в Azure в ближайшем будущем. Начиная с января 2018, Automatic Tuning включается для баз данных SQL Azure, для которых она еще не включена, с помощью уведомлений, отправляемых администраторам, поэтому этот параметр можно отключить при желании.

Как это работает

Когда опция Automatic Plan Correction включена, SQL Server мониторит производительность запросов, используя данные из Query Store. Он ищет существенные изменения^* в производительности CPU^** в пределах 48-часового окна ^***. Обратите внимание на звездочки в предложении; они означают:

• ^*Порог, который означает существенные изменения, не документирован, поскольку Майкрософт имеет право изменить его.


• ^**Метрика, используемая для определения изменения производительности (CPU) не документирована, поскольку Майкрософт имеет право изменить его. Подразумевая, что Майкрософт может рассмотреть дополнительные метрики для взгляда на производительность, если они окажутся лучше (работать лучше), чем только CPU.


• ^***Период времени, в течение которого сравниваются данные производительности запросов, не документированы по той же причине, что Майкрософт может изменить его.


• Замечание. Поскольку упомянутые выше пункты не документированы, важно понимать, что значения не зафиксированы и могут измениться с целью улучшения надежности этой функциональности.

Важно иметь в виду, что в терабайтах информации телеметрии, которые Майкрософт получает ежедневно из SQL Azure Databases для обработки, содержатся данные типа query_id, query_plan_id и query_hash, и Майкрософт НЕ получает query_text or query_plan (т.е. они не просматривают ваши реальные данные). Майкрософт не просто архивирует операционную телеметрию или использует ее для поиска проблем, данные подвергаются интеллектуальной обработке для разработки алгоритмов и моделей, которые позволили бы SQL Server принимать независимые интеллектуальные решения.

SQL Server может получить выгоду от данных в Query Store, которые детализируют производительность запросов, и автоматическая коррекция плана начинает со сравнения текущей производительности запроса с прошлой производительностью для оценки ее падения. Упала ли производительность, стала ли хуже, и если так, то насколько существенно?

Если имеет место падение производительности запроса, SQL Server будет навязывать последний известный хороший план этому запросу, который, конечно, вытаскивается из Query Store. Но сервер на этом нет останавливается. SQL Server продолжает мониторить производительность - по-прежнему используя Query Store - для подтверждения того, что навязанный план по-прежнему хорош для этого запроса, подразумевая, что запрос с навязанным планом выполняется лучше, чем версия, вызвавшая падение производительности. Если этот запрос не оказывается лучше, он будет отвязан от плана. План также может быть отвязан в случае перекомпиляции, или если навязывание не сработает.

Этот цикл продолжается; если запрос имеет навязанный план, а затем план был отвязан по одной из упомянутых причин, тот же самый план позже может быть навязан опять, или же может быть навязан другой план для этого запроса. Это непрерывный процесс, который продолжается, покуда для базы данных установлена опция Automatic Plan Correction. Интересно то, что вы можете ту же информацию использовать для навязывания плана вручную. Т.е. в SQL Server 2017 Enterprise Edition и в Azure SQL Database эти данные собраны в динамическом представлении sys.dm_db_tuning_recommendations, даже если опция Automatic Plan Correction не включена. Так что вы можете самостоятельно исследовать эти данные и последовать рекомендациям по навязыванию плана для конкретных запросов. Заметьте, что если вы навязываете план, следуя рекомендациям sys.dm_db_tuning_recommendations, то он никогда не будет автоматически отвязан. Более того, если включена опция Automatic Plan Correction, и вы вручную навязываете план, он никогда не будет автоматически отвязываться. Автоматически отвязываются только те планы, которые навязаны в режиме Automatic Plan Correction.

Следует ли передавать контроль SQL Server?

Если вы скептически настроены и не уверены, следует ли доверить SQL Server принятие решения по навязыванию плана, я бы посоветовал вам иметь в виду, что:

1. Эта функциональная возможность была разработана на основе огромного количества данных, собранных с почти двух миллионов баз данных SQL Azure. Это стало новинкой в SQL Server 2017, но в 2016 году она стала доступна в Azure по всему миру, поэтому реально эта функция доступна уже более года, и в течение этого срока совершенствовалась.


2. Инженеры вносили изменения в алгоритм по мере поступления новых данных. Возможно, не каждое падение производительности обнаруживается - поскольку оно может быть незначительным, и я готов поручиться, что многие из вас предпочтут использовать возможность навязывания не столь часто.


3. Помимо всего прочего, если план навязывается, но в конечном итоге вызывает проблему, SQL Server способен надежно и быстро отвязать план.

Заключение

Возможно, вам не нравится идея, что решение проблем производительности SQLK Server берет на себя. Но может быть это происходит оттого, что вы думаете, что будет принято плохое решение? Или вы беспокоитесь, что автоматизация лишит вас работы? Если первое, того мои рекомендации заключаются в том, чтобы посмотреть на собранные данные в sys.dm_db_tuning_recommendations (с выключенной опцией Automatic Plan Correction) и разобраться с тем, что хотел сделать SQL Server. Согласуется ли это с тем, что бы вы предприняли? Не нашел ли он падение производительности, которое вы могли не заметить? Если вы не хотите включать эту функцию, потому что боитесь, что вам вдруг нечего станет делать, я рекомендую прочитать недавнюю статью Конора Каннингема «Как скорость облака помогает администраторам баз данных SQL Server». Microsoft не пытается своим кодом оставить вас без работы. Они просто пытаются справиться с "низко висящими фруктами", чтобы вы могли сосредоточиться на более важных задачах.