Как ускорить длительную реструктуризацию регистра бухгалтерии при переходе на 1С:Бухгалтерию 3.0?

При масштабном обновлении конфигураций с большим объемом учетных данных (например, переход с редакции 2.0 на 3.0 для базы размером в сотни гигабайт и десятками миллионов записей субконто) процесс реструктуризации таблиц базы данных в MS SQL Server может затягиваться на несколько суток. Выясним причину такого поведения и разберем по шагам, как локализовать и устранить зависание.

Анализ проблемы: почему зависает служебный запрос платформы

Проанализируем ситуацию, возникающую в процессе реорганизации таблиц. Во время реструктуризации платформа 1С создает временные таблицы с суффиксом *NG (New Generation) и выполняет проверку логической и ссылочной целостности данных. В частности, для регистра бухгалтерии выполняется проверка отсутствия «осиротевших» записей в таблице значений субконто (_AccRgED...NG), для которых отсутствует родительская запись в основной таблице движений (_AccRg...NG).

Посмотрим на пример типичного запроса проверки целостности, формируемого платформой:

SELECT TOP 1
    T1._RecorderTRef,
    T1._RecorderRRef
FROM dbo._AccRgED479NG T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._AccRg443NG T2 WITH(NOLOCK)
    ON T1._RecorderTRef = T2._RecorderTRef 
    AND T1._RecorderRRef = T2._RecorderRRef 
    AND T1._LineNo = T2._LineNo
WHERE T2._RecorderTRef IS NULL 
    AND T2._RecorderRRef IS NULL

Рассмотрим подробнее, что происходит в оптимизаторе MS SQL Server:

1. Наличие конструкции SELECT TOP 1 вводит оптимизатор запросов СУБД в заблуждение: предполагается, что несовпадающая строка будет обнаружена практически сразу на первых итерациях.
2. Из-за отсутствия актуальной статистики по только что созданным временным таблицам *NG оптимизатор выбирает физический оператор Nested Loops (вложенные циклы) вместо Hash Join или Merge Join.
3. Во внешнем цикле выполняется полное сканирование таблицы субконто (Index Scan по 46 миллионам строк), и для каждой строки инициируется сканирование таблицы движений (Index Scan по 10 миллионам строк).
4. Если данные согласованы и битых записей нет вовсе, СУБД вынуждена выполнить перебор полного декартова произведения, что приводит к многосуточному выполнению одного-единственного запроса.

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

Проанализируем программные изменения в платформе 1С:Предприятие. В ранних релизах ветки 8.3.10 данный алгоритм генерации запросов валидации содержал архитектурные недоработки. В версиях 8.3.11 и выше разработчики платформы существенно переработали алгоритмы обновления и внедрили механизм оптимизированной серверной реструктуризации.

1. Переведем выполнение реструктуризации на актуальный стабильный релиз платформы 1С (не ниже 8.3.11).
2. Серверный механизм реструктуризации выполняет модификацию структуры колонок непосредственно средствами СУБД через команды ALTER TABLE без полного переливания данных во временные таблицы *NG, что экономит часы работы на больших базах.

Способ 2. Оптимизация плана выполнения на уровне MS SQL Server (Plan Guide)

Если обновление платформы в данный момент невозможно, а процесс уже запущен и завис, мы можем принудительно заставить оптимизатор СУБД сменить тип соединения таблиц. Разберем по шагам создание структуры плана запроса (Plan Guide):

1. Зафиксируем точный текст зависшего запроса из профайлера или системных представлений СУБД.
2. Создадим план выполнения с подсказкой принудительного использования хэш-соединения с помощью системной хранимой процедуры sp_create_plan_guide:
   Скопировать код

   
   EXEC sp_create_plan_guide
       @name = N'Fix_AccRg_Restructure_Join',
       @stmt = N'SELECT TOP 1 T1._RecorderTRef, T1._RecorderRRef FROM dbo._AccRgED479NG T1 WITH(NOLOCK) LEFT OUTER JOIN dbo._AccRg443NG T2 WITH(NOLOCK) ON T1._RecorderTRef = T2._RecorderTRef AND T1._RecorderRRef = T2._RecorderRRef AND T1._LineNo = T2._LineNo WHERE T2._RecorderTRef IS NULL AND T2._RecorderRRef IS NULL',
       @type = N'SQL',
       @module_or_batch = NULL,
       @params = NULL,
       @hints = N'OPTION (HASH JOIN, MERGE JOIN)';
   

3. Применение хэш-соединения позволяет СУБД обработать 46 миллионов строк за минуты вместо сотен часов работы вложенных циклов.

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

Если запрос уже выполняется в режиме Nested Loops, узким местом является операция Index Scan во внутреннем цикле. Мы можем ускорить работу СУБД «на лету» без прерывания транзакции:

1. Определим имена созданных временных таблиц в базе данных (по суффиксу *NG).
2. Создадим составной некластеризованный индекс по полям связи непосредственно на таблице движений:
   Скопировать код

   
   CREATE NONCLUSTERED INDEX IX_Temp_FastJoin 
   ON dbo._AccRg443NG (_RecorderTRef, _RecorderRRef, _LineNo);
   

3. Наличие такого индекса трансформирует операцию во внутреннем цикле из полного сканирования таблицы (Index Scan) в точечный поиск (Index Seek), что многократно ускоряет выполнение цикла.

Способ 4. Подготовка инфраструктуры и данных информационной базы

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

1. Модель восстановления: Обязательно переведем базу данных в режим SIMPLE (Простая) на время обновления, чтобы избежать лавинообразного роста журнала транзакций LDF.
2. Оптимизация TempDB: На время перестроения таблиц создается колоссальная нагрузка на системную базу tempdb. Разместим файлы tempdb на скоростных NVMe-накопителях и создадим несколько файлов данных, соответствующих количеству физических ядер сервера.
3. Аппаратные ресурсы: Для сокращения времени регламентного окна реструктуризацию исторически тяжелых баз рекомендуется выполнять на выделенном стенде с максимальной однопоточной производительностью процессора и быстрой дисковой подсистемой.
4. Свертка и очистка данных: При переходе с редакции 2.0 на 3.0 для базы размером более 200 Гб оптимальным решением является предварительная свертка базы с удалением неактуальных периодов либо ввод начальных остатков в чистую конфигурацию редакции 3.0.