Поделиться через

Автоматическая настройка

Применимо к: SQL Server 2017 (14.x) и более поздних версий Azure SQL DatabaseAzure SQL Управляемый экземпляр SQL база данных в Microsoft Fabric

Автоматическая настройка — это функция базы данных, которая предоставляет аналитические сведения о потенциальных проблемах производительности запросов, рекомендует решения и автоматически исправляет выявленные проблемы.

Автоматическая настройка, представленная в SQL Server 2017 (14.x), уведомляет вас при обнаружении потенциальной проблемы с производительностью и позволяет применять корректирующие действия или автоматически устранять проблемы с производительностью ядра СУБД. Автоматическая настройка SQL Server определяет и устраняет проблемы с производительностью, вызванные регрессией плана выполнения запросов. Автоматическая настройка в Базе данных SQL Azure и базе данных SQL в Microsoft Fabric также создает необходимые индексы и удаляет неиспользуемые индексы. Дополнительные сведения о планах выполнения запросов см. в разделе "Планы выполнения".

Ядро СУБД SQL Server отслеживает запросы, выполняемые в базе данных, и автоматически повышает производительность рабочей нагрузки. Ядро СУБД имеет встроенный механизм аналитики, который может автоматически настраивать и улучшать производительность запросов, динамически адаптируя базу данных к рабочей нагрузке. Доступны две функции автоматической настройки:

  • Автоматическое исправление плана определяет проблемные планы выполнения запросов, такие как проблемы с чувствительностью параметров или анализом параметров, и устраняет проблемы с производительностью, принудительно выбирая последний известный рабочий план перед возникновением регрессии. Область применения: SQL Server (начиная с SQL Server 2017 (14.x)), Базы данных SQL Azure и базы данных SQL в Microsoft Fabric и Управляемом экземпляре SQL Azure

  • Автоматическое управление индексами определяет индексы, которые следует добавлять в базу данных, и индексы, которые следует удалить. Область применения: База данных SQL Azure и база данных SQL в Microsoft Fabric

Note

В этой статье функции и поведение базы данных SQL Azure также применяются к базе данных SQL в Microsoft Fabric.

Почему автоматическая настройка?

Три основных задачи в классическом администрировании баз данных — мониторинг рабочей нагрузки, определение критически важных запросов Transact-SQL и определение индексов, которые следует добавить для повышения производительности или индексов, которые редко используются и могут быть удалены для повышения производительности. Ядро СУБД SQL Server содержит подробные сведения о запросах и индексах, которые необходимо отслеживать. Однако постоянное наблюдение за базой данных является сложной и неумной задачей, особенно при работе с многими базами данных. Управление огромным количеством баз данных может оказаться невозможным для эффективного выполнения. Вместо мониторинга и настройки базы данных вручную можно делегировать некоторые действия по мониторингу и настройке ядра СУБД с помощью функции автоматической настройки.

Как работает автоматическая настройка?

Автоматическая настройка — это непрерывный процесс мониторинга и анализа, который постоянно изучает характеристики рабочей нагрузки и определяет потенциальные проблемы и улучшения.

Процесс автоматической настройки.

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

Автоматическое исправление плана

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

Important

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

Что такое регрессия выбора плана выполнения?

Ядро СУБД SQL Server может использовать различные планы выполнения для выполнения Transact-SQL запросов. Планы запросов зависят от статистики, индексов и других факторов. Оптимальный план, который следует использовать для выполнения запроса Transact-SQL, может меняться с течением времени в зависимости от изменений в этих факторах. В некоторых случаях новый план может быть не лучше предыдущего, и новый план может привести к регрессии производительности, например чувствительности параметров или дозированию параметров.

Регрессия выбора плана выполнения запросов.

Всякий раз, когда вы заметите регрессию выбора плана, вы должны найти предыдущий хороший план и принудительно использовать его вместо текущего. Это можно сделать с помощью sp_query_store_force_plan процедуры. Ядро СУБД в SQL Server 2017 (14.x) содержит сведения о регрессированных планах и рекомендуемых действиях по исправлению. Кроме того, ядро СУБД позволяет полностью автоматизировать этот процесс и разрешить ядро СУБД устранить любую проблему, связанную с изменением плана.

Important

