Приветствуем всех администраторов баз данных и разработчиков 1С! Настройка регламентных процедур на уровне СУБД — это фундамент стабильной работы любой крупной информационной системы, особенно если речь идет о тяжелых конфигурациях уровня 1С:ERP. Часто в попытках «выжать» максимум производительности администраторы внедряют нестандартные схемы обслуживания, которые на первый взгляд кажутся эффективными, но на практике приводят к деградации производительности.
В этой статье мы подробно разберем одну из таких схем — обслуживание индексов небольшими порциями каждые 15 минут — и выясним, почему этот подход является опасным антипаттерном. Мы проанализируем влияние фрагментации на современные SSD, разберем критическую важность обновления статистик и рассмотрим новые особенности платформы 1С:Предприятие 8.3.22, которые напрямую влияют на обслуживание индексов СУБД.
Рассмотрим ситуацию, с которой часто сталкиваются на практике. Имеется рабочая база 1С:ERP на СУБД MS SQL Server. Для борьбы с фрагментацией администратор настроил регламентное задание, которое запускается каждые 15 минут. Логика работы скрипта следующая: он выбирает ТОП-10 самых фрагментированных индексов (размером более 100 страниц и фрагментацией выше 5%) и выполняет их реорганизацию (если фрагментация менее 30%) или перестроение с последующим обновлением статистики.
Для выбора индексов используется следующий T-SQL запрос:
SELECT
TOP 10
OBJECT_NAME(ind.OBJECT_ID) AS TableName,
ind.name AS IndexName,
indexstats.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,
indexstats.page_count AS page_count
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE
indexstats.avg_fragmentation_in_percent >= 5
AND indexstats.page_count >= 100
ORDER BY
indexstats.page_count * indexstats.avg_fragmentation_in_percent DESC;
На первый взгляд схема выглядит рабочей и безопасной: скрипт отрабатывает за 1–2 секунды, база не блокируется, тяжелые индексы постепенно приводятся в порядок. Однако при детальном анализе физических процессов внутри СУБД всплывает целый ряд скрытых проблем, которые могут напрямую влиять на замедление работы пользователей (например, когда проведение документов занимает по 5–7 секунд).
Проанализируем детально, к каким негативным последствиям приводит постоянное выполнение процедур дефрагментации в фоновом режиме параллельно с работой пользователей:
sys.dm_db_index_physical_stats, даже запущенная в режиме по умолчанию, вынуждена сканировать распределение страниц индексов. На базах объемом в сотни гигабайт даже простой опрос этой функции раз в 15 минут создает постоянную паразитную нагрузку на дисковую подсистему и процессор, вытесняя полезные данные из оперативной памяти СУБД.REBUILD) и реорганизации (REORGANIZE) индексов являются крайне транзакционно-емкими. Постоянное внесение изменений в структуру индексов в течение рабочего дня генерирует колоссальный объем записей в транзакционный лог. Это перегружает диски операциями записи и сильно усложняет процедуру резервного копирования логов транзакций.REORGANIZE позиционируется как онлайновая и не накладывает долгосрочных монопольных блокировок на таблицы, она активно утилизирует процессорное время и дисковый ввод-вывод (I/O). Запуск этого процесса в рабочее время совпадает с пиками пользовательской активности (например, при проведении документов или формировании тяжелых отчетов), что вызывает микрофризы и снижает общую отзывчивость системы.Ghost Cleanup, который работает автоматически и не требует принудительного запуска дефрагментации.Существует мнение, что при использовании быстрых твердотельных накопителей (SSD/NVMe) фрагментация индексов вообще не играет роли. Давайте выясним, насколько это утверждение близко к истине.
Действительно, для точечных запросов типа Index Seek (поиск конкретной записи по ключу, например, получение остатка по одному товару) фрагментированность индекса на SSD практически не снижает скорость. У SSD отсутствует физическая задержка на позиционирование магнитных головок чтения, поэтому чтение логически разрозненных страниц происходит мгновенно.
Однако ситуация кардинально меняется при выполнении сканирующих запросов типа Index Scan. 1С:Предприятие при построении отчетов, расчете себестоимости и проведении документов очень часто выполняет выборку больших диапазонов данных. В MS SQL Server для ускорения таких операций используется механизм упреждающего чтения — Read-Ahead. Если страницы индекса расположены на диске последовательно, СУБД считывает их крупными блоками за один физический запрос к диску. Если же индекс сильно фрагментирован, механизм Read-Ahead не может эффективно работать, СУБД совершает огромное количество мелких запросов ввода-вывода (IOPS), кэш процессора переполняется, и скорость выполнения запроса падает даже на самых быстрых NVMe SSD.
Кроме того, фрагментация приводит к снижению плотности заполнения страниц (Page Density). Из-за частых разбиений страниц (Page Splits) при добавлении данных страницы заполняются лишь частично (например, на 50%). В результате СУБД вынуждена тратить драгоценную оперативную память (буферный пул) на хранение полупустых страниц, неэффективно расходуя RAM.
Вывод: обслуживать индексы на SSD нужно, но делать это достаточно один раз в неделю (или даже в месяц) в период наименьшей нагрузки, сместив фокус на оптимизацию плотности заполнения страниц с помощью правильной настройки параметра FILLFACTOR.
Выясним истинную причину того, почему проведение документов ERP может занимать 5–7 секунд. В подавляющем большинстве случаев виновата не фрагментация индексов, а неактуальная статистика.
Оптимизатор запросов MS SQL Server строит план выполнения каждого запроса (решая, использовать ли индекс, какую таблицу сканировать первой и как их соединять) исключительно на основе статистических данных о распределении значений в таблицах. Если статистика устарела, оптимизатор может выбрать в корне неверный план: например, вместо быстрого поиска по индексу (Index Seek) запустить полное сканирование многомиллионной таблицы (Table Scan).
В базах данных 1С информация меняется лавинообразно. В течение дня в регистры сведений и накопления записываются сотни тысяч строк. Встроенный механизм автоматического обновления статистик MS SQL (AUTO_UPDATE_STATISTICS) часто срабатывает слишком поздно, так как по умолчанию требует изменения значительного процента строк таблицы (хотя в современных версиях SQL Server этот алгоритм стал более гибким).
Рекомендация: Вместо постоянного мучения индексов каждые 15 минут настройте частый пересчет статистик для наиболее активно изменяющихся таблиц (например, каждые 2–4 часа в течение рабочего дня) с помощью легких скриптов обновления или стандартных планов обслуживания. Для ключевых таблиц обновление желательно делать с параметром FULLSCAN.
Важнейший аспект, о котором часто забывают администраторы баз данных, касается изменений в архитектуре самой платформы 1С последних версий. Начиная с релизов платформы 8.3.22, 1С принудительно отключает блокировки страниц на уровне СУБД для целого ряда индексов (устанавливается параметр ALLOW_PAGE_LOCKS = OFF). Это сделано для минимизации взаимоблокировок (deadlocks) при параллельной работе множества пользователей.
Однако здесь кроется серьезная техническая проблема: стандартная команда реорганизации индекса в MS SQL Server физически не может быть выполнена, если для этого индекса запрещены страничные блокировки!
Попытка выполнить следующую команду на таком индексе завершится ошибкой СУБД:
ALTER INDEX [Имя_Индекса] ON [Имя_Таблицы] REORGANIZE;
Если ваш скрипт обслуживания (включая популярные готовые решения, такие как комплекс скриптов Олы Халленгрена без дополнительных настроек) пытается делать реорганизацию «вслепую», он либо будет постоянно сыпать ошибками в лог, либо просто пропустит эти индексы, оставив их без обслуживания.
Как решить эту проблему? Скрипт обслуживания должен уметь анализировать параметр ALLOW_PAGE_LOCKS. Если блокировки страниц отключены, для дефрагментации такого индекса необходимо применять только операцию перестроения (REBUILD), либо временно включать страничные блокировки перед реорганизацией и отключать их обратно по завершении процесса (как рекомендует сама фирма 1С в методологических материалах на ИТС). Для этого можно использовать доработанный скрипт MaintenanceSolution с коррекцией ALLOW_PAGE_LOCKS.
Проанализировав все факторы, мы можем сформулировать свод правил и настроек для создания идеального регламента обслуживания СУБД MS SQL под 1С:Предприятие: для комплексного подхода в такой регламент можно также включить автоматизацию архивирования, обновления и тестирования информационных баз 1С.
REORGANIZE);REBUILD).ONLINE = ON. Это позволит избежать блокирования таблиц и минимизирует влияние регламентных работ на пользователей, если их всё же приходится запускать вне технологического окна.FILLFACTOR на уровне 85–90%. Это оставит на страницах свободное место для новых записей, предотвратит частые разбиения страниц (Page Splits) и значительно снизит скорость роста фрагментации.UPDATE STATISTICS) для самых активных таблиц. Индексы в рабочее время не трогаем вообще!FULLSCAN.Перевод обслуживания вашей СУБД на эти рельсы позволит разгрузить дисковую подсистему и процессор в рабочее время, обеспечит оптимизатор MS SQL актуальными планами запросов и гарантирует стабильную, предсказуемую скорость проведения документов в 1С:ERP. Для пошаговой настройки регламентных работ подойдёт инструкция по настройке регламентных операций MS SQL Server под 1С.