Мы сталкиваемся с распространенной и крайне неприятной проблемой: периодическое зависание запросов в 1С:ERP 2.5 на платформе 8.3.25.1445, работающей с MSSQL 2019. Эти запросы, обращающиеся к периодическому регистру сведений с примерно 300 тысячами записей, могут выполняться от нескольких секунд до нескольких суток, при этом их поведение совершенно непредсказуемо. Самое тревожное — во время длительного выполнения объем tempdb может увеличиваться до 6 ТБ, тогда как в штатном режиме запрос возвращает лишь килобайты данных. Перезагрузки серверов и чистка кэша не помогают, а принудительное завершение таких запросов часто приводит к длительным откатам (rollback/killed).
Вместе мы разберем причины такого аномального поведения, рассмотрим инструменты для диагностики и анализа, а также обсудим методы оптимизации запросов и, как крайнюю меру, способы принудительного завершения зависших сеансов.
Первоочередная задача — понять, почему один и тот же запрос при одних и тех же входных данных может выполняться столь по-разному. Это часто указывает на фундаментальные проблемы в архитектуре запросов или во взаимодействии с СУБД.
Parameter Sniffing (чувствительность к параметрам)
Parameter Sniffing — это механизм SQL Server, при котором оптимизатор запросов "нюхает" текущие значения параметров во время компиляции или перекомпиляции плана запроса. На основе этих значений он строит план выполнения, который может быть очень эффективным для конкретного набора параметров. Однако, этот же план затем используется для всех последующих вызовов запроса с другими параметрами. Если параметры сильно отличаются, ранее скомпилированный план может стать крайне неэффективным.
Как это проявляется: Представьте условие типа ГДЕ Дата >= (&МояДата). Если для даты '01.01.2025' оптимизатор ожидает получить 10 записей и строит план, оптимальный для такого малого объема, то этот же план будет использоваться и для '01.01.1913', когда записей могут быть миллионы. Это приводит к значительному замедлению и перегрузке ресурсов, включая tempdb.
RLS (Row-Level Security)
Хотя в нашем случае RLS отключен, мы должны помнить, что эта функция может значительно усложнять планы запросов. Она добавляет дополнительные условия фильтрации на каждую строку, что может привести к слишком большому количеству соединений и условий, с которыми оптимизатор справляется не всегда эффективно. Иногда даже небольшие изменения в запросе могут запутать оптимизатор, приводя к серьезным ошибкам в построении плана.
Неоптимальные планы запросов и аномальный рост tempdb
Аномальный рост tempdb до 6 ТБ, при том что запрос должен возвращать лишь килобайты данных, является критическим индикатором. Это почти всегда говорит о том, что запрос выполняет операции, требующие огромного объема временного хранилища. Мы рассмотрим основные причины:
CROSS JOIN или аналогичное поведение: Часто возникает, когда в запросе некорректно заданы условия соединения таблиц или вообще отсутствуют связи между таблицами. В результате система пытается соединить каждую строку одной таблицы с каждой строкой другой, генерируя катастрофически большое количество промежуточных данных.ORDER BY, GROUP BY) и хеш-операции: Если запрос работает с большим объемом данных, даже при отсутствии явных ошибок в соединениях, операции сортировки или хеширования (например, при GROUP BY или хеш-соединениях) могут требовать значительных ресурсов tempdb, особенно если доступной оперативной памяти недостаточно.СрезПоследних, генерируют сложные SQL-запросы. Если в условиях отбора виртуальной таблицы передаются параметры, которые приводят к выборке миллионов записей, это может стать причиной проблемы.Блокировки и взаимоблокировки
Длительные зависания могут быть вызваны не только неэффективностью самого запроса, но и блокировками, созданными другими сессиями. Даже если ваш запрос сам по себе быстр, он может "упереться" в ресурс, заблокированный другой, возможно, менее критичной операцией. Это может привести к каскадным задержкам.
Для глубокого анализа поведения запросов и выявления корневых причин мы будем активно использовать средства мониторинга SQL Server.
Динамические представления управления (DMV) SQL Server
Это мощные инструменты для получения информации о состоянии и производительности сервера.
sys.dm_exec_query_memory_grants: Позволяет определить, какие запросы потребляют много памяти. Нас интересуют запросы с большим значением granted_memory_kb. Мы используем этот скрипт для определения проблемных сессий:
use ВАША_БАЗА;
SELECT TOP 15
mg.granted_memory_kb / 1024.0 / 1024.0 AS granted_memory_GB,
mg.session_id,
t.text,
qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC
OPTION (MAXDOP 1)
Этот запрос покажет топ-15 запросов, которым было выделено больше всего памяти.
sys.dm_exec_requests: Предоставляет информацию о текущем состоянии запросов, включая их статус (running, suspended, runnable), время выполнения, ожидание (wait_type, wait_time) и блокировки (blocking_session_id). Мы можем использовать его для определения SPID (идентификатора сессии), который затем можно использовать для завершения запроса. Например, команда sp_who2 'active' дает быструю сводку активных процессов.sys.dm_os_wait_stats: Помогает выявить основные типы ожиданий, с которыми сталкивается сервер (например, PAGEIOLATCH_SH, WRITELOG, LCK_M_S), что укажет на узкие места в производительности (дисковая подсистема, журнал транзакций, блокировки).Query Store в SQL Server (начиная с SQL Server 2016)
Это мощный инструмент для отслеживания истории выполнения запросов, их планов и статистики ресурсов. Мы можем использовать его, чтобы:
parameter sniffing, когда план запроса, скомпилированный для одних параметров, неэффективен для других.Анализ планов выполнения запросов
Всегда получайте фактический план выполнения для проблемного запроса. Используйте SET SHOWPLAN_ALL ON или SET STATISTICS PROFILE ON перед выполнением запроса (или графический план в SQL Server Management Studio). Анализируйте дорогостоящие операции: сканирование больших таблиц, неоптимальные соединения (особенно Nested Loops, Hash Match, Merge Join, если они работают с неожиданно большим объемом данных), операции сортировки, требующие большого объема памяти (SORT с предупреждениями о переполнении памяти). Особое внимание уделите предупреждениям в плане запроса (например, о нехватке памяти).
Мониторинг tempdb
Поскольку tempdb забивается на террабайты, необходимо тщательно проверить его конфигурацию: размер и количество файлов tempdb, а также их размещение на быстрых дисках. Слишком мало файлов tempdb или их неправильная конфигурация могут привести к contention (борьбе за ресурсы). Рекомендуем ознакомиться со статьями, посвященными оптимизации tempdb.
Лог блокировок и взаимоблокировок
Настройте мониторинг блокировок (например, с помощью расширенных событий SQL Server или встроенных отчетов). Это поможет определить, какие ресурсы блокируются, кто является инициатором блокировки и как часто возникают взаимоблокировки.
Давайте рассмотрим предоставленный вами проблемный запрос и выясним, как его можно улучшить. Проблема чаще всего кроется в логике получения данных, особенно при работе с виртуальными таблицами 1С.
Ваш запрос выглядит так:
ВЫБРАТЬ РАЗЛИЧНЫЕ
mol_КачественныеПоказателиВидаМолочнойНоменклатуры.ПоказательКачества КАК ПоказательКачества
ПОМЕСТИТЬ вт_ПоказателиИспользуемыеВПараметрах
ИЗ
Справочник.mol_КачественныеПоказателиВидаМолочнойНоменклатуры КАК mol_КачественныеПоказателиВидаМолочнойНоменклатуры
ГДЕ
mol_КачественныеПоказателиВидаМолочнойНоменклатуры.ИспользованиеВПараметрах
И НЕ mol_КачественныеПоказателиВидаМолочнойНоменклатуры.ПометкаУдаления
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Номенклатура КАК Номенклатура,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Серия КАК Серия,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ПоказательАнализа КАК ПоказательАнализа,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ЗначениеПоказателя КАК ЗначениеПоказателя,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Регистратор КАК ЛабАнализ,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Характеристика КАК Характеристика,
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ВидНоменклатуры КАК ВидНоменклатуры
ИЗ
РегистрСведений.лисЗначениеПоказателейЛабораторногоАнализа.СрезПоследних(&Дата, Номенклатура В (&СписокНоменклатуры)) КАК лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних
ГДЕ
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ПоказательАнализа В
(ВЫБРАТЬ
вт_ПоказателиИспользуемыеВПараметрах.ПоказательКачества КАК ПоказательКачества
ИЗ
вт_ПоказателиИспользуемыеВПараметрах КАК вт_ПоказателиИспользуемыеВПараметрах)
И лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Период >= ДОБАВИТЬКДАТЕ(&Дата, МЕСЯЦ, -3)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних.Номенклатура КАК Номенклатура,
mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних.ПоказательКачества КАК ПоказательАнализа,
mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних.БазовоеЗначение КАК ЗначениеПоказателя,
mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних.СортНоменклатуры КАК Сорт,
ЗНАЧЕНИЕ(Документ.mol_ЛабораторныйАнализ.ПустаяСсылка) КАК ЛабАнализ,
ЗНАЧЕНИЕ(Справочник.СерииНоменклатуры.ПустаяСсылка) КАК Серия
ИЗ
РегистрСведений.mol_БазовыеЗначенияКачественныхПоказателей.СрезПоследних(&Дата, ПоказательКачества.ИспользованиеВПараметрах = ИСТИНА) КАК mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних
Виртуальные таблицы СрезПоследних
Виртуальная таблица СрезПоследних (или СрезПервых) в 1С при неоптимальных условиях отбора может генерировать очень тяжелые SQL-запросы. Её производительность критически зависит от:
РегистрСведений.лисЗначениеПоказателейЛабораторногоАнализа созданы оптимальные индексы. Для периодических регистров крайне важен составной индекс, включающий поле Период (желательно в убывающем порядке) и все ключевые измерения. Например, для РегистрСведений.лисЗначениеПоказателейЛабораторногоАнализа, где измерения - Номенклатура, Серия, ПоказательАнализа, Характеристика, ВидНоменклатуры, имеет смысл создать индекс по Период DESC, Номенклатура, Серия, ПоказательАнализа.Номенклатура В (&СписокНоменклатуры)), должны быть максимально точными, чтобы минимизировать объем обрабатываемых данных. Если СписокНоменклатуры содержит слишком много элементов или если для него неэффективно работает индекс, это может замедлить выборку.Дополнение: В вашем запросе, условие лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Период >= ДОБАВИТЬКДАТЕ(&Дата, МЕСЯЦ, -3) было добавлено для улучшения ситуации. Это очень правильный шаг, так как он значительно сокращает диапазон поиска в периодическом регистре. Однако, идеально было бы перенести этот фильтр непосредственно в параметры виртуальной таблицы СрезПоследних, если это возможно по логике, чтобы SQL Server мог использовать его еще на этапе формирования среза.
Использование временных таблиц и оператора В
Первый блок запроса, помещающий данные в вт_ПоказателиИспользуемыеВПараметрах, является хорошим подходом. Однако, блок ГДЕ лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ПоказательАнализа В (ВЫБРАТЬ ...) использует подзапрос, который по сути дублирует выборку из временной таблицы. Это можно оптимизировать, заменив подзапрос на прямое соединение с временной таблицей:
// Вместо:
// ГДЕ лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ПоказательАнализа В
// (ВЫБРАТЬ
// вт_ПоказателиИспользуемыеВПараметрах.ПоказательКачества КАК ПоказательКачества
// ИЗ
// вт_ПоказателиИспользуемыеВПараметрах КАК вт_ПоказателиИспользуемыеВПараметрах)
// Использовать JOIN:
ВЫБРАТЬ ...
ИЗ
РегистрСведений.лисЗначениеПоказателейЛабораторногоАнализа.СрезПоследних(&Дата, Номенклатура В (&СписокНоменклатуры)) КАК лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_ПоказателиИспользуемыеВПараметрах КАК вт_ПоказателиИспользуемыеВПараметрах
ПО лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.ПоказательАнализа = вт_ПоказателиИспользуемыеВПараметрах.ПоказательКачества
ГДЕ
лисЗначениеПоказателейЛабораторногоАнализаСрезПоследних.Период >= ДОБАВИТЬКДАТЕ(&Дата, МЕСЯЦ, -3)
Такой подход часто более эффективен, так как позволяет оптимизатору лучше построить план соединения.
Анализ последнего блока запроса
Последний блок запроса:
ВЫБРАТЬ mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних.Номенклатура КАК Номенклатура,
...
ИЗ РегистрСведений.mol_БазовыеЗначенияКачественныхПоказателей.СрезПоследних(&Дата, ПоказательКачества.ИспользованиеВПараметрах = ИСТИНА) КАК mol_БазовыеЗначенияКачественныхПоказателейСрезПоследних
Условие ПоказательКачества.ИспользованиеВПараметрах = ИСТИНА внутри виртуальной таблицы СрезПоследних через точку к ссылочному полю — это потенциальное слабое место, если на связанные таблицы нет оптимальных индексов или если запрос с этим условием вынужден сканировать очень много записей. Если список таких показателей фиксирован или невелик, лучше также использовать временную таблицу, как вы делали в первом блоке, и затем присоединять ее или использовать оператор В.
Также, если вы уже готовили временную таблицу вт_ПоказателиИспользуемыеВПараметрах, то логично было бы использовать её и для фильтрации этого среза, а не повторять условие.
Декомпозиция сложных запросов
Иногда разделение одного большого, сложного запроса на несколько более простых, с использованием временных таблиц (как вы уже делаете) или табличных выражений (CTE), может улучшить производительность. Это может помочь оптимизатору SQL Server лучше понять намерения запроса и построить более эффективный план. Мы можем использовать временные таблицы для промежуточных результатов, что часто делает запрос более прозрачным и управляемым.
Обход Parameter Sniffing
Если после всех оптимизаций запроса проблема parameter sniffing все еще проявляется, мы можем попробовать следующие подходы:
RECOMPILE: Добавление OPTION (RECOMPILE) к запросу заставит SQL Server перекомпилировать план каждый раз при его выполнении. Это устранит проблему parameter sniffing, но может увеличить нагрузку на процессор, так как компиляция плана — это тоже ресурсная операция. Используйте осторожно.OPTIMIZE FOR UNKNOWN или OPTIMIZE FOR (@parameter_name = value): Эти хинты могут дать оптимизатору подсказку, как лучше построить план выполнения. Например, OPTIMIZE FOR UNKNOWN заставит оптимизатор использовать среднюю статистическую информацию для параметров, а не конкретные значения, что может привести к более общему (иногда менее оптимальному) плану. OPTIMIZE FOR (@parameter_name = value) позволяет указать конкретное "эталонное" значение параметра, для которого должен быть построен план.Обслуживание базы данных
Важно убедиться, что база данных MSSQL регулярно обслуживается: обновляется статистика, перестраиваются индексы. Вы упомянули, что индексы перестраиваются несколько раз в сутки для проблемной таблицы, и это очень хорошо. Однако, убедитесь, что статистика также актуальна и для всех полей, участвующих в условиях WHERE, JOIN и ORDER BY. Устаревшая статистика может приводить к неверным оценкам оптимизатора и, как следствие, к неоптимальным планам.
Хотя мы стремимся решить проблему на корню, бывают ситуации, когда зависший запрос необходимо немедленно остановить. Однако, мы должны осознавать риски и последствия такого подхода.
Почему автоматическое завершение — это "крайняя мера"
Автоматическое завершение процессов SQL Server или 1С — это симптом, а не лечение. Оно может приводить к:
ROLLBACK): Если запрос успел выполнить значительный объем изменений данных, то после команды KILL СУБД потребуется время для отмены всех этих изменений. Это подтверждает, что запрос обрабатывал слишком много данных или вызывал масштабные блокировки.Тем не менее, если это единственное работоспособное временное решение, его реализация требует тщательного контроля.
Как принудительно завершить сессию в MSSQL
Мы можем использовать стандартные команды SQL Server для определения и завершения зависших сессий:
SPID (идентификатор сессии):
sp_who2 'active'
Эта команда покажет список активных сессий, их статус, время ожидания и другие полезные данные. Найдите подозрительную сессию с длительным временем выполнения или высокими показателями использования ресурсов.
KILL {SPID value}
Замените {SPID value} на идентификатор сессии, который вы получили на предыдущем шаге. После выполнения этой команды SQL Server начнет процесс отката транзакции, и сессия будет завершена.
Автоматизация завершения (с осторожностью)
Для автоматизации завершения процессов мы можем использовать SQL Server Agent Jobs. Скрипт, который мониторит sys.dm_exec_requests на предмет длительных запросов (например, выполняющихся более часа) и затем выполняет KILL SPID, может быть запущен по расписанию. Однако, необходимо реализовать очень сложную логику, которая будет:
Мы настоятельно рекомендуем использовать такую автоматизацию только после исчерпывающей диагностики и понимания всех рисков.
Мониторинг 1С
В 1С есть средства для работы с активными пользователями и фоновыми заданиями. Мы можем написать обработку, которая будет периодически анализировать активные сеансы 1С (с помощью встроенных функций платформы) и при превышении определенного порога времени выполнения пытаться завершить сеанс 1С. Однако, это не всегда приводит к немедленному завершению запроса на стороне СУБД, особенно если он уже запущен и находится в стадии обработки большого объема данных.
Проблема длительных зависающих запросов в 1С:ERP с MSSQL является комплексной и требует системного подхода. Мы выяснили, что причинами могут быть parameter sniffing, неоптимальные планы запросов, неэффективное использование ресурсов tempdb и блокировки. Мы рассмотрели мощные инструменты SQL Server для диагностики и мониторинга, а также подходы к оптимизации самих запросов.
Принудительное завершение процессов — это лишь временное решение, которое не устраняет корневую причину и сопряжено с рисками. Наша главная задача — найти и исправить проблемные места в запросах и конфигурации СУБД, чтобы избежать зависаний в будущем.