Почему база данных 1С в формате SQL уменьшается в несколько раз после выгрузки и загрузки через DT-файл?

Каждый администратор баз данных или разработчик 1С рано или поздно сталкивается с удивительным явлением. Рабочая база данных на MS SQL Server, имеющая внушительный объем (например, 200 ГБ), после выгрузки в стандартный файл резервной копии платформы .dt и последующей загрузки в новую чистую базу внезапно «худеет» до 30 ГБ. При этом информационная база остается абсолютно работоспособной, все документы, справочники и проводки находятся на месте, а пользователи не замечают никакой пропажи данных.

В этой статье мы подробно разберем, почему СУБД забивает свои таблицы так называемыми «мусорными данными», куда исчезают лишние гигабайты при использовании формата .dt, и как правильно обслуживать базу данных, чтобы не прибегать к экстремальным и потенциально опасным процедурам выгрузки-загрузки.

Причина 1: Внутренняя фрагментация страниц и эффект "Page Splits"

Для того чтобы понять природу этого явления, нам необходимо заглянуть на самый низкий физический уровень хранения данных в MS SQL Server. СУБД хранит всю информацию в файлах данных (с расширением .mdf) блоками фиксированного размера — страницами по 8 КБ. Группа из 8 последовательных страниц образует экстент (64 КБ).

Платформа 1С:Предприятие для обеспечения уникальности записей в таблицах (будь то справочники, документы или записи регистров) использует уникальные идентификаторы — UUID (GUID), которые хранятся в полях с типом _IDRRef. С точки зрения СУБД, эти GUID выступают в качестве первичных ключей, по которым строится кластеризованный индекс таблицы. Главная проблема заключается в том, что новые UUID генерируются платформой хаотично, а не последовательно.

Проанализируем, что происходит внутри MS SQL Server при добавлении новых записей:

1. Когда в систему записывается новый документ, СУБД пытается вставить его в логическое место таблицы в соответствии с его UUID.
2. Поскольку UUID распределены хаотично, запись должна попасть на уже существующую страницу в середине таблицы.
3. Если эта страница объемом 8 КБ уже заполнена на 100%, СУБД не может просто дописать данные в конец файла. Она вынуждена выполнить операцию расщепления страницы (Page Split).
4. В процессе расщепления создается новая пустая страница, на которую переносится ровно половина (50%) записей со старой страницы, после чего вставляется новая запись.

В результате этой операции мы получаем две страницы, каждая из которых заполнена лишь наполовину. Со временем, при интенсивной многопользовательской работе, таблицы базы данных превращаются в «решето», где физический объем страниц огромен, но полезной информации в них — едва ли 30–40%. Это и есть внутренняя фрагментация.

Как на это влияет выгрузка в DT? При выгрузке информационной базы в файл .dt платформа 1С обращается к СУБД и последовательно считывает только логические данные. Вся физическая структура страниц и индексов СУБД игнорируется. При последующей загрузке из .dt в новую пустую базу СУБД записывает эти данные последовательно, плотно упаковывая страницы (практически на 100%). Вся пустота исчезает, и база сжимается в разы.

Причина 2: Специфика хранения итогов регистров в 1С (Нулевые итоги)

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

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

Когда по определенному набору измерений (например, конкретный склад и товар) остаток полностью списывается в ноль, платформа 1С не выполняет операцию DELETE для этой строки в таблице итогов SQL. Сделано это умышленно, с целью оптимизации производительности и избежания жестких взаимоблокировок (locks) при параллельной работе пользователей. Вместо удаления строки платформа просто записывает нулевые значения в ресурсы (количество, сумма) для данного набора измерений.

Со временем в базе накапливаются миллионы таких «нулевых» строк. Они продолжают занимать место на диске и раздувают таблицы итогов. При выгрузке базы в формат .dt платформа 1С применяет встроенные алгоритмы фильтрации: строки с нулевыми значениями ресурсов в таблицах итогов просто не выгружаются. При загрузке эти записи не создаются заново, что дает колоссальную экономию дискового пространства.

Причина 3: Раздувание и деградация индексов

Индексы в базах данных 1С необходимы для быстрого поиска информации и построения отчетов. Однако из-за частых изменений данных и хаотичной вставки записей (UUID) индексы фрагментируются значительно сильнее, чем сами таблицы с данными. Физический размер неэффективных, фрагментированных индексов может превышать размер реальных данных в несколько раз.

При стандартной работе базы данных MS SQL Server выделяет новые страницы под индексы, но неохотно отдает их операционной системе даже после удаления записей. При загрузке базы из файла .dt платформа 1С создает все индексы абсолютно заново («с нуля»). Они выстраиваются в идеальном физическом порядке и занимают минимально возможный объем памяти.

Причина 4: Механизм работы СУБД с дисковым пространством (High-Water Mark)

MS SQL Server работает по принципу максимального сохранения выделенного ему пространства. Если в процессе работы (например, при массовой загрузке картинок, проведении крупного обмена или свертке базы за прошлые годы) файл базы данных .mdf расширился до 200 ГБ, он никогда самостоятельно не уменьшится обратно до 30 ГБ, даже если вы удалите 90% всех данных.