Автоматическое исправление плана должно использоваться в области обновления уровня совместимости базы данных после записи базового плана для автоматического снижения рисков обновления рабочей нагрузки. Дополнительные сведения об этом варианте использования см. в статье "Сохранение стабильности производительности во время обновления до более нового SQL Server".

Автоматическая коррекция выбора плана

Ядро СУБД может автоматически переключаться на последний известный хороший план при обнаружении регрессии выбора плана.

Исправление выбора плана выполнения запросов.

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

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

Note

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

Включение автоматического исправления выбора плана

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

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

После включения этой опции СУБД автоматически принудит любую рекомендацию, если предполагаемый выигрыш времени использования ЦП превышает 10 секунд или количество ошибок в новом плане больше, чем в рекомендуемом плане, и проверит, что принудительный план лучше текущего.

Чтобы включить автоматическую настройку в Базе данных SQL Azure и Управляемом экземпляре SQL Azure, см. статью "Включить автоматическую настройку в Базе данных SQL Azure" с помощью портала Azure.

Альтернатива — исправление выбора плана вручную

Без автоматической настройки пользователи должны периодически отслеживать систему и искать запросы, которые регрессировали. Если какой-либо план ухудшился, пользователь должен найти предыдущий хороший план и применить его вместо текущего с помощью процедуры sp_query_store_force_plan. Рекомендуется принудительно применить последний известный рабочий план, так как старые планы могут быть недействительными из-за изменений статистики или индекса. Пользователь, который применяет последний известный хороший план, должен отслеживать производительность запроса, выполняемого с использованием этого плана, и убедиться в том, что он работает должным образом. В зависимости от результатов мониторинга и анализа, план должен быть приведён в действие или усилен, либо пользователь должен найти другой способ оптимизации запроса, например, перезаписать его. Планы, принудительно заданные вручную, не должны быть закреплены навсегда, поскольку СУБД должна иметь возможность применять оптимальные планы. Пользователь или DBA в конечном итоге должен отменить план с помощью sp_query_store_unforce_plan процедуры и позволить ядру СУБД найти оптимальный план.

Tip

В качестве альтернативы используйте представление "Запросы с принудительными планами Query Store", чтобы найти и снять принудительность планов.

SQL Server предоставляет все необходимые представления и процедуры, необходимые для мониторинга производительности и устранения проблем в хранилище запросов.

В SQL Server 2016 (13.x) можно найти регрессии выбора плана с помощью системных представлений хранилища запросов. Начиная с SQL Server 2017 (14.x), ядро СУБД обнаруживает и показывает потенциальные регрессии выбора плана и рекомендуемые действия, которые следует применять в динамическом административном представлении sys.dm_db_tuning_recommendations (Transact-SQL). В представлениях динамического управления (DMV) предоставляется информация о проблеме, её важности и детали, такие как идентифицированный запрос, идентификатор регрессированного плана, идентификатор плана, использованного в качестве базы для сравнения, и инструкция Transact-SQL, которую можно выполнить для устранения проблемы.

type description datetime score details ...
FORCE_LAST_GOOD_PLAN Время ЦП изменилось с 4 мс на 14 мс 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Время ЦП изменилось с 37 мс на 84 мс 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Некоторые столбцы из этого представления описаны в следующем списке:

  • Тип рекомендуемого действия FORCE_LAST_GOOD_PLAN.
  • Описание, содержащее сведения о том, почему ядро СУБД считает, что это изменение плана является потенциальной регрессией производительности.
  • Дата и время обнаружения потенциальной регрессии.
  • Оценка этой рекомендации.
  • Сведения о таких проблемах, как идентификатор обнаруженного плана, идентификатор регрессированного плана, идентификатор плана, который должен быть вынужден устранить проблему, Transact-SQL скрипт, который может применяться для устранения проблемы и т. д. Сведения хранятся в формате JSON.

Используйте следующий запрос, чтобы получить скрипт, который исправляет проблему и дополнительные сведения о предполагаемом выигрыше:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Вот результат.

reason score script query_id текущий идентификатор плана рекомендуемый plan_id предполагаемая_прибыль склонный к ошибкам
Время ЦП изменилось с 3 мс на 46 мс 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

