Query Store в SQL Server: как найти медленный запрос

Чтобы найти медленный запрос в SQL Server 2022, начинайте с Query Store. Для новых баз он включён по умолчанию, записывает планы и статистику выполнения каждого запроса почасовыми интервалами и переживает перезапуски, так что у вопроса «что стало медленным вчера после обеда» есть ответ. Откройте в SSMS отчёт Top Resource Consuming Queries, отсортируйте по общему CPU или по логическим чтениям, и запрос наверху почти всегда и есть тот, который нужно исправлять. Для того, что медленно прямо сейчас, - запроса, выполняющегося в эту минуту, сеанса, блокирующего других, - инструмент другой: динамические административные представления (sys.dm_exec_requests и компания). Это руководство разбирает и то и другое, а также что делать с запросом, когда вы его нашли.

Всё здесь работает в редакции Express. Query Store есть во всех редакциях, и в Express он важнее, чем где-либо ещё: при четырёх ядрах и примерно 1,4 ГБ buffer pool один плохой запрос забирает большую долю машины.

Что записывает Query Store#

Query Store живёт внутри каждой пользовательской базы и для каждого запроса, который решит отслеживать, сохраняет:

• Текст запроса и его хеш, так что одно и то же выражение из разных сеансов считается одним запросом.
• Каждый план, который оптимизатор построил для этого запроса, вместе с XML плана.
• Статистику выполнения по каждому плану за каждый интервал: число выполнений, длительность, время CPU, логические и физические чтения, записи, выделение памяти, число строк - каждое в виде среднего, минимума, максимума и стандартного отклонения.
• Статистику ожиданий по каждому плану за каждый интервал (начиная с SQL Server 2017), сгруппированную в категории вроде CPU, Buffer IO, Lock и Memory.

Поскольку Query Store хранит планы во времени, он отвечает на вопрос, на который кэш планов ответить не может: «на прошлой неделе этот запрос был быстрым, что изменилось?». Кэш планов держит только то, что закэшировано сейчас, теряет всё при перезапуске и вытесняет записи при нехватке памяти - а на экземпляре Express это происходит постоянно.

Данные хранятся в самой базе. Они уходят вместе с базой в backup, возвращаются при восстановлении и - в Express - засчитываются в лимит 10 ГБ на базу. Последнее определяет, как его настраивать.

Включение и размер#

Сначала проверьте состояние:

SELECT actual_state_desc, desired_state_desc, readonly_reason,       current_storage_size_mb, max_storage_size_mb,       query_capture_mode_desc, interval_length_minutesFROM sys.database_query_store_options;

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

ALTER DATABASE [appdb] SET QUERY_STORE = ON (    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,    MAX_STORAGE_SIZE_MB = 300,    INTERVAL_LENGTH_MINUTES = 60,    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),    SIZE_BASED_CLEANUP_MODE = AUTO);

На базе Express в 10 ГБ стандартный потолок в 1000 МБ - это десятая часть вашего лимита. 200-500 МБ хватает на месяц истории для типичного приложения. Оставьте режим захвата AUTO: ALL в приложении, которое отправляет непараметризованный SQL, забивает хранилище тысячами разовых запросов.

Если Query Store достигает максимального размера, он сам переключается в режим только для чтения и перестаёт записывать, а readonly_reason объясняет почему. Очистка по размеру должна это предотвращать; если всё-таки случилось, немного поднимите лимит или сократите срок хранения, затем снова установите OPERATION_MODE = READ_WRITE.

Встроенные отчёты#

В SSMS разверните базу, затем Query Store. Отчёты, которые стоят внимания:

В Top Resource Consuming Queries смените метрику со значения по умолчанию на общее время CPU или общее число логических чтений, а не на среднее. Запрос, который длится 4 мс, но выполняется 2 миллиона раз в час, стоит гораздо больше, чем 3-секундный отчёт, запускаемый дважды в день, а средние значения это скрывают. График справа показывает каждый план, который использовал выбранный запрос, по цвету на план; запрос с двумя планами, быстрым и медленным, - самый явный признак проблемы с планом, а не с индексами.

