При работе с крупными базами данных 1С 7.7 (объемом 500 Гбайт и более) на платформе MS SQL Server администраторы и разработчики часто сталкиваются с внезапным падением производительности системы. Типичная ситуация: отчеты, которые обычно выполнялись за пару минут, начинают формироваться часами. Особенно остро эта проблема проявляется в начале месяца, когда пользователи открывают новый период. Как правило, перезапуск сервера не помогает, а на полное обслуживание базы и реиндексацию просто нет времени из-за жестких рамок технологического окна.
Выясним причину этого поведения и подробно разберем, как правильно организовать обновление статистик и индексов на больших терабайтных базах 1С 7.7, чтобы укладываться в минимальные технологические паузы.
Рассмотрим специфику работы платформы 1С 7.7. При сдвиге или открытии периода происходит массовый пересчет итогов и запись новых движений. В СУБД MS SQL Server за эти операции отвечают таблицы остатков (их имена начинаются с префикса RG) и таблицы движений регистров (с префиксом RA). В этот момент миллионы записей перемещаются, группируются и обновляются. Плотность распределения данных в таблицах кардинально меняется.
Оптимизатор MS SQL Server строит планы выполнения запросов на основе статистических данных. Если статистика устарела, оптимизатор «думает», что в таблице содержится одна строка (или наоборот, слишком много строк), и выбирает неоптимальный план выполнения (например, сканирование всей таблицы Index Scan вместо быстрого поиска по индексу Index Seek). В результате отчеты начинают работать в разы медленнее.
Для регулярного обслуживания индексов и статистик на больших базах не стоит использовать стандартные планы обслуживания (Maintenance Plans) SSMS, так как они работают негибко и создают колоссальную нагрузку. Рекомендуется внедрить популярный и проверенный временем комплекс скриптов от Олы Халленгрена (Ola Hallengren).
Проанализируем преимущества этого подхода:
При использовании MS SQL Server 2008 будьте осторожны с параметром @OnlineIndexRebuild (онлайн-перестроение индексов). На этой версии СУБД данная функция имеет технологические ограничения и может приводить к блокировкам в 1С 7.7, поэтому детально тестируйте ее применение или отдавайте предпочтение дефрагментации (Reorganize) в рабочее время.
По умолчанию в MS SQL Server автоматическое обновление статистики срабатывает только тогда, когда в таблице изменяется более 20% строк + 500 строк. Для таблицы объемом в 100 000 записей это приемлемо. Но если в таблице базы 1С 7.7 (например, в таблице проводок или движений регистров) содержится 50 000 000 строк, то для автоматического обновления статистики СУБД будет ждать изменения 10 000 500 строк! За это время статистика безнадежно устареет, а система начнет зависать.
Чтобы решить эту проблему, включим флаг трассировки Trace Flag 2371. Этот флаг меняет алгоритм автообновления статистик на динамический: чем больше таблица, тем меньший процент измененных строк требуется для запуска обновления.
Разберем по шагам, как активировать этот флаг глобально для всего сервера SQL:
-T2371.Если перезагрузка сервера сейчас невозможна, мы можем включить флаг динамически «на лету» с помощью T-SQL команды:
DBCC TRACEON (2371, -1);
-- Проверим статус выполнения команды
DBCC TRACESTATUS;
Важное примечание: Если в дальнейшем планируется перенос базы данных 1С 7.7 на современные версии MS SQL Server (2016 и выше), включать флаг 2371 вручную больше не требуется. Начиная с уровня совместимости базы данных (Compatibility Level) 130 этот динамический алгоритм уже встроен в ядро СУБД по умолчанию.
По умолчанию в MS SQL Server включено синхронное обновление статистик. Это означает следующее: когда пользователь запускает отчет, СУБД понимает, что статистика устарела, блокирует выполнение отчета и начинает пересчитывать статистику. На базе объемом 500 ГБ этот процесс может занять до получаса. Пользователь видит «песочные часы», а вся система начинает испытывать проблемы из-за транзакционных блокировок.
Посмотрим на пример включения асинхронного обновления статистик:
ALTER DATABASE [Имя_Вашей_Базы] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
При такой настройке первый пользовательский запрос выполнится мгновенно по старому (пусть и не самому идеальному) плану, но параллельно в фоновом режиме СУБД запустит поток обновления статистики. Следующие запросы уже получат новый, оптимизированный план выполнения без ожидания и зависаний.
Если полное обновление статистик всей базы объемом 500 ГБ занимает слишком много времени, настроим регламентное задание на точечное обновление наиболее критичных таблиц 1С 7.7 сразу после открытия нового периода.
Напишем скрипт, который находит все таблицы остатков RG* и движений RA* и обновляет статистику по ним с высокой точностью (FULLSCAN). При этом мы задействуем многопоточность через параметр MAXDOP, чтобы ускорить процесс сканирования за счет использования нескольких ядер процессора:
-- Объявляем переменные для курсора
DECLARE @TableName NVARCHAR(256);
DECLARE @SQLCommand NVARCHAR(MAX);
-- Выбираем только таблицы регистров остатков и движений
DECLARE TableCursor CURSOR FOR
SELECT name FROM sys.tables
WHERE name LIKE 'RG%' OR name LIKE 'RA%';
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Формируем команду обновления с полным сканированием и ограничением параллелизма (например, 4 ядра)
SET @SQLCommand = 'UPDATE STATISTICS ' + QUOTENAME(@TableName) + ' WITH FULLSCAN, MAXDOP = 4;';
-- Выполняем команду
EXEC sp_executesql @SQLCommand;
FETCH NEXT FROM TableCursor INTO @TableName;
END;
CLOSE TableCursor;
DEALLOCATE TableCursor;
Запуск такого скрипта по расписанию (например, каждые 6 часов или сразу после регламентного сдвига периода) занимает всего 10–15 минут даже на полутерабайтной базе, гарантируя идеальную скорость формирования ключевых отчетов.
Иногда возникает ситуация: статистика обновлена, но отчеты все равно продолжают медленно формироваться. Это связано с тем, что в оперативной памяти сервера хранится старый кэш планов запросов (Procedure Cache). СУБД продолжает использовать ранее скомпилированный неоптимальный план, игнорируя новые статистические данные.
Чтобы заставить SQL Server перестроить планы выполнения с учетом свежих статистик, необходимо сбросить кэш планов. Рассмотрим два варианта:
Вариант 1 (жесткий): Полная очистка кэша планов на сервере. Подходит для старых версий MS SQL Server (2000/2005/2008). Будьте аккуратны, в момент запуска может возникнуть кратковременный рост нагрузки на CPU, так как все планы для всех баз будут компилироваться заново.
DBCC FREEPROCCACHE;
Вариант 2 (мягкий): На более современных версиях SQL Server (начиная с 2016) можно очистить кэш планов точечно для конкретной базы данных 1С:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Для стабильной работы большой базы 1С 7.7 на MS SQL Server необходимо отказаться от стандартных подходов обслуживания «в лоб». Эффективный регламент администрирования должен включать:
TF 2371 для автоматического расчета статистик на таблицах-гигантах.AUTO_UPDATE_STATISTICS_ASYNC ON) для предотвращения зависания сессий пользователей.FULLSCAN для критически важных таблиц остатков и движений (RG*, RA*, 1SJOURN) в периоды закрытия и открытия месяцев.