Query Store и планы запросов для базы 1С: находим регресс за 15 минут

«После обновления стало медленно» — жалоба, которую почти невозможно доказать или опровергнуть без истории. Query Store эту историю ведёт: он хранит тексты запросов, их планы и статистику выполнения за выбранный период. Включается за две минуты, а при разборе регресса экономит дни. Разберём, как настроить его под базу 1С и как им реально пользоваться.

До Query Store единственным источником данных о планах был кэш. У него два свойства, которые делают его непригодным для разбора регресса.

Первое: кэш очищается. При перезапуске службы, при нехватке памяти, при обновлении статистики, при реструктуризации таблиц. То есть ровно в те моменты, которые вас интересуют.

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

Query Store устраняет оба ограничения. Он пишет тексты запросов, все их планы и агрегированную статистику выполнения в саму базу данных. Это значит: данные переживают перезапуск, попадают в бэкап и доступны за выбранный период истории.

Практический эффект: на вопрос «что изменилось после обновления» вы отвечаете за пятнадцать минут вместо дня разбирательств, потому что видите оба плана рядом.

Доступен начиная с SQL Server 2016, во всех редакциях, включая Express.

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

Две минуты работы сегодня. Сэкономленная неделя через полгода.

Включается на уровне базы данных, одной командой.

ALTER DATABASE [buh_prod] SET QUERY_STORE = ON;
ALTER DATABASE [buh_prod] SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    MAX_STORAGE_SIZE_MB = 4096,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 30,
    QUERY_CAPTURE_MODE = AUTO,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 45)
);

Разберу параметры, которые важны именно для 1С.

QUERY_CAPTURE_MODE = AUTO — критично. В режиме ALL будет записываться каждый запрос, а платформа 1С генерирует их огромное количество, большая часть из которых выполняется один раз. Хранилище забьётся за сутки. Режим AUTO отсекает редкие и дешёвые запросы.

MAX_STORAGE_SIZE_MB — 2–4 ГБ для обычной базы. Меньше гигабайта смысла нет: не хватит на осмысленную историю.

INTERVAL_LENGTH_MINUTES — интервал агрегации. 30 или 60 минут. Меньше даёт детальнее, но быстрее расходует хранилище.

STALE_QUERY_THRESHOLD_DAYS — глубина истории. 30–45 дней покрывает цикл «обновление конфигурации — обнаружение проблемы».

Самое важное — следить за переходом в READ_ONLY. При заполнении хранилища Query Store перестаёт писать и молча превращается в бесполезный. Проверять так:

SELECT actual_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Эту проверку стоит поставить в мониторинг: обнаружить, что история не пишется, в момент разбора аварии — обидно.

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

В SQL Server Management Studio для этого есть готовый отчёт «Regressed Queries» в разделе Query Store у базы. Он визуальный и для быстрого взгляда удобнее любого скрипта.

Запросом то же самое, если нужен контроль над критериями:

WITH plans AS (
  SELECT q.query_id, p.plan_id,
         SUM(rs.count_executions) AS execs,
         SUM(rs.avg_duration * rs.count_executions) / SUM(rs.count_executions) AS avg_dur
  FROM sys.query_store_query q
  JOIN sys.query_store_plan p ON p.query_id = q.query_id
  JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
  WHERE i.start_time > DATEADD(day, -14, GETUTCDATE())
  GROUP BY q.query_id, p.plan_id
)
SELECT query_id, COUNT(*) AS plan_count,
       MIN(avg_dur) / 1000 AS best_ms, MAX(avg_dur) / 1000 AS worst_ms,
       MAX(avg_dur) / NULLIF(MIN(avg_dur), 0) AS ratio, SUM(execs) AS total_execs
FROM plans
GROUP BY query_id
HAVING COUNT(*) > 1 AND MAX(avg_dur) / NULLIF(MIN(avg_dur), 0) > 3
ORDER BY (MAX(avg_dur) - MIN(avg_dur)) * SUM(execs) DESC;

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

Получив query_id, смотрим текст и оба плана — визуально в студии проще всего.

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

EXEC sp_query_store_force_plan @query_id = 4127, @plan_id = 9053;
-- отменить
EXEC sp_query_store_unforce_plan @query_id = 4127, @plan_id = 9053;

Это мощный инструмент и одновременно способ создать себе проблему на будущее.

Когда уместно. Регресс случился, причина непонятна, пользователи стоят, окна на разбор нет. Форсируете хороший план, снимаете остроту, разбираетесь спокойно.

Чем это плохо как постоянное решение. Данные меняются, объёмы растут, и план, оптимальный сегодня, через полгода может стать плохим. Оптимизатор бы это учёл — форсированный план не учтёт ничего.

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

SELECT q.query_id, p.plan_id, p.is_forced_plan, p.last_force_failure_reason_desc,
       SUBSTRING(qt.query_sql_text, 1, 200) AS txt
FROM sys.query_store_plan p
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE p.is_forced_plan = 1;

Колонка last_force_failure_reason_desc заслуживает внимания. Форсирование может перестать работать — например, если индекс, на который опирался план, исчез при реструктуризации. Тогда SQL Server тихо вернётся к обычному выбору, и вы об этом не узнаете, пока не посмотрите.

В базах 1С такое случается регулярно именно из-за реструктуризации при обновлениях. Поэтому проверка форсированных планов входит у нас в послеобновленческий чек-лист.

