Диспетчер Хранилища Запросов в SQL Server 2016+ (он же Query Store)

Публикация № 1054413

Администрирование - Производительность и оптимизация (HighLoad)

sql server мониторинг запросы оптимизация план запроса

36
Если вы используете SQL Server 2016 или более позднюю версию, то у вас есть возможность использовать встроенную систему мониторинга, которая позволяет отслеживать самые базовые метрики выполняемых запросов и статистику ожиданий (потребления ресурсов). Эта информация позволяет быстро получить самые ресурсоемкие запросы с их планами и агрегированной статистикой выполнения.

Основные преимущества использования Хранилища Запросов

  • встроен в SQL Server и не требует дополнительной установки

  • собирает данные без существенной нагрузки на систему

При этом Хранилище Запросов не заменяет полноценную систему мониторинга, например, RedGate или SenturyOne, но дополняет ее, предоставляя данные с другого ракурса.

 

Негативные стороны:

  • Слегка увеличивает использование процессора. Я бы сказал 2-5% на сильно загруженном сервере. Я бы не стал включать Хранилище Запросов если ваш сервер постоянно использует 80-90% CPU.

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

    • Query Store существенно усугубил ситуацию с обновлением кэша WMI. Проблема появилась и пропала с обновлениями Windows 2016. Эх.. столько ресурсов было растрачено на поиск основной причины… https://dba.stackexchange.com/questions/214818/why-select-query-is-waiting-on-hadr-sync-commit

    • Только пару дней назад процесс на основном сервере упал с дампом из-за бага в коде SQL Server. Переписка с MS уже в процессе, может и исправят.

Где Хранилище Запросов может быть полезно

Хранилище Запросов может быть использовано во многих ситуациях для отслеживания нагрузки на базы данных и идентификации регрессии. Некоторые сценарии:

  • Для идентификации и исправления запросов с деградировавшим планом выполнения

  • Идентификации основных запросов, потребляющих ресурсы сервера.

  • Обеспечения стабильности сервера после обновления на новую версию SQL Server

  • Идентификации и исправления ad-hoc запросов (в основном запросы, которые выполняют бизнес пользователи)

Как включить Хранилище Запросов

 

Активируется Query Store в свойствах базы данных (или через TSQL, конечно).

Основные параметры, на которые стоит обратить внимание:

  • Operation Mode: Read Write - SQL будет собирать и сохранять данные

  • Statistics Collection Interval - зависит от того насколько гранулярно нужны данные. Для критичных серверов и достаточным количеством свободных ресурсов я обычно ставлю 5 минут.

  • Query Store Capture Mode - лучше не ставить All, а поменять на Auto - в этом случае SQL сохранит только значимые запросы и большая часть мелких нечастых запросов будет отсеяна.

  • Stale Query Threshold (Days) - 30 дней мне кажется мало, обычно увеличиваю до года.

Работа с собранными данными.

Для просмотра данных можно воспользоваться TSQL запросами. Примеры приведены здесь - https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017

 

Хотя обычно можно начать с UI. Хранилище Запросов существует в контексте базы данных (не сервера), поэтому данные можно получить даже из копии базы, востановленной на другом сервере.

 

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

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

Справа вверху, список всех планов выполнения для данного запроса и примерный расклад времени выполнения в заданном периоде.

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

При нажатии Configure появляется меню с базовыми настройками. Из него можно также понять какие параметры выполнения запросов собираются.

Я надеюсь, что этого достаточно чтобы понять основную суть инструмента.

А вот как работать с результатами уже индивидуальная история для каждого клиента.

36

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. nvv1970 27.04.19 00:28 Сейчас в теме
Спасибо за статью.
Инструмент действительно хороший.
Было бы интересно услышать читателям, какие из стандартных отчётов наиболее интересны. Какие сценарии работы приходилось использовать. Например, как фиксить планы)
Вот это была бы ещё более ценная информация.

С другой стороны (и от себя лично) всем рекомендую не останавливаться на статье, а обратиться в документацию. Она достаточно подробно разбирает различные моменты, приводит best practics.
2. YPermitin 3939 28.04.19 13:09 Сейчас в теме
(0) Инструмент отличный, просто замечательный :)

С некоторыми собственными дополнениями может достаточно оперативно подсказывать что вообще происходит и куда копать.
Aleksey.Bochkov; +1 Ответить
3. ashvik 06.05.19 15:14 Сейчас в теме
(2) Ну и я так понимаю, сейчас это нарушение лицензионного соглашения?
4. YPermitin 3939 06.05.19 15:47 Сейчас в теме
(3) по идее нет, ведь в базе ничего не изменяется. Но могу ошибаться, т.к. в лицензионном соглашении запрещается использовать недокументированные возможности.

Вообщем, как обычно на свой страх и риск не получить поддержку в случае чего. Вы ведь ей часто пользуйтесь :)
5. ashvik 07.05.19 09:06 Сейчас в теме
(4) Ну и еще момент уточню, после реструктуризации надо опять выставлять это свойство у базы?
6. Aleksey.Bochkov 3215 08.05.19 01:29 Сейчас в теме
(5) нет, реструктуризация не затронет Query Store. А вот удаление базы, создание новой и последующая загрузка из dt сбросит все параметры (редкий сценарий как я понимаю)
7. ALex_1C_8 03.07.19 18:06 Сейчас в теме
Отличная штука. Пользовался ее на продуктиве довольно долго.
Правда есть маленький нюанс. По сути одни и те-жи запросы, при создании временной таблицы, разделяются так как 1с разное название дает.
Оставьте свое сообщение