Почему при расчете себестоимости в 1С переполняется tempdb и как это исправить?

← На главную

При выполнении регламентной операции расчета себестоимости (в частности, при расчете партий дополнительных расходов) в конфигурациях 1С:ERP или 1С:Комплексная Автоматизация на клиент-серверном варианте с MS SQL Server может возникать критическая проблема: системная база данных tempdb начинает стремительно расти и забивает все свободное дисковое пространство (вплоть до терабайтов), после чего фоновое задание аварийно завершается.

Разберем подробно причины такого поведения оптимизатора SQL Server, методы локализации проблемы и проверенные способы ее решения.

Причины бесконтрольного роста tempdb

Проблема кроется в особенностях построения сложных запросов платформой 1С и механизмах работы СУБД MS SQL Server со статистикой временных таблиц:

Ошибка оценки кардинальности (Cardinality Estimation): Когда функция расчета себестоимости формирует один громоздкий запрос из десятка секций с конструкцией ОБЪЕДИНИТЬ ВСЕ, оптимизатор SQL Server не может корректно оценить количество ожидаемых строк на промежуточных этапах обработки.
Использование служебных объектов (Internal Objects): Базу tempdb переполняют не сами временные таблицы 1С (User Objects), а внутренние служебные объекты СУБД — операторы Table Spool (Lazy Spool), Worktables и Workfiles. При неверном плане выполнения SQL Server начинает временно сбрасывать огромные объемы промежуточных хеш-таблиц и сортировок на диск.
Отсутствие индексов и статистики: Если временные таблицы создаются в запросе без явного индексирования, SQL Server считает, что в них содержится минимальное количество строк. В результате для объединения и соединения выбирается неэффективный алгоритм (например, Nested Loops с созданием временного спула).

Локализация проблемного участка запроса

Рассмотрим структуру типового запроса функции ПолучитьДанныеДляДопРасходов в модуле партионного учета:


Запрос.Текст = ""
    // подготовка временных таблиц
    + ТекстПартииПоЗаказам() // вт ПартииПоЗаказам
    + ТекстРазделениеЗапросов()    + ТекстРегистраторыПрочих() // вт РегистраторыПрочих
    + ТекстРазделениеЗапросов()    + ТекстПериодыПартийПрочих() // вт ПериодыПартийПрочих
    + ТекстРазделениеЗапросов()
    // выборка данных
    + ТекстОписаниеДанныхДляПартийПрочих()
    + ТекстОбъединениеЗапросов() + ТекстОстаткиПартийПрочих()
    + ТекстОбъединениеЗапросов() + ТекстПриходыПартийПрочих()
    + ТекстОбъединениеЗапросов() + ТекстБазисОстаткиСебестоимость()
    + ТекстОбъединениеЗапросов() + ТекстБазисПриходыСебестоимость()
    + ТекстОбъединениеЗапросов() + ТекстБазисКорректировкиПоступлений()
    + ТекстОбъединениеЗапросов() + ТекстБазисПередачиТоваровСебестоимость()
    + ТекстОбъединениеЗапросов() + ТекстБазисПеремещенияСебестоимость()
    + ТекстОбъединениеЗапросов() + ТекстБазисПрошлыеПриходыТоваровСебестоимость()
    + ТекстОбъединениеЗапросов() + ТекстБазисЗаказы()
    + ТекстОбъединениеЗапросов() + ТекстБазисУслуги()
    + ТекстСортировка;

Запрос.УстановитьПараметр("РасчетныеПартииПоЗаказам", РасчетныеПартииПоЗаказам);
ДанныеДляРасчета = Запрос.Выполнить().Выгрузить();

Для отладки проанализируем ситуацию путем разделения составного запроса на отдельные пакеты. Выполнение подзапросов по отдельности происходит за считанные секунды, но их объединение через единый Запрос.Выполнить() приводит к сбою. Это подтверждает, что проблема находится именно в тяжелом плане выполнения единого запроса СУБД, а не в объеме реальных данных.

Варианты решения проблемы

Способ 1. Обновление платформы и конфигурации

Проанализировав практический опыт исправлений, первым делом рекомендуется выполнить следующие шаги:

Обновить платформу 1С:Предприятие до актуального стабильного релиза (проблема часто проявлялась на сборках 8.3.10 и устранялась в более свежих версиях, начиная с 8.3.11 и выше).
Обновить релиз конфигурации. В обновленных версиях партионного учета (Партионный учет 2.2 / 2.4 / 2.5) разработчики фирмы «1С» переработали алгоритмы распределения доп. расходов и отказались от громоздких объединений в едином тексте запроса.

Способ 2. Явное индексирование временных таблиц в запросе

Если быстрое обновление конфигурации невозможно, внесем изменения в код запроса. В функциях, формирующих временные таблицы (ТекстПартииПоЗаказам, ТекстРегистраторыПрочих, ТекстПериодыПартийПрочих), необходимо явно добавить секцию индексирования по ключевым полям связей:


// Пример добавления индексации во временную таблицу
"ВЫБРАТЬ
|   ...
|ПОМЕСТИТЬ ВТ_ПартииПоЗаказам
|ИЗ
|   ...
|ИНДЕКСИРОВАТЬ ПО
|   Номенклатура,
|   ДокументПартии"

Наличие индекса заставляет MS SQL Server автоматически построить статистику по временной таблице, благодаря чему оптимизатор выберет правильный алгоритм соединения и не будет создавать сбрасываемые на диск Spool-объекты.

Способ 3. Разбиение объединения запросов

Вместо выполнения единого монолитного запроса с множеством конструкций ОБЪЕДИНИТЬ ВСЕ перепишем алгоритм так, чтобы промежуточные результаты временно сохранялись в одну общую временную таблицу последовательными командами или выполнялись отдельными шагами. В простых случаях локализации достаточно временно исключить пустые неиспользуемые секции базиса (например, ТекстБазисПрошлыеПриходыТоваровСебестоимость()), если в конкретном периоде по ним отсутствуют данные.

Способ 4. Настройка MS SQL Server

Для предотвращения проблем с tempdb на стороне СУБД проанализируем следующие системные настройки:

Установка сервис-паков СУБД: Для MS SQL Server 2012 обязательна установка пакетов обновления Service Pack 2 или Service Pack 3 (KB3072779), в которых существенно оптимизирована работа с временными таблицами.
Флаги трассировки: Включим флаги трассировки -T1118 (устраняет контенцию страниц GAM/SGAM) и -T2371 (обеспечивает динамическую и более частую автоактуализацию статистики).
Структура файлов tempdb: База данных tempdb должна быть разбита на несколько файлов данных одинакового размера (из расчета 1 файл на физическое ядро процессора, но не более 8 файлов) с фиксированным шагом авторасширения.