Приветствуем вас! Вопросы администрирования СУБД MS SQL Server для систем на платформе «1С:Предприятие» всегда вызывают множество споров. Особенно часто дискуссии разворачиваются вокруг регламентных процедур: как часто нужно обновлять статистику, стоит ли полностью доверяться автоматическим механизмам СУБД, и почему стандартные операции обслуживания иногда ведут себя вопреки здравой логике — например, когда полное обновление статистики длится дольше, чем полное перестроение (ребилд) индексов.
В этой статье мы подробно разберем внутренние механизмы работы MS SQL Server в связке с высоконагруженными базами данных 1С (такими как УТ, УПП, ERP). Мы ответим на самые острые вопросы администрирования, проанализируем неочевидные нюансы автообновления и предложим проверенные на практике конфигурации регламентных заданий.
Начнем с базовых вещей. Как известно, начиная с версии SQL Server 2016 (уровень совместимости базы данных 130 и выше), а также в более старых версиях при включенном флаге трассировки 2371, алгоритм автоматического обновления статистики существенно изменился. Вместо устаревшего статического порога в 20% измененных строк + 500 записей СУБД перешла на динамический алгоритм. Теперь количество изменений, необходимое для запуска автоматического пересчета, рассчитывается по формуле:
Порог срабатывания = Квадратный корень из (1000 * Количество строк в таблице)
Для наглядности сравним старый статический порог автообновления статистик (20% + 500 строк) и новый динамический алгоритм:
Казалось бы, отличная новость! Однако даже 100 000 изменений в таблице на 10 млн строк — это огромный массив данных. В базе ERP крупного торгового или производственного предприятия за один день в ключевых регистрах может измениться 50 000 - 80 000 строк. Для запуска автообновления этого недостаточно, но для полной деградации планов выполнения запросов — более чем.
Основная причина кроется в специфике работы платформы 1С и так называемой проблеме возрастающего ключа (Ascending Key Problem). Базы данных 1С характеризуются колоссальной интенсивностью записи новых данных в хронологическом порядке. Каждый день пользователи вводят сотни и тысячи новых документов, создают записи в регистрах сведений и накопления. Даты этих документов и их последовательные идентификаторы всегда больше, чем любые значения, зафиксированные в гистограмме статистики во время ее последнего расчета.
Давайте разберем по шагам, к чему это приводит на практике:
Nested Loops вместо быстрого хэш-соединения Hash Match или решает выполнить полное сканирование таблицы Table Scan вместо точечного поиска по индексу. Как результат — система начинает критически тормозить.Именно поэтому для систем 1С критически важно выполнять регулярное принудительное обновление статистики.
Этот парадокс часто ставит в тупик начинающих администраторов СУБД. При анализе времени выполнения ночного обслуживания выясняется, что полное перестроение индексов (ALTER INDEX REBUILD) завершается быстрее, чем простая процедура обновления статистик (UPDATE STATISTICS). Выясним причину этого явления.
Когда вы выполняете ребилд индекса, SQL Server физически пересоздает структуру индекса «с нуля». СУБД обязана прочитать все 100% страниц данных таблицы, отсортировать их и записать заново. Поскольку сервер в любом случае выполняет полное чтение данных таблицы, он попутно обновляет статистику, привязанную к этому индексу, причем делает это с параметром 100%-го сканирования (FULLSCAN). Это линейное, последовательное чтение больших блоков данных, которое отлично распараллеливается движком базы данных.
Процедура же обновления статистики всей базы данных работает совершенно иначе по двум основным причинам:
AUTO_CREATE_STATISTICS. Сервер создает их в тот момент, когда в запросах (в секциях WHERE, JOIN или GROUP BY) используются колонки, не входящие в существующие индексы. Такие статистики легко узнать по системным префиксам в именах, начинающимся с _WA_Sys_....sp_updatestats), SQL Server вынужден производить сканирование таблиц для каждого объекта статистики отдельно. Если у вас на таблице висит 5 индексов и 20 автоматически созданных статистик колонок, сервер может перечитывать одну и ту же таблицу 25 раз! Это порождает гигантскую дисковую нагрузку (I/O) и растягивает выполнение операции на часы.Таким образом, ребилд индексов обновляет только статистику самих индексов за один проход, игнорируя статистики колонок, в то время как полное обновление статистик пытается пересчитать абсолютно все объекты, многократно сканируя таблицы.
Проанализируем, почему включение опции асинхронного обновления статистик является жизненно важным решением для баз данных 1С.
По умолчанию в MS SQL Server включено синхронное обновление статистик. Это работает так: когда пользователь проводит документ или формирует отчет, SQL Server понимает, что порог изменений для таблицы превышен, и статистика устарела. Сервер приостанавливает выполнение пользовательского запроса и начинает пересчитывать статистику. В этот момент пользователь видит «песочные часы».
Если таблица большая, пересчет статистики может занять длительное время. В условиях многопользовательской работы 1С это гарантированно приводит к лавинообразному росту блокировок (Lock Timeout). Все остальные пользователи, пытающиеся записать данные в эту же таблицу, выстраиваются в очередь, транзакции отваливаются по таймауту, работа компании парализуется.
Включение опции асинхронного обновления статистик кардинально меняет поведение СУБД:
AUTO_UPDATE_STATISTICS_ASYNC ON
Теперь, когда порог изменений превышен, SQL Server не блокирует запрос пользователя. Запрос мгновенно выполняется по старому (пусть и не идеальному) плану запроса. Одновременно с этим СУБД в фоновом режиме запускает отдельный поток, который тихо и незаметно для пользователей обновляет статистику. Как только фоновый процесс завершается, новые запросы начинают использовать уже обновленные данные.
Для баз данных 1С опции AUTO_UPDATE_STATISTICS и AUTO_UPDATE_STATISTICS_ASYNC всегда должны находиться в состоянии ON. Это защитит ваших пользователей от внезапных зависаний системы посреди рабочего дня.
Вы обновили статистику ночью, но пользователи утром все равно жалуются на медленную работу конкретного отчета? Разберем причину этой проблемы.
SQL Server кэширует однажды скомпилированные планы выполнения запросов в специальной области памяти — процедурном кэше (Procedural Cache). Это сделано для экономии ресурсов процессора на постоянную компиляцию схожих запросов. К сожалению, даже после того, как вы успешно обновили статистику, оптимизатор может продолжать использовать старый, неоптимальный план запроса из кэша, поскольку он считает его все еще актуальным.
Чтобы новые актуальные данные статистики сразу же вступили в силу, процедурный кэш необходимо сбрасывать. В старых версиях SQL Server для этого применялась жесткая команда:
DBCC FREEPROCCACHE;
Однако выполнение этой команды на рабочей системе вызывает резкий скачок нагрузки на процессор (CPU), так как абсолютно все запросы ко всем базам данных сервера начинают компилироваться заново. Начиная с SQL Server 2016, мы имеем возможность выполнять точечную, безопасную очистку кэша планов применительно только к конкретной базе данных 1С. Для этого после завершения ночного обслуживания выполните следующую команду:
USE [Имя_Вашей_Базы_1С];
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
Это заставит оптимизатор перестроить планы запросов только для вашей базы 1С при их первом последующем обращении, опираясь на свежесобранную ночную статистику.
Стандартные планы обслуживания (Maintenance Plans) в MS SQL Server слишком примитивны. Они действуют «в лоб», перебирая все таблицы подряд, что приводит к нерациональному расходу ресурсов.
Стандартом де-факто в мире администрирования баз данных являются бесплатные скрипты Олы Халленгрена. Давайте разберем по шагам, как правильно настроить их использование в среде 1С для оптимизации времени обслуживания:
@OnlyModifiedStatistics = 'Y'. При его включении система будет обновлять статистику только для тех таблиц, в которых реально происходили изменения данных с момента последнего сбора. Это сбережет огромное количество времени, так как архивные периоды прошлых лет не будут перечитываться каждую ночь.FULLSCAN. А для статистики колонок (те самые _WA_Sys_...) настройте сбор методом случайной выборки (sampling). Для этого используйте параметры скрипта @UpdateStatistics = 'ALL' в сочетании с ограничением процента сканирования через @SamplePercent.Рассмотрим пример настройки шага задания агента SQL Server (SQL Server Agent Job) для умного обслуживания индексов и статистик:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationThresholdLow = 5,
@FragmentationThresholdMedium = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100,
@LogToTable = 'Y';
Давайте проанализируем параметры, которые мы указали в данном вызове:
@Databases = 'USER_DATABASES' — скрипт будет обрабатывать все пользовательские базы данных на сервере (все ваши базы 1С).@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE' — для Enterprise-редакций SQL Server скрипт попытается перестроить индексы в режиме онлайн (не блокируя работу пользователей), а если это невозможно, он выполнит перестроение в оффлайн-режиме.@UpdateStatistics = 'ALL' — скрипт обновит как статистики индексов, так и статистики одиночных колонок.@OnlyModifiedStatistics = 'Y' — статистика будет обновляться только по тем таблицам, данные в которых действительно поменялись с момента последнего сбора.@StatisticsSample = 100 — указывает СУБД собирать статистику со 100%-м сканированием таблицы (эквивалент FULLSCAN) для измененных таблиц.Давайте рассмотрим практические инструменты, которые помогут нам проанализировать текущее состояние нашей базы данных 1С.
Вы можете написать и выполнить T-SQL запрос, который выведет список всех статистик в базе данных, дату их последнего обновления, количество измененных строк с момента последнего обновления и общее количество строк в таблице. Это позволит вам наглядно увидеть, какие таблицы «выпали» из автоматического обслуживания:
SELECT
obj.name AS [Имя Таблицы],
stat.name AS [Имя Статистики],
STATS_DATE(stat.object_id, stat.stats_id) AS [Дата Последнего Обновления],
sp.rows AS [Всего Строк в Таблице],
sp.modification_counter AS [Измененных Строк с Последнего Обновления],
CAST(100.0 * sp.modification_counter / sp.rows AS DECIMAL(18, 2)) AS [% Изменений]
FROM sys.stats AS stat
INNER JOIN sys.objects AS obj ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE obj.type = 'U'
AND sp.rows > 1000
ORDER BY [Дата Последнего Обновления] ASC;
Запустите этот скрипт в среде SQL Server Management Studio (SSMS) для вашей базы данных 1С. Если вы увидите в колонке Дата Последнего Обновления даты недельной или месячной давности для активно изменяющихся таблиц регистров (имена которых начинаются с _AccumReg или _Document), это верный сигнал к тому, что настройки обслуживания вашей СУБД требуют немедленного вмешательства.
Чтобы понять, в каком состоянии находятся индексы вашей базы данных 1С прямо сейчас, воспользуемся следующим T-SQL скриптом для анализа фрагментации:
SELECT
OBJECT_NAME(ips.object_id) AS [Имя Таблицы],
i.name AS [Имя Индекса],
ips.index_type_desc AS [Тип Индекса],
ips.avg_fragmentation_in_percent AS [% Фрагментации],
ips.page_count AS [Количество Страниц]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ips
INNER JOIN sys.indexes AS i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
AND ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;
Если в вашей базе 1С множество индексов имеют фрагментацию более 30%, это существенно снижает скорость чтения данных с диска, что также требует регулярного перестроения индексов (ребилда) во время ночного обслуживания.
Проанализируем еще один неочевидный аспект, влияющий на скорость выполнения регламентных процедур. Важнейшим системным ресурсом, который активно задействуется при ребилде индексов и расчете статистик, является системная база данных tempdb.
При перестроении индексов SQL Server выполняет сортировку огромных массивов данных. Если у вас включена опция SORT_IN_TEMPDB = ON в параметрах ребилда, вся промежуточная сортировка будет производиться в базе данных tempdb, разгружая оперативную память и файлы данных основной базы. Если дисковая подсистема, на которой расположена tempdb, медленная, или если файлы базы настроены некорректно, любая регламентная операция будет длиться бесконечно долго.
Рекомендации по оптимизации tempdb для ускорения обслуживания баз 1С:
tempdb должна располагаться на самых быстрых доступных накопителях (лучше всего NVMe SSD).tempdb должно соответствовать количеству логических ядер процессора (но не более 8 файлов, если у вас многоядерная система, во избежание накладных расходов на распределение страниц). Все файлы данных должны быть абсолютно одинакового размера с фиксированным шагом авторасширения (например, 512 МБ или 1024 МБ).Если ваша информационная система развернута на современных версиях MS SQL Server (2019 или 2022), вам доступны встроенные интеллектуальные механизмы обработки запросов (Intelligent Query Processing — IQP). Они существенно снижают пагубное влияние неточной статистики на производительность 1С.
Проанализируем две ключевые технологии, которые облегчают жизнь администраторам 1С:
Nested Loops. Если в реальности туда приходило 100 000 строк, запрос зависал. Теперь же, благодаря адаптивным соединениям, SQL Server умеет переключать тип соединения на Hash Join на лету, прямо во время выполнения запроса, если видит, что фактический объем данных превысил определенный порог.Подводя итог нашего исследования, мы можем однозначно утверждать: принудительное регулярное обновление статистик в базах данных 1С на MS SQL Server жизненно необходимо. Автоматика СУБД, даже в современных версиях, не способна в полной мере справиться со спецификой интенсивной лавинообразной записи хронологических данных 1С и проблемой возрастающего ключа. Настраивайте регламентные операции с умом, и ваша система отблагодарит вас стабильной и быстрой работой!