При выполнении регламентной процедуры закрытия месяца в программе 1С:Управление торговлей 11.4 (а также в сопутствующих решениях 1С:ERP и 1С:Комплексная автоматизация) системные администраторы и программисты часто сталкиваются со следующей проблемой: на этапе расчета себестоимости системная база данных СУБД tempdb начинает лавинообразно увеличиваться в размерах. Рост может составлять десятки и сотни гигабайт (вплоть до 70–160 Гб), что приводит к полному исчерпанию свободного дискового пространства и аварийному падению сервера СУБД.
В этой статье мы подробно проанализируем причины этой проблемы, разберем поведение оптимизатора запросов MS SQL Server и предложим несколько вариантов решения — от административных настроек СУБД до архитектурного перехода на новую модель партионного учета.
Для начала проанализируем запрос, на котором происходит аварийное завершение работы СУБД. В типовом механизме распределения затрат и расчета себестоимости при использовании партионного учета версии 2.1 выполняется следующий запрос:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ДД.ДокументВыпуска КАК Регистратор,
ДД.АналитикаУчетаПродукции КАК АналитикаУчетаПродукции,
Приходы.Период
ИЗ
РегистрНакопления.ПартииЗатратНаВыпуск.Остатки(&НачалоПериода, Организация В (&МассивОрганизаций)) КАК ДД
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииЗатратНаВыпуск КАК Приходы
ПО Приходы.Период < &НачалоПериода
И Приходы.Регистратор = ДД.ДокументВыпуска
И Приходы.Регистратор = Приходы.ДокументВыпуска
Давайте выясним причину, почему этот, на первый взгляд, простой запрос вызывает катастрофический рост tempdb со скоростью несколько мегабайт в секунду на базе объемом около 65 Гб (где записей в регистре всего 19 миллионов, а актуальных остатков — 75 тысяч).
Главная проблема этого запроса кроется в особенностях построения физического плана выполнения на стороне СУБД MS SQL Server. Рассмотрим ключевые факторы, которые заставляют оптимизатор СУБД совершать фатальные ошибки:
ДокументВыпуска и стандартное поле Регистратор в платформе 1С:Предприятие имеют составной тип данных. На уровне СУБД это означает, что поля представлены не одной колонкой, а несколькими (обычно это пара колонок: тип документа _TRef и уникальный идентификатор ссылки _RRef). Соединение таблиц по составным полям крайне затрудняет для оптимизатора MS SQL Server точную оценку кардинальности (Cardinality Estimation). СУБД не может корректно предсказать, сколько строк вернет это соединение.И Приходы.Регистратор = Приходы.ДокументВыпуска. Это фильтр по полям одной и той же физической таблицы Приходы, но внедренный непосредственно в секцию ПО (ON) внутреннего соединения. Оптимизатор SQL Server не имеет статистических данных о корреляции значений между колонками Регистратор и ДокументВыпуска внутри таблицы из 19 миллионов строк. СУБД строит гипотезу, что совпадений будет либо слишком много, либо слишком мало, и принимает неверное решение.tempdb, вызывая её неконтролируемый рост.В попытках ускорить работу системы некоторые администраторы переносят файлы tempdb на RAM-диск (в оперативную память), искусственно ограничивая доступную память для самого SQL Server (например, выделяя СУБД всего 60 Гб из 184 Гб доступных на сервере). Рассмотрим, почему в нашей ситуации это решение усугубляет проблему:
tempdb лежали на быстром NVMe SSD-накопителе, у базы была бы возможность расшириться до физических пределов диска, транзакция бы завершилась (пусть и медленнее) или корректно откатилась. На RAM-диске при исчерпании лимита СУБД мгновенно аварийно завершает работу, прерывая сеансы всех пользователей.Это самое правильное, надежное и стратегическое решение проблемы. Описанный выше проблемный запрос выполняется только при использовании устаревшей модели Партионного учета версии 2.1.
В версии партионного учета 2.1 расчет себестоимости опирается на целое семейство разрозненных регистров накопления: ПартииЗатратНаВыпуск, ПартииТоваровОрганизаций, ПартииПроизводственныхЗатрат и другие. В версии 2.2 (и выше) разработчики фирмы 1С полностью переработали архитектуру: все данные теперь аккумулируются в едином оптимизированном регистре накопления Себестоимость товаров.
Переход на версию 2.2 полностью исключает выполнение тяжелых запросов к регистру ПартииЗатратНаВыпуск, так как этот регистр больше не используется системой.
Как выполнить переход:
Если ваша компания по каким-то причинам не может прямо сейчас перейти на партионный учет версии 2.2 (например, из-за специфических кастомизаций старого механизма), проблему можно решить точечно. Для этого перепишем исходный запрос в общем модуле расчета себестоимости, используя механизм расширений конфигурации.
Разберем по шагам, как переписать этот запрос с использованием временных таблиц, чтобы гарантировать быстрое выполнение без привлечения ресурсов tempdb:
Шаг 1. Выгрузка остатков во временную таблицу. Нам не нужно сопоставлять миллионы строк «в лоб». Сначала выберем небольшой объем данных из виртуальной таблицы остатков (в примере автора — всего 75 тысяч строк) и обязательно проиндексируем их по полю связи:
ВЫБРАТЬ
ДД.ДокументВыпуска КАК ДокументВыпуска,
ДД.АналитикаУчетаПродукции КАК АналитикаУчетаПродукции
ПОМЕСТИТЬ ВТ_ОстаткиПартий
ИЗ
РегистрНакопления.ПартииЗатратНаВыпуск.Остатки(&НачалоПериода, Организация В (&МассивОрганизаций)) КАК ДД
ИНДЕКСИРОВАТЬ ПО
ДокументВыпуска
;
Шаг 2. Получение итогового результата через соединение с временной таблицей. Теперь соединим индексированную временную таблицу ВТ_ОстаткиПартий с физической таблицей регистра. Благодаря индексу на поле ДокументВыпуска, оптимизатор SQL Server гарантированно выберет быструю операцию Nested Loops (вложенные циклы), считывая из огромной таблицы регистра только те строки, которые соответствуют остаткам:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Приходы.Регистратор КАК Регистратор,
ВТ_ОстаткиПартий.АналитикаУчетаПродукции КАК АналитикаУчетаПродукции,
Приходы.Period КАК Период
ИЗ
ВТ_ОстаткиПартий КАК ВТ_ОстаткиПартий
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииЗатратНаВыпуск КАК Приходы
ПО Приходы.Период < &НачалоПериода
И Приходы.Регистратор = ВТ_ОстаткиПартий.ДокументВыпуска
И Приходы.Регистратор = Приходы.ДокументВыпуска
Такой подход снизит потребление tempdb с сотен гигабайт до считанных килобайт, а время выполнения запроса сократится до нескольких секунд.
Если у вас нет возможности оперативно вносить изменения в код конфигурации или менять настройки партионного учета, можно скорректировать поведение оптимизатора на уровне СУБД:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
Это заставит SQL Server строить планы по старым алгоритмам, которые для соединений по составным полям чаще выбирают безопасные вложенные циклы (Nested Loops) вместо ресурсоемких Hash Join.
Query Store, вы можете найти ресурсоемкий запрос, потребляющий весь объем tempdb, проанализировать историю его планов и принудительно зафиксировать («запинить») тот план выполнения, который использует вложенные циклы (Nested Loops) и не вызывает сброса данных во временные рабочие таблицы.tempdb следует перенести на физический быстрый массив NVMe SSD. Также убедитесь, что база tempdb разделена на несколько файлов одинакового начального размера (количество файлов должно соответствовать количеству физических ядер процессора, но не более 8), чтобы исключить конкуренцию за страницы распределения памяти (PFS/GAM).Иногда причиной резкого изменения планов выполнения запросов СУБД становится появление «битых» данных в базах. Проанализируем состояние данных в регистре накопления:
EXEC sp_updatestats;
DBCC FREEPROCCACHE;
Используя предложенные методы, мы сможем полностью ликвидировать проблему неконтролируемого роста базы tempdb и обеспечить стабильную и быструю процедуру закрытия месяца в 1С:Управление торговлей.