При работе с крупными информационными базами 1С на MS SQL Server можно столкнуться со ситуацией, когда физический размер индексов одной таблицы (например, регистра накопления партионного учета или НДС) в несколько раз превышает размер самих данных. Проанализируем, почему возникает эта проблема и какими способами можно уменьшить объем индексов без вреда для производительности системы.
Основные причины избыточного размера индексов
Выясним, какие факторы приводят к существенному разрастанию индексных таблиц в базе данных:
Дублирование индекса первого измерения: Платформа 1С автоматически создает первичный кластерный индекс для таблицы итогов регистра остатков. В начале этого индекса уже содержится первое измерение. Если в конфигураторе для первого измерения дополнительно установить флаг Индексировать, платформа создаст отдельный некластерный индекс, который фактически продублирует существующий и будет напрасно занимать место на диске.
Снятие отборов и лишняя индексация измерений: Установка свойства Индексировать на всех измерениях подряд ведет к формированию множества отдельных некластерных индексов на уровне СУБД.
Наличие незакрытых остатков («хвостов»): В остаточных регистрах накопления из-за ошибок учета (например, когда количество списалось в ноль, а сумма или НДС остались) комбинации измерений остаются в таблице итогов. Каждая такая незакрытая строка продолжает индексироваться.
Составные типы данных в измерениях: Если измерение имеет составной тип (например, содержит несколько видов документов или справочников), СУБД создает под него несколько физических колонок, что увеличивает ширину всех создаваемых по этому измерению индексов.
Фрагментация страниц в MS SQL: Из-за постоянного добавления и изменения записей происходит расщепление страниц индексов (page splits). Без регулярного обслуживания индексные страницы заполняются полезными данными лишь частично, увеличивая физический размер индекса на диске в 2–3 раза.
Способ 1. Оптимизация состава индексов в конфигураторе
Проанализируем состав измерений регистра накопления в конфигураторе 1С:
Откроем свойства регистра накопления (например, РегистрыНакопления.НДСПартииТоваров) и перейдем к списку измерений.
Проверим свойство Индексировать у первого измерения регистра. Если там установлено значение Индексировать или Индексировать с доп. упорядочиванием, изменим его на Не индексировать, так как первое измерение уже входит в первичный кластерный индекс.
Снимем флаг индексирования с тех измерений, по которым в системе не выполняются частые отдельные отборы в запросах или отчетах.
Применим изменения конфигурации базы данных.
Способ 2. Устранение учетных расхождений и пересчет итогов
Проверим закрытие регистра накопления и очистим таблицу итогов от нулевых записей:
Выполним запрос к виртуальной таблице остатков регистра, чтобы проверить наличие записей с нулевым количеством, но ненулевой суммой/НДС, или наоборот.
Проведем технологическую корректировку остатков, зарыв незакрытые партии и списав зависшие суммовые остатки.
Выполним пересчет итогов через механизм Тестирование и исправление или программно с помощью метода РегистрыНакопления.[ИмяРегистра].ПересчитатьИтоги(). Это позволит удалить неактуальные записи из таблицы итогов СУБД.
Рассмотрим пример программного вызова пересчета итогов для конкретного регистра накопления:
Способ 3. Обслуживание индексов на уровне MS SQL Server
Разберем действия по обслуживанию базы данных на стороне СУБД:
Разделяем операции обслуживания: не следует регулярно использовать процедуру сжатия базы (Shrink), так как она приводит к критической фрагментации индексов.
Настраиваем регламентный план обслуживания MS SQL Server для регулярного выполнения перестройки (ALTER INDEX REBUILD) или реорганизации (ALTER INDEX REORGANIZE) индексов в зависимости от степени их фрагментации.
При перестройке индексов задаем параметр FILLFACTOR (например, 80–90%), чтобы оставить запас свободного места на страницах и снизить частоту расщепления страниц при активной записи.
В современных версиях MS SQL Server (включая выпуск Standard начиная с SQL Server 2016) оценим возможность применения сжатия данных и индексов (Data Compression).