Как быстро удалить десятки миллионов записей в MS SQL Server для базы 1С

← На главную

При обслуживании крупных информационных баз 1С разработчики и администраторы периодически сталкиваются с необходимостью массовой очистки устаревших данных. Если требуется удалить десятки миллионов документов или записей регистров с отбором по дате, стандартные средства платформы (такие как метод УдалитьОбъекты() или фоновое удаление в цикле) оказываются неприменимы из-за высокой длительности выполнения. Попытка выполнить простую команду DELETE напрямую в MS SQL Server также может занять долгие часы, поскольку СУБД вынуждена на каждую удаляемую строку перестраивать все существующие индексы таблицы и заполнять журнал транзакций.

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

Причина медленной работы стандартного оператора DELETE

Выясним причину, почему обычный запрос на удаление выполняется крайне медленно. При выполнении инструкции DELETE над крупной таблицей СУБД выполняет следующие ресурсоемкие операции:

Модификация некластерных индексов: При удалении каждой строки MS SQL вынужден находить и помечать записи во всех некластерных индексах таблицы, что создает колоссальную нагрузку на дисковый ввод-вывод (I/O).
Запись в журнал транзакций (LDF): Операция DELETE относится к полностью журналируемым операциям. Каждая удаляемая строка фиксируется в транзакционном логе.
Блокировки и накладные расходы: По умолчанию СУБД накладывает поэлементные или постраничные блокировки, тратя ресурсы на их эскалацию.
Версионирование строк: В базах данных 1С обычно включен режим READ_COMMITTED_SNAPSHOT (RCSI), из-за чего удаляемые версии строк копируются в системную базу данных tempdb.

Самый эффективный способ: Отключение индексов и монопольная блокировка

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

Суть метода заключается в том, чтобы отключить все некластерные индексы перед выполнением удаления, выполнить удаление одной командой с хинтом блокировки таблицы, а затем выполнить полное пересоздание (rebuild) индексов.

Разберем данный процесс по шагам:

Отключение индексов таблицы:
Для отключения всех индексов на целевой таблице выполним T-SQL команду DISABLE. В отключенном состоянии индексы не обновляются при операциях изменения данных:
ALTER INDEX ALL ON _Document123 DISABLE;
При этом кластерный индекс (если удаление происходит не по нему) можно оставить активным или временно оставить только индекс по полю даты для ускорения поиска отбираемых строк.
Выполнение удаления с монопольной блокировкой таблицы:
Для предотвращения эскалации блокировок и ускорения работы укажем хинт WITH (TABLOCKX), который блокирует всю таблицу целиком на время выполнения транзакции:
```
DELETE FROM _Document123 WITH (TABLOCKX)
WHERE _DateDev < '2020-01-01';
```
Благодаря отключенным индексам СУБД производит удаление строк напрямую из первичной структуры данных, не отвлекаясь на поддержание порядка в индексах.
Включение и пересоздание (Rebuild) индексов:
После завершения удаления необходимо восстановить работоспособность индексов. Включение индекса в MS SQL Server выполняется путем его полной пересборки:
ALTER INDEX ALL ON _Document123 REBUILD;

Практический результат: На тестовых замерах удаление 30 миллионов документов из таблицы размером более 15 ГБ без индексов занимает около 16 минут, а последующее пересоздание всех индексов — около 28 минут. Итоговое время выполнения задачи составляет менее 45 минут, что легко вписывается в технологическое окно простоя.

Альтернативный подход: Удаление пакетами (порциями)

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

Посмотрим на пример T-SQL скрипта для пакетного удаления:


DECLARE @RowsDeleted INT = 1;

WHILE @RowsDeleted > 0
BEGIN
    DELETE TOP (100000) 
    FROM _Document123 WITH (TABLOCKX)
    WHERE _DateDev < '2020-01-01';

    SET @RowsDeleted = @@ROWCOUNT;
END

При использовании этого метода размер порции (например, 50 000 – 100 000 записей) подбирается эмпирическим путем. Главное преимущество пакетного удаления — возможность визуально отслеживать прогресс выполнения и не блокировать всю базу данных на длительное время.

Оптимизация параметров СУБД и инфраструктуры

Разберем дополнительные системные настройки MS SQL Server, которые помогут максимально ускорить процесс очистки крупных таблиц:

Модель восстановления базы данных (Recovery Model): Перед началом массового удаления рекомендуется перевести модель восстановления базы данных в режим SIMPLE (Простая). Это предотвратит переполнение файла журнала транзакций (.ldf). После окончания работ модель восстановления переводится обратно в FULL с обязательным созданием резервной копии.
Управление режимом RCSI: На время выполнения регламентной операции имеет смысл временно отключить режим изоляции версионирования строк, чтобы избежать переполнения tempdb: ALTER DATABASE [ИмяБазы] SET READ_COMMITTED_SNAPSHOT OFF; После завершения всех процедур режим следует включить обратно: ALTER DATABASE [ИмяБазы] SET READ_COMMITTED_SNAPSHOT ON;
Предварительное расширение файлов: Убедитесь, что файлы базы данных, журнала транзакций и tempdb имеют достаточное свободное место. Автоматический прирост файлов (Autogrowth) во время выполнения операции DELETE вызывает паузы в работе СУБД.
Автоматизация формирования скриптов в 1С: Чтобы получить точные имена таблиц и индексов в СУБД, используйте метод встроенного языка 1С ПолучитьСтруктуруХраненияБазыДанных(). Это позволит автоматически сформировать T-SQL скрипты для отключения и включения нужных индексов конкретного документа или регистра.

Таким образом, сочетание отключения некластерных индексов перед удалением, применение прямых T-SQL инструкций с блокировкой таблицы TABLOCKX и последующий перерасчет индексов представляют собой наиболее эффективный и проверенный способ очистки завышенных объемов данных в 1С.