Тормоза Accounting в SQL: waits и план запроса, не profiler навсегда

Короткий ответ

Медленный SQL на Accounting разбирайте так: wait stats (на что ждём), активный запрос и план, затем точечное действие (блокировка, индекс/статистика, spill в tempdb, параметр sniffing). Не включайте Profiler / sp_trace_create / XEvent «sql_statement_completed без фильтра» на неделю на проде: вы добавите I/O и не прочитаете лог. Query Store на SQL 2019/2022 — штатный инструмент с потолком размера.

Связка с 1С: сначала отделите сеть и кластер (1С медленно), затем SQL.

Симптомы и как отличить

Типичная картина:

• в dm_exec_requests один SPID минутами, wait_type стабилен;
• CPU SQL 80% на одном запросе;
• deadlock graph 1205.

Отличия:

Возможные причины

1. Блокировки и длинные транзакции 1С.
2. Устаревшая статистика после массовой загрузки.
3. План с lookup/scan по регистру накопления.
4. Parameter sniffing после смены плана.
5. Spill в tempdb.
6. MAXDOP/cost threshold неадекватны.
7. Редко: повреждение индекса (827 и CHECKDB).

Диагностика

SQL01, база Accounting, instance MSSQLSERVER.

1. Wait stats (экземпляр)

SELECT TOP 20 wait_type, wait_time_ms, waiting_tasks_count,
       wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE N'SLEEP%'
  AND wait_type NOT LIKE N'WAITFOR%'
  AND wait_type NOT LIKE N'BROKER%'
  AND wait_type NOT LIKE N'XE%'
ORDER BY wait_time_ms DESC;

Это с накопления старта службы — для окна используйте дельту (два снимка) или XEvent waits точечно.

2. Кто сейчас

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.cpu_time, r.logical_reads,
       r.granted_query_memory, t.text, p.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE r.database_id = DB_ID(N'Accounting');

3. Query Store (если включён)

SELECT TOP 15 qsq.query_id, rs.avg_duration / 1000 AS avg_ms, rs.count_executions,
       qt.query_sql_text
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan qsp ON rs.plan_id = qsp.plan_id
JOIN sys.query_store_query qsq ON qsp.query_id = qsq.query_id
JOIN sys.query_store_query_text qt ON qsq.query_text_id = qt.query_text_id
WHERE rs.last_execution_time > DATEADD(HOUR, -4, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Включение QS — с лимитом размера, не «unlimited».

4. Разовый XEvent, не навсегда

Сессия на конкретный session_id или duration > N, файл на диск с местом, выключить после замера. Шаблон «query_post_execution_showplan на все» на проде 1С — плохая идея.

Решение

Сценарий A. Блокировка

Снимите/дождитесь сеанса, не «оптимизируйте план». Сеанс завис.

Сценарий B. Устаревшая статистика

UPDATE STATISTICS [Accounting].dbo.[ИмяТаблицы] WITH FULLSCAN;

Имя таблицы — из плана, не все подряд в обед. Обслуживание статистики — ночное окно.

Сценарий C. План

Сравните в Query Store «быстрый/медленный» plan_id. Принудительный план — осознанно, с комментарием. Индекс добавляйте на копии Accounting, замер 1С, потом прод.

Сценарий D. Кэш планов

Точечно DBCC FREEPROCCACHE (plan_handle) одного плана, не всего сервера, если доказан sniffing.

Сценарий E. Диск/RAM

Не индекс, а память или сторадж.

Контур 1С: как связать план SQL с вызовом платформы

Текст в dm_exec_sql_text для 1С часто нечитаемый автоген. Не правите его руками в проде. Идентификаторы таблиц _Document123 / _InfoRg сопоставляйте с метаданными через консультанта или инструменты 1С, не переименовывайте таблицы SQL.

Технологический журнал с событием DBMSSQL в том же окне, что и план, даёт длительность вызова с стороны платформы. Если ТЖ показывает 50 мс, а пользователь ждёт 8 с — время ушло не в этот SQL (кластер, сеть, блокировка 1С, клиент). Если ТЖ 8 с и SQL тот же SPID 8 с — работайте с планом.

Query Store: включите на Accounting с OPERATION_MODE = READ_WRITE, ограничьте MAX_STORAGE_SIZE_MB (например сотни МБ–единицы ГБ, не безлимит). CAPTURE_POLICY не обязан ловить каждый мелкий вызов. После заполнения QS не «выключите и удалите» в день инцидента — сначала выгрузите регрессирующие query_id.

Параллелизм: для коротких OLTP-проведений 1С большой CXPACKET не всегда зло, но если один отчёт забирает все ядра, ограничьте MAXDOP для этого ресурса Resource Governor или дождитесь окна. Глобальный MAXDOP 1 без замера может удлинить закрытие месяца.

Не добавляйте 20 индексов «по совету недостающих» за ночь: запись регистров 1С подорожает, регламент удлинится. Один индекс — тест на копии, замер проведения, потом прод.

Как проверить, что проблема устранена

Тот же пользователь, тот же документ/отчёт, время в секундах, wait_type исчез. План в QS стабилен несколько часов пика. Трассировка выключена.

Если не помогло

• Текст запроса меняется каждый раз (1С параметризует странно) — ТЖ 1С + замер.
• Только отчёт СКД — оптимизация СКД, не «все индексы».
• После обновления конфигурации — регресс, тестируйте на копии.

Профилактика

• Query Store с max size и stale capture.
• Алерт по blocking и CPU.
• Регламент статистики/индексов вне пика.
• Запрет Profiler на прод-SQL01 политикой.

На SQL01 заведите короткий регламент: раз в неделю смотреть Query Store регрессии по Accounting, раз в квартал полный замер пяти операций. Не копируйте «волшебные» trace flag из случайных веток форума на SQL 2019/2022 без документа к CU. Если консультант 1С просит «включить все события ТЖ на неделю» — откажите и предложите окно на 2 часа с фильтром Duration. Для блокировок достаточно blocking_session_id и кластера, не profiler. Для плана — Query Store или разовые XEvents. Так вы сохраните и диск, и достоверность замера.

Если после добавления индекса проведение ускорилось, а обмен ночью удлинился — это ожидаемый trade-off записи. Зафиксируйте оба числа, не объявляйте победу по одному отчёту. На копии Accounting_updtest повторите закрытие периода, прежде чем нести индекс в прод.

FAQ

Profiler «только на минуту» можно?

Иногда. Фильтр по database_id/Accounting, не RPC:Completed без фильтра. Лучше XEvent с файлом и stop.

Нужны ли индексы «как советует Database Engine Tuning Advisor» на 1С?

Осторожно: метаданные 1С и регламенты могут конфликтовать. Тест на копии, консультант конфигурации.

MAXDOP 1 для 1С обязательно?

Нет догмы. Часто ограничивают параллелизм на OLTP, но мерить. Не ставьте 1 «потому что 1С» без замера.

Trace flag 4199?

Не включайте пачкой без документа к вашей сборке SQL. Сначала план и waits.

Можно ли включить все события технологического журнала 1С и SQL вместе?

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