Каждый администратор крупных производственных баз на платформе 1С:Предприятие 8.3 рано или поздно сталкивается с ситуацией, когда регламентный расчет себестоимости в конфигурации Управление производственным предприятием (УПП 1.3), ранее занимавший несколько часов, внезапно начинает выполняться двое суток или зависает намертво. При этом внешние параметры системы не меняются: объем документов стабилен, пользовательская нагрузка отсутствует, а стандартное тестирование и исправление (ТиИ) или выгрузка в файл *.dt больше не приносят желаемого ускорения.
В этой статье мы подробно разберем, почему возникает эта проблема, проанализируем внутренние механизмы взаимодействия платформы 1С и СУБД MS SQL Server, и пошагово внедрим комплекс мер для возвращения высокой скорости расчетов.
---
На начальных этапах эксплуатации базы данных выгрузка в файл резервной копии *.dt с последующей загрузкой действительно творит чудеса. Секрет этой процедуры прост: при импорте файла *.dt платформа 1С полностью пересоздает структуру таблиц в SQL-базе, физически записывает данные последовательно, заново строит абсолютно все индексы и полностью обновляет статистику распределения данных.
Однако по мере роста базы данных (особенно при достижении объема в 100–180 Гб и более) этот метод становится неприменимым из-за огромных временных затрат. Обычное тестирование и исправление также перестает помогать, если конфигурация обслуживания СУБД настроена неоптимально. Разберем детально, какие системные факторы влияют на производительность расчета себестоимости.
---
Частой ошибкой системных администраторов является настройка планов обслуживания MS SQL Server на ежедневную реорганизацию индексов (Reorganize) вместо их перестроения (Rebuild).
Реорганизация индекса — это деликатный процесс, который дефрагментирует физические страницы на листовом уровне индекса без его полной блокировки. Если фрагментация индекса превышает 30%, реорганизация становится практически бесполезной. Тяжелый расчет себестоимости выполняет миллионы записей во временные и постоянные таблицы, вызывая катастрофическое расщепление страниц данных (Page Splits). Индексы разрушаются быстрее, чем СУБД успевает их «причесывать».
Для восстановления производительности нам необходимо настроить полное еженедельное (или ежемесячное непосредственно перед расчетом себестоимости) перестроение индексов. Проанализируем пример SQL-скрипта, который можно запускать в качестве шага SQL Server Agent:
-- Перестроение всех индексов для таблиц с высокой фрагментацией
DECLARE @TableName NVARCHAR(256)
DECLARE @SQL NVARCHAR(MAX)
DECLARE TableCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'ALTER INDEX ALL ON [' + @TableName + '] REBUILD WITH (FILLFACTOR = 85, ONLINE = OFF);'
EXEC sp_executesql @SQL
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Параметр FILLFACTOR = 85 указывает СУБД оставлять 15% свободного места на страницах данных при перестроении индекса. Это предотвратит моментальное расщепление страниц при массовой записи движений по регистрам накопления в процессе расчета себестоимости.
---
Одной из ключевых причин внезапного падения скорости закрытия месяца является генерация неоптимального плана запроса оптимизатором MS SQL Server. Это происходит из-за устаревшей статистики.
Во время итерационных расчетов себестоимости данные в таблицах регистра накопления Затраты на выпуск (бухгалтерский учет) меняются лавинообразно. Если оптимизатор СУБД думает, что в таблице содержится 100 строк (на основе старой статистики), а по факту их там уже 10 000 000, он выберет алгоритм сканирования всей таблицы (Table Scan) вместо использования индекса (Index Seek).
Для решения этой проблемы необходимо регулярно выполнять обновление статистики с полным сканированием (Fullscan) и очищать кэш планов запросов, чтобы СУБД построила новые, адекватные планы выполнения.
Выполним следующий запрос в среде SQL Server Management Studio (SSMS) перед запуском расчета себестоимости:
-- Обновление статистики для всей базы данных с полным сканированием
EXEC sp_updatestats;
-- Очистка процедурного кэша (сброс старых неоптимальных планов)
DBCC FREEPROCCACHE;
Применение DBCC FREEPROCCACHE заставит SQL Server при следующем обращении к таблицам построить планы запросов заново на основе свежей, только что обновленной статистики.
---
Если ваша база данных УПП работает на MS SQL Server версии 2014 или выше, то причиной резкого замедления может быть новый алгоритм оценки мощности множеств (Cardinality Estimator). Он отлично работает с простыми запросами, но часто допускает критические ошибки на сложных, многократно вложенных запросах УПП, которые используют временные таблицы.
Чтобы вернуть стабильную скорость выполнения запросов, рекомендуется принудительно включить старый оптимизатор запросов для рабочей базы 1С. Выясним, как это сделать с помощью системной команды:
-- Для SQL Server 2016 и выше:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATOR = ON;
Если база работает на SQL Server 2014, аналогичного эффекта можно добиться переключением уровня совместимости базы данных (Compatibility Level) в значение 110 (SQL Server 2012) через свойства базы данных в SSMS.
---
Если оптимизация на уровне СУБД выполнена, но расчет все равно идет медленнее обычного, обратим внимание на программную часть конфигурации. Запустим замер производительности в Конфигураторе 1С во время выполнения расчета себестоимости.
Очень часто "бутылочным горлышком" в партионном учете УПП является последовательный обход коллекций с поиском по структуре в цикле:
// Пример неоптимального типового кода
Пока ВыборкаПоВыпуску.НайтиСледующий(СтруктураПоиска) Цикл
// Выполнение расчетов затрат
КонецЦикла;
При огромном объеме выпуска (особенно с использованием характеристик и серий номенклатуры) этот метод превращает алгоритм в квадратичную сложность. Программистам 1С рекомендуется оптимизировать этот участок общего модуля ПроцедурыРасчетаСебестоимостиВыпуска следующим образом: выгрузить выборку в таблицу значений, проиндексировать ее по ключевым полям и использовать метод НайтиСтроки():
// Оптимизированный вариант работы с индексами в ОЗУ
ТаблицаВыпуска = ВыборкаПоВыпуску.Выгрузить();
ТаблицаВыпуска.Индексы.Добавить("Номенклатура,ХарактеристикаНоменклатуры");
НайденныеСтроки = ТаблицаВыпуска.НайтиСтроки(СтруктураПоиска);
Для Каждого СтрокаТЗ Из НайденныеСтроки Цикл
// Быстрая обработка данных из проиндексированной таблицы значений
КонецЦикла;
Также немаловажным фактором является объем регистра накопления Затраты на выпуск (бухгалтерский учет). Итерационный алгоритм расчета себестоимости может многократно записывать промежуточные движения по одним и тем же измерениям, из-за чего объем регистра необоснованно раздувается в несколько раз.
Рекомендуется использовать механизм свертки движений после успешного выполнения расчета себестоимости. Объединение записей с одинаковыми аналитиками позволяет уменьшить объем физической таблицы регистра в 3–5 раз без потери важной информации для анализа.
---
Поскольку расчет себестоимости генерирует гигантское количество временных таблиц 1С (которые физически создаются в системной базе TempDB), производительность дисковой подсистемы и конфигурация этой базы играют решающую роль.
Проанализируем ключевые настройки TempDB, которые необходимо выполнить:
TempDB одинакового размера. Оптимальное количество файлов — по числу физических ядер процессора, но не более 8. Это исключит конкуренцию потоков за распределение страниц (PFS/SGAM contention).TempDB на самые быстрые диски (идеально — NVMe SSD), изолированно от файлов основной рабочей базы.TempDB фиксированным объемом (например, по 256 или 512 МБ), а не в процентах. Это исключит микрозависания СУБД в моменты, когда СУБД требуется выделить дополнительное дисковое пространство под тяжелый запрос.TF 1117 (равномерное расширение всех файлов в файловой группе) и TF 1118 (выделение полных экстентов для уменьшения конкуренции выделения страниц). Для SQL Server 2016 и выше эти механизмы работают по умолчанию.TF 2371. Без него автоматическое обновление статистики SQL Server срабатывает только при изменении 20% строк таблицы. Для крупных таблиц УПП этот порог недостижим годами, из-за чего статистика перманентно устаревает. Флаг TF 2371 делает этот порог динамическим: чем больше таблица, тем меньший процент измененных строк требуется для запуска обновления статистики.---
Мы подробно разобрали, как решить проблему резкого падения производительности расчета себестоимости в 1С:УПП. Чтобы вернуть системе былую скорость, действуйте последовательно:
FILLFACTOR.DBCC FREEPROCCACHE.Эти комплексные меры позволят стабилизировать работу СУБД, исключат неожиданные просадки скорости и сократят время расчета себестоимости до рекордных показателей.