Поиск медленного запроса через T-SQL#

Отчёты - это представления поверх нескольких системных представлений каталога, к которым можно обращаться напрямую, - удобно на сервере, где у вас есть только sqlcmd, или чтобы сохранить результаты:

SELECT TOP (20)    q.query_id,    SUM(rs.count_executions)                                AS executions,    SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0     AS total_cpu_ms,    SUM(rs.avg_duration * rs.count_executions) / 1000.0     AS total_duration_ms,    SUM(rs.avg_logical_io_reads * rs.count_executions)      AS total_reads,    MAX(qt.query_sql_text)                                  AS query_textFROM sys.query_store_runtime_stats AS rsJOIN sys.query_store_runtime_stats_interval AS i    ON i.runtime_stats_interval_id = rs.runtime_stats_interval_idJOIN sys.query_store_plan AS p        ON p.plan_id = rs.plan_idJOIN sys.query_store_query AS q       ON q.query_id = p.query_idJOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_idWHERE i.start_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET())GROUP BY q.query_idORDER BY total_cpu_ms DESC;

Время в Query Store хранится в микросекундах, отсюда деление на 1000. Поменяйте ORDER BY на total_reads, чтобы найти запросы, читающие больше всего страниц, - на экземпляре с ограниченной памятью именно они вытесняют из кэша всё остальное.

Ожидания говорят, почему запрос медленный, а не просто что он медленный:

SELECT TOP (20) p.query_id, ws.wait_category_desc,       SUM(ws.total_query_wait_time_ms) AS wait_msFROM sys.query_store_wait_stats AS wsJOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_idGROUP BY p.query_id, ws.wait_category_descORDER BY wait_ms DESC;

Ожидания CPU означают, что запрос выполняет работу, - исправляйте лучшим планом или индексом. Buffer IO означает, что он читает страницы с диска, потому что их не было в памяти. Lock - что его блокирует другой сеанс. Memory - что он ждёт выделения памяти, часто потому, что неверная оценка запросила слишком много.

Что медленно прямо сейчас: DMV#

Query Store смотрит назад. Если проблема происходит в эту минуту, спросите у движка, чем он занят:

SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time,       r.blocking_session_id, r.cpu_time, r.logical_reads,       r.total_elapsed_time / 1000 AS elapsed_s,       t.text AS sql_textFROM sys.dm_exec_requests AS rCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS tWHERE r.session_id <> @@SPID  AND r.session_id > 50ORDER BY r.total_elapsed_time DESC;

Ненулевой blocking_session_id означает, что запрос ждёт блокировку, удерживаемую этим сеансом. Пройдите по цепочке до сеанса в её начале - того, который блокирует других, но сам не заблокирован, - и посмотрите, что он выполняет или что выполнил и так и не зафиксировал. sys.dm_exec_sessions даёт его логин, хост и имя программы. Длинные цепочки блокировок на небольшом сервере обычно сводятся к одной забытой открытой транзакции - тому же виновнику, который не даёт усекаться журналу транзакций, о чём рассказывает статья модели восстановления SQL Server и рост журнала.

Для самых тяжёлых запросов с момента последней очистки кэша планов есть более старая альтернатива Query Store - sys.dm_exec_query_stats:

SELECT TOP (10) qs.execution_count,       qs.total_worker_time / 1000 AS total_cpu_ms,       qs.total_logical_reads,       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,           (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)            ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statementFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tORDER BY qs.total_worker_time DESC;

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

Регрессии планов и закрепление плана#

Самые резкие замедления вызывает не рост данных. Их вызывает переход запроса на другой план - после обновления статистики, перезапуска, изменения индекса или повышения уровня совместимости. Сегодня запрос делает seek, завтра scan, и в коде при этом ничего не менялось.