Помимо регрессов, есть три сценария, ради которых мы держим Query Store включённым постоянно.

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

SELECT TOP 15 q.query_id,
       SUM(rs.count_executions) AS execs,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000000 AS total_cpu_s,
       SUM(rs.avg_duration * rs.count_executions) / 1000000 AS total_dur_s,
       SUBSTRING(qt.query_sql_text, 1, 150) AS txt
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
GROUP BY q.query_id, SUBSTRING(qt.query_sql_text, 1, 150)
ORDER BY total_cpu_s DESC;

Запросы с высокой изменчивостью. Разница между минимальной и максимальной длительностью при одном плане — признак блокировок или конкуренции за ресурсы, а не проблемы плана.

Ожидания по категориям. Начиная с SQL Server 2017 Query Store хранит и статистику ожиданий по запросам — это отвечает на вопрос «запрос медленный, потому что считает или потому что ждёт».

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

Клиент — оптовая база стройматериалов, 37 рабочих мест, УТ на MS SQL 2019, база 118 ГБ.

Ситуация классическая по форме. В понедельник обновили конфигурацию до нового релиза. Во вторник посыпались жалобы: подбор номенклатуры в документе реализации стал открываться «вечность».

Вывод сделали очевидный — виновато обновление. Обратились к франчайзи, который вёл конфигурацию, тот две недели искал изменения в коде подбора и не нашёл ничего существенного.

К нам обратились уже с вопросом «можно ли откатиться».

Query Store на этой базе был включён нами полугодом ранее, при постановке на поддержку. Это и решило дело.

Отчёт по регрессам за две недели сразу дал кандидата: один запрос с двумя планами. Старый — средняя длительность 240 мс, 14 тысяч выполнений. Новый — 9,8 секунды, появился в понедельник.

Сравнили планы рядом. Старый использовал поиск по индексу и вложенные соединения. Новый — сканирование той же таблицы с хеш-соединением.

Текст запроса в обоих планах был идентичен до символа. То есть код не менялся — менялось решение оптимизатора.

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

Лечение заняло восемь минут: обновили статистику по трём затронутым таблицам с FULLSCAN. План вернулся к прежнему на следующем же выполнении.

Подбор номенклатуры стал открываться за 0,3 секунды.

Две недели поиска в коде — и восемь минут работы, когда стало понятно, где смотреть. Без Query Store мы бы тоже нашли причину, но дольше и с меньшей уверенностью: доказать, что «раньше был другой план», без истории невозможно.

После этого случая мы добавили обновление статистики с полным сканированием по затронутым таблицам в стандартный чек-лист обновления конфигурации. Стоит пять минут, снимает целый класс инцидентов вида «после обновления стало медленно».

Честно про минусы, потому что их надо учитывать.

Накладные расходы на запись. По документации — единицы процентов. По нашим замерам на базах 1С с интенсивным вводом — в пределах 3–5 % при режиме AUTO. В режиме ALL может быть существенно больше, вплоть до заметного на глаз.

Место в базе. Query Store хранится внутри базы данных и увеличивает её размер и размер бэкапов. Четыре гигабайта на базе в 150 ГБ — терпимо, на базе в 20 ГБ — заметно.

Переход в READ_ONLY. Уже упоминал, но повторю, потому что это самая частая проблема: заполнилось хранилище — сбор молча прекратился.

Не заменяет технологический журнал. Query Store видит SQL-запросы, но не знает ничего про 1С: какой пользователь, какая операция в интерфейсе, какая строка кода конфигурации. Для связи с прикладной логикой нужен техжурнал.

Не видит запросы, не дошедшие до SQL. Если тормоза на стороне сервера приложений — Query Store покажет, что все запросы быстрые, и будет прав.

Наша практика: включать на всех боевых базах 1С в режиме AUTO с хранилищем 2–4 ГБ и историей 45 дней, добавлять проверку состояния в мониторинг. Это даёт постоянную историю ценой нескольких процентов производительности — обмен, который окупается на первом же разборе регресса.

И одно замечание про порядок работ. Включать Query Store в момент аварии почти бесполезно: истории нет, сравнивать не с чем. Он ценен только тем, что был включён заранее.

Замедлит ли Query Store работу базы 1С?

В режиме QUERY_CAPTURE_MODE = AUTO накладные расходы по нашим замерам держатся в пределах 3–5 % на базах с интенсивным вводом. Режим ALL для 1С не подходит: платформа генерирует слишком много одноразовых запросов, и хранилище забьётся за сутки.

Какой размер хранилища задавать?

2–4 ГБ для типовой боевой базы при глубине истории 30–45 дней. Главное — поставить в мониторинг проверку состояния: при заполнении Query Store переходит в READ_ONLY и молча перестаёт собирать данные.

Безопасно ли форсировать план выполнения?

Как временная мера — да, она снимает остроту, пока вы разбираетесь. Как постоянное решение — рискованно: данные растут, и закреплённый план со временем может стать плохим. Обязательно фиксируйте форсирование в журнале изменений и проверяйте его после каждого обновления конфигурации.

Заменяет ли Query Store технологический журнал 1С?

Нет, они дополняют друг друга. Query Store видит SQL-запросы и их планы, но не знает, какой пользователь и какая операция в интерфейсе их породили. Связь с прикладной логикой даёт только технологический журнал.