СУБД просто помечает освободившиеся страницы внутри файла как свободные (это называется уровнем High-Water Mark) и планирует использовать их под будущие записи. ОС по-прежнему видит файл размером 200 ГБ. Чтобы вернуть это место операционной системе, администратору СУБД необходимо вручную выполнить операцию сжатия базы данных — SHRINK.

Причина 5: Системный мусор и история конфигурации (Config, ConfigSave)

В процессе разработки, частых обновлений, динамических обновлений в системных таблицах СУБД, таких как Config, ConfigSave и DBSchema, накапливается огромное количество мусора: старые версии конфигураций поставщиков, временные кэши метаданных и незавершенные транзакции обновлений. Эти таблицы могут разрастаться до десятков гигабайт.

При выгрузке в .dt платформа упаковывает исключительно текущую активную конфигурацию, отсекая всю историю изменений, временные файлы и зависшие транзакции, очищая базу от эксплуатационного налета.

Как узнать, какие таблицы занимают больше всего места в вашей базе?

Прежде чем приступать к каким-либо действиям по оптимизации, необходимо провести диагностику. Для этого мы можем запустить специальный SQL-скрипт в среде MS SQL Server Management Studio (SSMS). Этот скрипт покажет реальный размер таблиц, индексов и количество строк в них.

Рассмотрим и выполним следующий запрос:

SELECT 
    obj.name AS TableName,
    SUM(ps.reserved_page_count) * 8 / 1024 AS Reserved_MB,
    SUM(used_page_count) * 8 / 1024 AS Total_MB,
    SUM(
        CASE
            WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END
    ) * 8 / 1024 AS Data_MB,
    SUM (
        CASE
            WHEN (index_id >= 2) THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
            ELSE 0
        END
    ) * 8 / 1024 AS Index_MB,
    SUM(CASE WHEN (index_id < 2) THEN row_count ELSE 0 END) AS RowCounts
FROM
    sys.tables obj 
    LEFT JOIN sys.dm_db_partition_stats ps ON ps.object_id = obj.object_id
GROUP BY
    obj.object_id, obj.name
ORDER BY
    Total_MB DESC;

Этот запрос выведет список всех таблиц базы данных, отсортированных по убыванию их физического объема на диске. Проанализировав результат, мы сможем четко определить «виновников» раздувания базы данных.

Почему регулярное использование DT для сжатия базы — это плохая практика?

Казалось бы, если выгрузка и загрузка через .dt так эффективно сжимает базу, почему бы не делать это регулярно? На самом деле, этот метод имеет критические недостатки:

1. Огромное время простоя системы. Для базы объемом 200 ГБ процесс выгрузки и последующей загрузки может занять от нескольких часов до суток, в течение которых пользователи не смогут работать.
2. Риск потери данных. Формат .dt изначально создавался как средство переноса небольших баз данных между различными СУБД. На базах объемом более 50-100 ГБ процесс загрузки из .dt может аварийно завершиться из-за нехватки оперативной памяти или внутренних ошибок платформы, оставив вас без работающей базы.
3. Нагрузка на сервер. Процесс упаковки и распаковки данных создает колоссальную нагрузку на дисковую подсистему и процессор сервера.

Правильный регламент обслуживания базы данных без использования DT

Чтобы поддерживать оптимальный размер базы данных 1С и её высокую производительность без рискованных манипуляций с файлами .dt, мы рекомендуем настроить регулярный план обслуживания (Maintenance Plan) в MS SQL Server, состоящий из следующих шагов:

Шаг 1: Регулярное обслуживание индексов (Rebuild и Reorganize)

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

• Реорганизацию индексов (Reorganize) — если фрагментация составляет от 5% до 30% (выполняется «на лету» без блокировки таблиц).
• Перестроение индексов (Rebuild) — если фрагментация превышает 30% (выполняется во внерабочее время, полностью пересоздает структуру индекса, упаковывая страницы).

Шаг 2: Свертка и очистка итогов регистров в 1С

Для удаления записей с нулевыми итогами не обязательно выгружать базу в .dt. Достаточно периодически запускать штатные процедуры платформы:

• Использование встроенной обработки «Управление итогами» для пересчета итогов на актуальную дату.
• Запуск тестирования и исправления информационной базы (ТиИС) через Конфигуратор с включенными опциями «Реиндексация таблиц» и «Пересчет итогов».

Шаг 3: Разумное использование операции SHRINK

Если в базе проводились масштабные работы по удалению старых данных или свертке прошлых периодов, выполните операцию сжатия файла данных:

1. Запустите MS SQL Server Management Studio.
2. Нажмите правой кнопкой мыши по базе данных, выберите Tasks -> Shrink -> Database (или Files для сжатия конкретно файла .mdf).
3. Обратите внимание: выполнять SHRINK необходимо строго до перестроения индексов, так как само сжатие сильно фрагментирует базу данных. Оптимальный порядок: Сжатие (Shrink) -> Перестроение индексов (Rebuild).

Соблюдение этих простых правил позволит вам держать размер базы данных под полным контролем, обеспечивая максимальную скорость её работы и исключая необходимость опасных манипуляций с выгрузками в формат .dt.