Изучение хранилища запросов

  • 13 мин

Хранилище запросов SQL Server — это функция для каждой базы данных, которая автоматически записывает журнал запросов, планов и статистики среды выполнения, упрощая устранение неполадок с производительностью и настройку запросов. Он также предоставляет аналитические сведения о шаблонах использования базы данных и использовании ресурсов.

Хранилище запросов состоит из трех хранилищ:

  • Хранилище планов: хранит предполагаемые сведения о плане выполнения.
  • Хранилище статистики среды выполнения: хранит сведения о статистике выполнения.
  • Хранилище статистики ожидания: сохраняет сведения о статистике ожидания.

Снимок экрана: компоненты хранилища запросов.

включение хранилища запросов;

Хранилище запросов включено по умолчанию в базах данных SQL Azure. Чтобы использовать его с SQL Server и Azure Synapse Analytics, необходимо сначала включить его. Чтобы включить функцию хранилища запросов, используйте следующий запрос, допустимый для вашей среды:

-- SQL Server
ALTER DATABASE <database_name> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Azure Synapse Analytics
ALTER DATABASE <database_name> SET QUERY_STORE = ON;

Сбор данных в хранилище запросов

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

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

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

  3. Во время компиляции и проверки на наличие этапов перекомпиляции хранилище запросов определяет, имеется ли принудительный план выполнения запроса. Запрос перекомпилируется, если хранилище запросов предоставляет принудительный план, отличный от плана в кэше процедур.

  4. При выполнении запроса статистика среды выполнения сохраняется в хранилище запросов. Хранилище запросов объединяет эти данные, чтобы обеспечить точное представление каждого плана запроса.

Снимок экрана: точки интеграции хранилища запросов в конвейере выполнения запросов, отображаемые в виде блок-диаграммы.

Дополнительные сведения о сборе данных в хранилище запросов см. в статье о сборе данных в хранилище запросов.

Распространенные сценарии

Хранилище запросов SQL Server предоставляет ценные сведения о производительности операций базы данных. Ниже приведены распространенные сценарии.

  • Определение и исправление регрессии производительности из-за низшего выбора плана выполнения запроса.
  • Определение и настройка запросов наибольшего потребления ресурсов.
  • Тестирование A/B для оценки влияния изменений базы данных и приложений.
  • Обеспечение стабильности производительности после обновления SQL Server.
  • Определение наиболее часто используемых запросов.
  • Аудит истории планов запросов для конкретного запроса.
  • Определение и улучшение незапланированных рабочих нагрузок.
  • Общие сведения о распространенных категориях ожидания базы данных и запросах и планах, влияющих на время ожидания.
  • Анализ шаблонов использования базы данных с течением времени с точки зрения потребления ресурсов (ЦП, операций ввода-вывода, памяти).

Обнаружение представлений хранилища запросов

После включения хранилища запросов в базе данных папка хранилища запросов отображается для базы данных в обозревателе объектов. Для Azure Synapse Analytics представления хранилища запросов отображаются в списке системных представлений. В представлениях хранилища запросов содержатся агрегированные, краткие аналитические сведения об аспектах производительности базы данных SQL Server.

Снимок экрана: обозреватель объектов S S M S с выделенными представлениями хранилища запросов.

Регрессированные запросы

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

Представление " Регрессированные запросы" может указывать запросы , метрики выполнения которых регрессивны из-за изменений плана выполнения в течение указанного интервала времени. Это представление позволяет фильтровать на основе выбранной метрики (например, длительности, времени ЦП, количества строк и многого другого) и статистики (общее, среднее, минимальное, максимальное или стандартное отклонение). Затем в нем перечислены первые 25 регрессированных запросов на основе предоставленного фильтра. По умолчанию отображается графическое представление линейчатой диаграммы запросов, но при необходимости можно просматривать запросы в формате сетки.