Столбец estimated_gain представляет предполагаемое количество секунд, которое будет сохранено, если рекомендуемый план будет использоваться для выполнения запроса вместо текущего плана. Рекомендуемый план должен быть принудительно применен, иначе текущий план, если выигрыш превышает 10 секунд. Если в текущем плане есть больше ошибок (например, тайм-ауты или прерывание исполнения), чем в рекомендуемом плане, столбец error_prone будет иметь значение YES. План, подверженный ошибкам, является дополнительной причиной, по которой рекомендуемый план должен быть применён вместо текущего.

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

Note

Данные в sys.dm_db_tuning_recommendations динамическом административном представлении не сохраняются после перезапуска ядра СУБД. Узнать время последнего запуска ядра СУБД можно в столбце sqlserver_start_time из sys.dm_os_sys_info.

Автоматическое управление индексами

В Базе данных SQL Azure управление индексами легко, так как база данных SQL Azure узнает о рабочей нагрузке и гарантирует, что данные всегда оптимально индексированы. Правильное проектирование индекса имеет решающее значение для оптимальной производительности рабочей нагрузки, а автоматическое управление индексами помогает оптимизировать индексы. Автоматическое управление индексами может устранять проблемы с производительностью неправильно индексированных баз данных или поддерживать и улучшать индексы существующей схемы базы данных. Автоматическая настройка в Базе данных SQL Azure выполняет следующие действия:

  • Определяет индексы, которые могут повысить производительность запросов Transact-SQL, считывающих данные из таблиц.
  • Определяет избыточные индексы или индексы, которые не использовались в течение длительного периода времени, которые можно удалить. Удаление ненужных индексов повышает производительность запросов, которые обновляют данные в таблицах.

Почему требуется управление индексами?

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

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

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

Автоматическое управление индексами

Помимо обнаружения база данных SQL Azure может автоматически применять определенные рекомендации. Если вы обнаружите, что встроенные правила повышают производительность базы данных, вы можете позволить Базе данных SQL Azure автоматически управлять индексами.

Если база данных SQL Azure применяет рекомендацию CREATE INDEX или DROP INDEX, она автоматически отслеживает производительность запросов, затронутых индексом. Новый индекс будет сохранен только в том случае, если производительность затронутых запросов улучшается. Удаленный индекс будет автоматически создан повторно, если существуют некоторые запросы, которые выполняются медленнее из-за отсутствия индекса.

Рекомендации по автоматическому управлению индексами

Действия, необходимые для создания необходимых индексов в базе данных SQL Azure, могут использовать ресурсы и временно влиять на производительность рабочей нагрузки. Чтобы свести к минимуму влияние создания индекса на производительность рабочей нагрузки, база данных SQL Azure находит соответствующее время для любой операции управления индексами. Действие настройки откладывается, если база данных нуждается в ресурсах для выполнения рабочей нагрузки и перезапускается, когда база данных имеет достаточно неиспользуемых ресурсов, которые можно использовать для задачи обслуживания. Одной из важных функций автоматического управления индексами является проверка действий. Когда база данных SQL Azure создает или удаляет индекс, процесс мониторинга анализирует производительность рабочей нагрузки, чтобы убедиться, что действие улучшило общую производительность. Если это не принесло значительного улучшения - действие немедленно отменяется. Таким образом, база данных SQL Azure гарантирует, что действия автоматической настройки не влияют на производительность рабочей нагрузки. Индексы, созданные автоматической оптимизацией, прозрачны для поддерживающих операций в базовой схеме. Изменения схемы, такие как удаление или переименование столбцов, не блокируются наличием автоматически созданных индексов. Индексы, автоматически созданные базой данных SQL Azure, немедленно удаляются при удалении связанной таблицы или столбцов.

Альтернатива — ручное управление индексами

Без автоматического управления индексами пользователю или DBA потребуется вручную запросить представление sys.dm_db_missing_index_details (Transact-SQL) или использовать отчет панели мониторинга производительности в Management Studio для поиска индексов, которые могут повысить производительность, создать индексы с помощью сведений, предоставленных в этом представлении, и вручную отслеживать производительность запроса. Чтобы найти индексы, которые следует удалить, пользователи должны отслеживать статистику использования операционных индексов, чтобы найти редко используемые индексы.

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

Дальнейшие шаги

  • Last updated on