Query Store делает это видимым и исправимым. В Regressed Queries или Tracked Queries на графике планов точки старого плана находятся внизу, а точки нового - вверху. Выберите хороший план и нажмите Force Plan или сделайте это через T-SQL:

EXEC sp_query_store_force_plan @query_id = 412, @plan_id = 1187;-- Later, once the underlying cause is fixedEXEC sp_query_store_unforce_plan @query_id = 412, @plan_id = 1187;

С этого момента SQL Server использует для этого запроса этот план, пока он остаётся допустимым. Закрепление - это жгут, а не лечение. Оно останавливает кровотечение сегодня, но данные будут продолжать меняться, и план, верный для данных прошлого месяца, через полгода может оказаться неверным. Записывайте каждый закреплённый план, выясняйте, почему оптимизатор выбрал плохой, - устаревшая статистика, недостающий индекс, перекошенный параметр, - и снимайте закрепление, когда это исправлено. SQL Server также умеет автоматически закреплять последний хороший план через функцию автоматической настройки там, где редакция это поддерживает; прежде чем полагаться на это в Express, проверьте.

В SQL Server 2022 появились подсказки Query Store, которые прикрепляют подсказку запроса к идентификатору запроса без изменения кода приложения:

EXEC sys.sp_query_store_set_hints @query_id = 412,     @query_hints = N'OPTION (RECOMPILE)';

Так исправляют запрос, сгенерированный ORM, который нелегко отредактировать. sys.sp_query_store_clear_hints снимает подсказку.

Parameter sniffing и другие обычные причины#

Когда запрос найден, причина обычно из короткого списка:

• Недостающий или бесполезный индекс. План сканирует большую таблицу или делает тысячи key lookup. Как читать план и проектировать индекс, разбирает статья индексы и планы выполнения SQL Server.
• Parameter sniffing. Параметризованный запрос компилируется один раз, под первые увиденные значения параметров, и этот план используется для всех последующих значений. Если первый вызов был для клиента с тремя заказами, а следующий - для клиента с тремя миллионами, повторно используемый план может оказаться ужасным. Это видно в Queries With High Variation. Исправления - от OPTION (RECOMPILE) для нечастого запроса до OPTIMIZE FOR показательного значения или лучшего индекса, подходящего обоим случаям. На уровне совместимости 160 оптимизация планов, чувствительных к параметрам, в SQL Server 2022 может хранить несколько планов для некоторых подходящих запросов, что помогает, но ловит не всё.
• Неявные преобразования. Строковый параметр, отправленный как nvarchar, против столбца varchar, из-за чего индекс по этому столбцу нельзя использовать для seek. Ищите CONVERT_IMPLICIT в плане.
• Устаревшая статистика после крупной загрузки или удаления. Выполните UPDATE STATISTICS dbo.Orders; и сравните.
• Слишком много обращений к базе. Страница, которая выполняет один и тот же маленький запрос 400 раз в цикле. Каждый быстрый, итог - нет. Ранжирование по общему CPU такие ловит, а исправление находится в приложении, а не в базе. Общий метод из статьи EXPLAIN ANALYZE и медленные запросы применим и здесь.

В Express читайте ожидания ещё и с учётом ограничений редакции. Много ожиданий Buffer IO на экземпляре, у которого остаётся много свободной памяти, означает, что рабочий набор превышает потолок buffer pool в 1410 МБ. Больше RAM в тарифе этого не изменит; изменит чтение меньшего числа страниц - за счёт лучших индексов и более узких запросов. Много ожиданий CPU при всех четырёх занятых ядрах - это потолок вычислений, и совет тот же.

Разбор примера: от жалобы до исправления#

Жалоба типичная: «страница заказов тормозит со вторника». Код никто не менял. Вот последовательность, которая примерно за двадцать минут превращает это в исправление.