После выбора запроса в левой верхней части области запросов панель сводки плана отображает сохраненные планы запросов, связанные с запросом с течением времени. При выборе плана запроса на панели "Сводка плана" отображается графический план запроса на нижней панели. Кнопки панели инструментов в области сводки плана и области графического плана запросов позволяют принудительно применять выбранный план для выбранного запроса. Эта структура и поведение панели последовательно используются во всех представлениях SQL Query.

Снимок экрана: представление регрессированных запросов хранилища запросов, отображающее каждую из разных областей.

Кроме того, можно использовать хранимую процедуру sp_query_store_force_plan для принудительного применения плана.

EXEC sp_query_store_force_plan @query_id=73, @plan_id=79

Общее потребление ресурсов

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

Снимок экрана: представление общего потребления ресурсов хранилища запросов SQL с диалоговым окном конфигурации, указывающим различные метрики, доступные для отображения.

В представлении сведений указаны первые 25 запросов потребителей ресурсов, которые связаны с выбранной метрикой. В этом представлении сведений используется согласованный интерфейс, позволяющий проверять связанные запросы и их сведения, оценивать сохраненные предполагаемые планы запросов и при необходимости использовать план принудительно для повышения производительности. Это представление полезно, когда состязание за системные ресурсы становится проблемой, например, когда загрузка ЦП достигает предельного ограничения.

Снимок экрана: 25 наибольших потребителей ресурсов для базы данных.

Основные запросы, потребляющие ресурсы

Представление "Основные запросы, потребляемые ресурсами", аналогично детализации представления "Общее потребление ресурсов". В нем также можно выбрать метрику и статистику в качестве фильтра. Однако отображаемые запросы — это 25 наиболее ресурсоемких запросов на основе выбранного фильтра и интервала времени.

Снимок экрана: представление основных запросов, потребляющих ресурсы для базы данных.

Представление "Основные запросы, потребляемые ресурсами" , предоставляет первое представление о незапланированной природе рабочей нагрузки при выявлении и улучшении незапланированных рабочих нагрузок. Например, на следующем рисунке выбраны метрика Количество выполнений и статистика Общее, чтобы показать, что примерно 90 [%] запросов, потребляющих основную часть ресурсов, выполняются только один раз.

Снимок экрана запросов, ресурсоемких по количеству выполнения.

Запросы с принудительными планами

Представление "Запросы с принудительными планами " позволяет быстро изучить запросы, которые имеют принудительные планы запросов. Это представление становится актуальным, если план принудительного выполнения больше не выполняется должным образом и должен быть пересмотрен. Это представление позволяет просмотреть все сохраненные предполагаемые планы выполнения для выбранного запроса, чтобы с легкостью определить, подходит ли другой план для повышения производительности. В этом случае на панели инструментов доступны кнопки для отмены принудительного выполнения плана по мере необходимости.

Снимок экрана запросов с принудительными планами.

Запросы с высокой вариативностью

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

Снимок экрана: запросы с большой вариативностью.

Статистика ожидания запросов

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

Снимок экрана: запросы с высоким представлением вариантов.

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

Отслеживание запроса

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

Снимок экрана: фильтрация представления запроса отслеживания по определенному идентификатору запроса.

Поиск ожиданий запросов с помощью хранилища запросов

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

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

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

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

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

Снимок экрана: представление статистики ожидания запросов, отображающее наиболее важные запросы для категории ожидания.

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

В SQL Server 2017 и базе данных SQL Azure представлена концепция автоматического исправления плана путем анализа данных в хранилище запросов. Если хранилище запросов включено в базе данных в SQL Server 2017 (или более поздней версии) и в базе данных SQL Azure, подсистема SQL Server будет искать регрессии плана запроса и предоставлять рекомендации. Эти рекомендации можно просмотреть в динамическом sys.dm_db_tuning_recommendations представлении управления (DMV). Эти рекомендации включают инструкции T-SQL, позволяющие вручную установить план запроса на момент, когда производительность была хорошей.

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

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