При выполнении регламентной процедуры закрытия месяца в крупных корпоративных базах данных на платформе 1С (таких как 1С:ERP Управление предприятием или 1С:Комплексная автоматизация) системные администраторы и разработчики часто сталкиваются с внезапным падением производительности. Процесс «зависает» на неопределенное время, а на сервере СУБД Microsoft SQL Server лавинообразно увеличивается размер базы данных tempdb, вплоть до полного исчерпания свободного места на диске. Выясним причину этой проблемы, детально проанализируем стек вызовов и структуру SQL-запроса, а также разберем по шагам эффективные методы ее устранения.
Для начала проанализируем технологический журнал и стек вызовов, зафиксированный в момент зависания системы. Как видно из логов, проблема возникает на этапе проверки состояния и использования шагов закрытия месяца:
ОбщийМодуль.ДлительныеОперации.Модуль : 734 : ВыполнитьПроцедуру(ВсеПараметры.ИмяПроцедуры, ВсеПараметры.ПараметрыПроцедуры);
ОбщийМодуль.ДлительныеОперации.Модуль : 743 : ОбщегоНазначения.ВыполнитьМетодКонфигурации(ИмяПроцедуры, ПараметрыПроцедуры);
ОбщийМодуль.ОбщегоНазначения.Модуль : 4856 : Выполнить ИмяМетода + "(" + ПараметрыСтрока + ")";
: 1 : БухгалтерскиеОтчеты.ОпределитьСостояниеЭтаповРасчетаЗакрытияМесяца(Параметры[0],Параметры[1])
ОбщийМодуль.БухгалтерскиеОтчеты.Модуль : 1923 : Результат.СостоянияЭтапов = ЗакрытиеМесяцаСервер.ОпределитьСостояниеЭтаповРасчета(
ОбщийМодуль.ЗакрытиеМесяцаСервер.Модуль : 11293 : Обработки.ОперацииЗакрытияМесяца.ЗаполнитьСостоянияЭтапов(ПараметрыЗапускаОбновленияСостояния);
Обработка.ОперацииЗакрытияМесяца.МодульМенеджера : 526 : ПроверитьИспользованиеЭтапа(ПараметрыОбработчика);
Обработка.ОперацииЗакрытияМесяца.МодульМенеджера : 779 : ОбщегоНазначения.ВыполнитьМетодКонфигурации(
ОбщийМодуль.ОбщегоНазначения.Модуль : 4856 : Выполнить ИмяМетода + "(" + ПараметрыСтрока + ")";
: 1 : ЗакрытиеМесяцаСервер.Использование_НастройкаПоказателейДляРаспределенияРасходовНаСебестоимостьПродукции(Параметры[0])
ОбщийМодуль.ЗакрытиеМесяцаСервер.Модуль : 3435 : Документы.РаспределениеПрочихЗатрат.ИнициализироватьЗапросПолученияДанныхДляРаспределения(Запрос, ПараметрыЗапроса);
Документ.РаспределениеПрочихЗатрат.МодульМенеджера : 2269 : ПодготовитьСлужебныеТаблицы(Запрос.Параметры.КонецПериода,
Документ.РаспределениеПрочихЗатрат.МодульМенеджера : 3145 : НалоговыйУчет.ДоляТранспортныхРасходовТекущегоМесяца(Период, Организации, Запрос.МенеджерВременныхТаблиц);
ОбщийМодуль.НалоговыйУчет.Модуль : 6408 : Запрос.Выполнить();
Рассмотрим подробнее этот стек. Платформа пытается определить, нужно ли выполнять этап «Настройка показателей для распределения расходов на себестоимость продукции». Для этого вызывается служебный алгоритм налогового учета НалоговыйУчет.ДоляТранспортныхРасходовТекущегоМесяца, который рассчитывает долю транспортных расходов текущего периода в соответствии со ст. 320 НК РФ. Внутри этого метода выполняется сложный многострочный запрос СУБД, использующий конструкции UNION ALL и связывающий физические таблицы тяжелых регистров накопления с временными таблицами (такими как #tt37, #tt40, #tt189, #tt186).
Этот запрос обращается к основным таблицам движений и итогов регистров накопления:
_AccumRg65103 (движения) и _AccumRgT65135 (итоги) — регистр накопления «Прочие расходы»;_AccumRg65591 (движения) и _AccumRgT65666 (итоги) — регистр накопления «Выручка и себестоимость продаж» (или смежный тяжелый регистр себестоимости).Рассмотрим внутреннюю механику работы оптимизатора MS SQL Server в этой ситуации. База данных tempdb используется СУБД для хранения временных таблиц, созданных платформой 1С (префиксы #tt), а также для выполнения промежуточных операций сортировки (Sort), хэширования (Hash Match) и сохранения промежуточных результатов выполнения запросов.
Когда СУБД выполняет сложный запрос с множеством соединений, оптимизатор строит план выполнения на основе имеющейся статистики распределения данных в таблицах. Если статистика устарела или не актуализирована, происходит критическая ошибка оценки кардинальности (Cardinality Estimation):
Nested Loops (вложенные циклы). Этот алгоритм эффективен только для единичных строк.tempdb (происходит так называемый Spill to Tempdb). Этот процесс лавинообразно забивает дисковую подсистему временными данными, увеличивая размер файлов tempdb.mdf и tempdb.ldf до тех пор, пока на физическом диске не закончится свободное место. Процесс закрытия месяца при этом полностью зависает.Выясним, как устранить эту проблему и вернуть систему в штатный режим работы.
Главная причина неоптимального плана запроса — неактуальная статистика. Стандартные регламентные операции обновления статистики СУБД со стандартным уровнем выборки могут пропускать изменения в таблицах итогов и движений регистров накопления 1С, особенно если в течение месяца проводились масштабные операции перепроведения документов.
Для исправления ситуации выполним принудительное обновление статистики с полным сканированием (FULLSCAN) для проблемных таблиц. Запустим в Среде управления SQL Server (SSMS) следующий сценарий:
-- Обновление статистики с полным сканированием для основной таблицы регистра
UPDATE STATISTICS dbo._AccumRg65591 WITH FULLSCAN, ALL;
-- Обновление статистики для таблицы итогов регистра
UPDATE STATISTICS dbo._AccumRgT65666 WITH FULLSCAN, ALL;
-- Также рекомендуется обновить статистику по второму задействованному регистру
UPDATE STATISTICS dbo._AccumRg65103 WITH FULLSCAN, ALL;
UPDATE STATISTICS dbo._AccumRgT65135 WITH FULLSCAN, ALL;
Использование параметра FULLSCAN гарантирует, что SQL Server проанализирует абсолютно все строки в таблицах, что позволит оптимизатору построить максимально точный и эффективный план выполнения запроса.
Простое обновление статистики не всегда сразу решает проблему зависания, так как СУБД может продолжать использовать ранее скомпилированный «плохой» план запроса, который уже находится в кэше планов (Plan Cache). Чтобы принудительно заставить оптимизатор построить новый план с учетом обновленной статистики, выполним очистку кэша.
Чтобы не сбрасывать кэш планов для всего сервера целиком (что может временно снизить производительность других процессов), найдем и удалим конкретный проблемный план. Для этого выполним следующий скрипт в SSMS:
-- Поиск и удаление конкретного плана запроса из кэша по тексту запроса
DECLARE @PlanHandle VARBINARY(64);
SELECT TOP 1 @PlanHandle = deps.plan_handle
FROM sys.dm_exec_query_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dept
WHERE dept.text LIKE '%_AccumRg65591%' AND dept.text LIKE '%#tt190%';
IF @PlanHandle IS NOT NULL
BEGIN
DBCC FREEPROCCACHE (@PlanHandle);
PRINT 'План запроса успешно удален из кэша.';
END
ELSE
BEGIN
PRINT 'План запроса не найден в кэше.';
END
Если нет возможности точечно удалять планы, на тестовой или некритичной рабочей базе в период технологического окна можно выполнить полную очистку кэша планов командной:
DBCC FREEPROCCACHE;
Если в базе данных 1С сдвинута граница рассчитанных итогов далеко в прошлое, платформа не сможет быстро получить остатки и обороты из оптимизированных таблиц итогов (таких как _AccumRgT65666). Платформа будет вынуждена делать ресурсоемкие выборки из основной таблицы движений за все периоды. Проверим и актуализируем итоги:
Чтобы исключить падение всей системы из-за переполнения диска файлами tempdb при выполнении тяжелых регламентных операций, системному администратору необходимо корректно настроить конфигурацию системной базы данных:
tempdb на несколько файлов данных одинакового размера. Оптимальное количество файлов — 1 файл на каждое логическое ядро процессора (но не более 8 файлов). Это существенно снизит конкуренцию потоков за распределение страниц (конфликты SGAM/PFS).tempdb на максимально быстрые накопители (высокоскоростные NVMe SSD), физически отделенные от дисков с основными базами данных и журналами транзакций.В современных версиях MS SQL Server (начиная с SQL Server 2014) разработчики СУБД изменили внутренний алгоритм оценки кардинальности (New Cardinality Estimator). Практика эксплуатации систем на платформе 1С показывает, что для сложных запросов со множеством временных таблиц старый алгоритм оценки работает стабильнее и реже строит катастрофически неоптимальные планы.
Для стабилизации планов запросов СУБД рекомендуется рассмотреть возможность использования следующих настроек:
Выполнив указанные шаги по обслуживанию СУБД и оптимизации настроек платформы 1С, мы гарантированно избавимся от проблемы зависания закрытия месяца и предотвратим критический рост временных файлов базы данных tempdb.