1. Сузьте окно. Откройте Overall Resource Consumption за последнюю неделю. CPU подскакивает во вторник около 03:00 и остаётся высоким. В этот момент что-то изменилось, а в 03:00 запускается ночное задание статистики.
2. Найдите запрос. Откройте Top Resource Consuming Queries за последние 48 часов, метрика - общее время CPU. Верхний запрос с большим отрывом - список заказов: SELECT ... FROM dbo.Orders WHERE CustomerId = @p0 ORDER BY CreatedAt DESC. Он выполняется около 30 000 раз в час.
3. Посмотрите на его планы. На графике два плана. План 1187, использовавшийся до вторника, в среднем занимает 2 мс. План 1204, используемый с тех пор, - 180 мс. Это регрессия, а не рост.
4. Сравните планы. План 1187 делает seek по индексу на CustomerId и несколько key lookup. План 1204 сканирует кластерный индекс и сортирует. Оценочное число строк в новом плане - 40 000, фактическое - 12. План скомпилировали после обновления статистики, для вызова с единственным оптовым клиентом, у которого 40 000 заказов, и с тех пор его повторно используют для каждого мелкого клиента. Это parameter sniffing, запущенный перекомпиляцией, которую вызвало обновление статистики.
5. Остановите кровотечение. Закрепите план 1187. CPU падает в течение минуты, и страница снова быстрая.
6. Устраните причину. Старый план тоже был лишь терпимым - ему нужны были key lookup. Создайте индекс на (CustomerId, CreatedAt), включающий столбцы, которые читает страница. С покрывающим индексом seek - лучший план для любого клиента, крупного или мелкого, так что у оптимизатора нет плохого выбора, какое бы значение он ни подсмотрел.
7. Снимите закрепление и проверьте. Снимите закрепление плана 1187. Следите за запросом в Tracked Queries в течение следующего дня: появляется новый план, использующий новый индекс, в среднем меньше миллисекунды, и он сохраняется и после следующего запуска статистики в 03:00.

Эта схема обобщается. Query Store сужает «приложение тормозит» до одного запроса и одного момента; планы объясняют, что изменилось; закрепление выигрывает время; структурное исправление - индекс, переписанный предикат, исправленный тип параметра - убирает причину что-либо закреплять. Пропускают обычно последний шаг, и непересмотренный закреплённый план - это то, из-за чего та же страница снова начинает тормозить через полгода, когда данные ушли вперёд и закреплённый план им больше не подходит.

Пример показывает и то, почему правильное первое ранжирование - по общему CPU, а не по средней длительности. Список заказов ни разу не появлялся ни в одном списке «самых медленных запросов» - 180 мс для одного вызова это не медленно, - но при 30 000 вызовов в час он занимал большую часть сервера.

FAQ#

Замедляет ли Query Store базу?

Накладные расходы невелики - максимум несколько процентов на типичных нагрузках, а обычно меньше. Вредят случаи, когда режим захвата ALL сочетается с потоком непараметризованных запросов, или когда хранилище слишком мало и всё время занято очисткой. Захват AUTO и разумный размер избавляют от обоих.

Есть ли Query Store в SQL Server Express?

Да, во всех редакциях начиная с SQL Server 2016. В SQL Server 2022 он по умолчанию включён для новых баз. В Express держите MAX_STORAGE_SIZE_MB скромным, потому что данные засчитываются в лимит 10 ГБ на базу.

Почему нужного мне запроса нет в Query Store?

В режиме захвата AUTO редкие и дешёвые запросы не захватываются. Кроме того, он может быть в другой базе: Query Store работает на уровне базы, и запрос записывается в той базе, в которой выполнялся. Проверьте, что в тот момент Query Store был в режиме READ_WRITE.

Как очистить данные Query Store?

ALTER DATABASE [appdb] SET QUERY_STORE CLEAR; удаляет всё собранное к этому моменту. Это полезно после крупного изменения приложения, когда старые данные только запутали бы отчёты. Учтите, что так же пропадает история, которая нужна для сравнения до и после.

Стоит ли закреплять планы навсегда?

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