Перенос баз данных 1С:Предприятие на более новые версии СУБД — стандартная задача для администратора. Однако в процессе миграции часто возникает странная ситуация: одна и та же база данных на старом сервере MS SQL Server 2012 занимала, например, 15 Гигабайт, а после переноса на MS SQL Server 2017 ее файл данных .mdf мгновенно «раздулся» до 25 Гигабайт. При этом объем реальных пользовательских данных внутри 1С никак не изменился.
Давайте подробно разберем, почему так происходит, какие внутренние механизмы MS SQL Server за это отвечают, и как вернуть базу данных к оптимальному размеру без потери производительности.
Это главная архитектурная причина увеличения дискового пространства на новых версиях SQL Server, начиная с версии 2016.
Рассмотрим, как СУБД выделяет место под таблицы на низком уровне. Память в SQL Server делится на страницы по 8 КБ. Восемь таких страниц объединяются в блок — экстент (Extent) размером 64 КБ. Экстенты бывают двух типов:
В MS SQL Server 2012 для экономии места по умолчанию действовал режим, при котором первые 8 страниц для любой новой таблицы выделялись из смешанных экстентов. Если в таблице была всего одна строка, она занимала на диске физические 8 КБ.
Начиная с MS SQL Server 2016 и 2017, параметр MIXED_PAGE_ALLOCATION по умолчанию отключен. Теперь для абсолютно любого нового объекта (даже если это пустая таблица) СУБД сразу выделяет целый однородный экстент размером 64 КБ.
Специфика баз данных 1С заключается в том, что они содержат тысячи таблиц: каждый справочник, документ, регистр сведений или накопления, а также их табличные части физически представляют собой отдельные таблицы в SQL. При переносе базы через выгрузку/загрузку .dt или при первом ребилде индексов на новом сервере эти таблицы пересоздаются. Выделение минимум 64 КБ на каждую, даже самую мелкую таблицу, приводит к лавинообразному росту физического размера файла .mdf.
Если для вас критически важно сэкономить место на диске, мы можем вернуть старое поведение SQL Server для конкретной базы данных с помощью следующего запроса:
ALTER DATABASE [Имя_Вашей_Базы] SET MIXED_PAGE_ALLOCATION ON;
После выполнения этой команды необходимо запустить полное обслуживание базы данных: перестроить индексы и выполнить сжатие файлов.
Проанализируем политику выделения дискового пространства «на вырост». В MS SQL Server 2012 шаг автоувеличения файлов данных по умолчанию часто составлял всего 1 МБ. База росла медленно, сильно фрагментировалась на физическом диске, но размер файла жестко соответствовал реальному объему информации.
В MS SQL Server 2017 стандартный шаг автоувеличения составляет уже 64 МБ (или более, в зависимости от настроек инстанса). Когда 1С во время массовой загрузки или проведения документов запрашивает запись данных, СУБД выделяет дисковое пространство крупными блоками. Внутри файла .mdf резервируется много неиспользуемого места, которое операционная система считает занятым.
Выяснить реальный объем занятого места можно с помощью встроенной системной процедуры. Выполним следующий запрос в среде SQL Server Management Studio (SSMS):
USE [Имя_Вашей_Базы];
GO
EXEC sp_spaceused;
В результатах запроса обратите внимание на параметр unallocated space — это и есть то пустое место внутри файла базы, которое зарезервировано СУБД, но фактически не заполнено данными.
Начиная с версии SQL Server 2016, появилась отличная технология отслеживания производительности — Query Store (Хранилище запросов). Она собирает подробную статистику выполнения планов запросов, время их работы и системные ресурсы.
Если на новом сервере SQL Server 2017 при создании базы данных эта опция была включена (либо включена администратором вручную), вся собранная статистика записывается непосредственно внутрь системных таблиц вашей базы в файле .mdf. На высоконагруженных системах 1С за несколько дней активной работы Query Store может легко добавить к объему базы от 5 до 20 Гигабайт чисто технических метаданных.
Проверить состояние этой опции и при необходимости отключить ее можно в свойствах базы данных (раздел Database Properties -> Query Store) или с помощью T-SQL кода:
ALTER DATABASE [Имя_Вашей_Базы] SET QUERY_STORE = OFF;
Параметр Fill Factor определяет, какой процент свободного места на страницах данных будет оставаться пустым при создании или перестроении индексов. Это свободное место необходимо, чтобы новые записи вставлялись быстрее и без физического разбиения страниц.
Если на новом сервере SQL Server 2017 в глобальных настройках СУБД установлен дефолтный Fill Factor, отличный от 100% (например, 80%), то при первой же регламентной процедуре перестроения индексов (Index Rebuild) СУБД принудительно оставит по 20% свободного места на каждой странице данных. В базах данных 1С, где индексов всегда очень много, это приводит к мгновенному увеличению размера файла данных примерно на 20–25%.
Проверить глобальную настройку сервера можно через свойства сервера в SSMS (вкладка Database Settings -> Default index fill factor) или запросом:
SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
SP_CONFIGURE 'fill factor';
Значение 0 или 100 означает, что страницы заполняются полностью (за исключением верхнего уровня индекса), что обеспечивает максимальную плотность хранения данных.
Важную роль играет сам способ переноса информации. Рассмотрим два основных варианта:
Как показывает практика успешных миграций, наиболее надежным решением является подготовка базы данных еще на старом сервере. Разберем по шагам правильный алгоритм действий.
Выполним последовательно следующие шаги для минимизации размера базы данных при переносе:
Шаг 1. Обслуживание базы данных на исходном сервере (SQL 2012)
Перед созданием резервной копии необходимо очистить базу от неиспользуемых индексов, выполнить реиндексацию и принудительно сжать файлы базы данных. Для этого используем стандартный план обслуживания СУБД (Maintenance Plan) или запускаем команды вручную:
-- Шаг 1.1. Перестроение индексов с коэффициентом заполнения 100%
ALTER INDEX ALL ON [Имя_Вашей_Базы] REBUILD WITH (FILLFACTOR = 100);
-- Шаг 1.2. Сжатие базы данных для освобождения нераспределенного места
DBCC SHRINKDATABASE ([Имя_Вашей_Базы]);
Шаг 2. Создание резервной копии и восстановление на SQL Server 2017
Создаем резервную копию .bak на старом сервере и восстанавливаем ее на новом сервере SQL Server 2017 средствами самой СУБД (Restore Database), избегая использования выгрузки .dt.
Шаг 3. Изменение уровня совместимости и настроек выделения страниц
После успешного восстановления базы на новом сервере SQL Server 2017, повысим уровень совместимости базы данных (Compatibility Level) и, если на диске критически мало места, включим старый режим распределения страниц:
-- Повышаем уровень совместимости базы данных до SQL Server 2017 (140)
ALTER DATABASE [Имя_Вашей_Базы] SET COMPATIBILITY_LEVEL = 140;
-- Включаем экономный режим выделения страниц
ALTER DATABASE [Имя_Вашей_Базы] SET MIXED_PAGE_ALLOCATION ON;
Шаг 4. Финальное обслуживание на новом сервере
После изменения настроек совместимости и механизма страниц, обязательно проведем повторную реиндексацию и окончательно сожмем файл данных, чтобы вернуть излишне зарезервированное место операционной системе:
-- Перестраиваем индексы для применения новых параметров выделения страниц
ALTER INDEX ALL ON [Имя_Вашей_Базы] REBUILD;
-- Сжимаем файлы базы данных
DBCC SHRINKDATABASE ([Имя_Вашей_Базы]);
После выполнения этой последовательности действий размер вашего файла .mdf на новом сервере MS SQL Server 2017 вернется к своим физическим границам и будет максимально приближен к исходному размеру на старой системе, при этом сохранив все преимущества обновленной СУБД.