При работе с крупными базами данных 1С на MS SQL Server администраторы часто сталкиваются с проблемой неэффективного выполнения регламентных заданий. Обычная ситуация: настроенный план обслуживания, включающий резервное копирование, реиндексацию и обновление статистики, запускается ночью, но вместо последовательного выполнения операций наблюдаются многочасовые паузы между шагами. Это приводит к тому, что ресурсоемкие задачи, такие как обновление статистики, переносятся на рабочее время, значительно снижая производительность для пользователей, например, бухгалтеров, работающих в 1С:Бухгалтерия или 1С:Зарплата и Управление Персоналом.
Рассмотрим, почему возникают такие задержки и как можно эффективно оптимизировать выполнение регламентных заданий MS SQL Server, чтобы они работали последовательно и не мешали основной работе.
Прежде чем перейти к решениям, выясним ключевые факторы, которые могут вызывать описанные проблемы:
Для решения проблемы с длительными паузами и неэффективным выполнением регламентных заданий нам потребуется комплексный подход, включающий как обновления программного обеспечения, так и тонкую настройку SQL Server.
Один из критически важных шагов, упомянутых в обсуждении, — это установка последних накопительных обновлений (CU) для вашего MS SQL Server 2019. Мы обнаружили, что некоторые проблемы с производительностью, в частности, неоправданно долгое выполнение запроса перед обновлением статистики, были исправлены именно в накопительных обновлениях. Например, для SQL Server 2019 упоминалось исправление в CU13, а также актуальный на данный момент CU15 (KB5008996).
Мы настоятельно рекомендуем всегда использовать последние доступные накопительные обновления, поскольку они содержат не только исправления ошибок, но и улучшения производительности, а также включают все предыдущие исправления. После установки CU желательно пересоздать планы обслуживания, чтобы они могли использовать обновленную функциональность и оптимизацию. Иногда простое пересохранение плана после обновления может не дать полного эффекта, и создание нового плана с нуля может быть более действенным.
Для решения проблем, связанных с задержками при обновлении статистики, мы можем включить опцию автоматического асинхронного обновления статистики в MS SQL Server. Эта настройка позволяет SQL Server обновлять статистику в фоновом режиме, не блокируя выполнение запросов, которые используют устаревшую статистику.
Разберем, как это работает и как настроить:
По умолчанию, если SQL Server обнаруживает устаревшую статистику во время выполнения запроса, он синхронно обновляет ее, что блокирует выполнение запроса на время обновления. При асинхронном обновлении, если запрос обращается к таблице с устаревшей статистикой, SQL Server позволяет ему использовать текущую (хоть и устаревшую) статистику, а в фоновом режиме запускает обновление статистики. После завершения обновления, будущие запросы начнут использовать новую статистику.
Главное преимущество — уменьшение блокировок и снижение влияния обновления статистики на производительность в рабочее время. Пользователи не будут ждать завершения обновления статистики.
Запросы могут временно использовать неоптимальные планы выполнения, пока статистика не обновится в фоновом режиме.
Эту настройку можно включить для каждой базы данных отдельно с помощью команды ALTER DATABASE:
ALTER DATABASE [ИмяВашейБазыДанных] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT;
Замените [ИмяВашейБазыДанных] на актуальное название вашей базы данных 1С.
Стандартные планы обслуживания SQL Server, хоть и удобны, часто имеют ограниченную гибкость. Для более тонкой настройки и эффективного выполнения задач обслуживания (особенно в среде 1С с ее динамичной структурой данных), многие специалисты переходят на использование кастомных скриптов. Одним из наиболее популярных и хорошо зарекомендовавших себя решений является Ola Hallengren's Maintenance Solution.
Мы проанализируем основные принципы, которые применяются в таких скриптах:
Скрипты позволяют выполнять перестроение (REBUILD) или реорганизацию (REORGANIZE) индексов выборочно, в зависимости от уровня их фрагментации:
REBUILD). Перестроение индекса полностью удаляет старую структуру и создает новую, плотно упакованную, что значительно улучшает производительность. Важным бонусом является то, что перестроение индекса автоматически обновляет статистику для данного индекса.REORGANIZE). Реорганизация менее ресурсоемка, чем перестроение, и также помогает уменьшить фрагментацию.Такой подход значительно сокращает общее время обслуживания, так как мы не тратим ресурсы на индексы, которые в этом не нуждаются.
Обновление статистики часто дает больший эффект для производительности запросов, чем просто перестроение индексов, и выполняется быстрее, создавая меньше блокировок. Мы рекомендуем выполнять UPDATE STATISTICS WITH FULLSCAN для наиболее важных таблиц, чтобы получить самую актуальную статистику. Это обеспечивает максимальную точность для оптимизатора запросов.
USE [ИмяВашейБазыДанных];
GO
UPDATE STATISTICS [ИмяТаблицы] WITH FULLSCAN;
GO
Оптимальная частота обновления статистики зависит от характера нагрузки на вашу базу данных 1С. Для активно изменяющихся таблиц может потребоваться более частое обновление.
Для того чтобы точно определить, не являются ли регламентные задания 1С причиной длительных пауз или общей нагрузки, мы рекомендуем провести диагностический эксперимент: запустить обслуживание SQL Server с полностью остановленным сервером 1С. Если при этом регламенты SQL выполняются последовательно и без задержек, это будет явным указанием на то, что фоновые процессы 1С или конкуренция за ресурсы между сервером 1С и SQL Server вызывают проблему.
В этом случае необходимо будет:
Помимо обновлений и скриптов, мы рассмотрим несколько важных настроек SQL Server, которые значительно влияют на производительность, особенно в среде 1С:
Max Degree of Parallelism (MAXDOP):
Этот параметр определяет максимальное количество потоков, которые может использовать один запрос для параллельного выполнения. Для большинства систем с нагрузкой, характерной для 1С, часто рекомендуется устанавливать MAXDOP в значение 1. Это помогает предотвратить взаимные блокировки и снизить нагрузку на CPU от высокопараллельных запросов, которые могут быть неэффективными для OLTP-нагрузки 1С. Мы можем установить его через SQL Server Management Studio (SSMS) или с помощью T-SQL:
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
Max Server Memory:
Критически важно корректно настроить максимальный объем памяти, выделяемый SQL Server. Если SQL Server и сервер 1С находятся на одном физическом сервере, мы должны ограничить память SQL Server так, чтобы он не конкурировал за память с сервером 1С (rphost процессы) или другими приложениями. Обычно оставляют 2-4 ГБ для операционной системы и других служб, а остальное делят между SQL Server и сервером 1С.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'max server memory (MB)', [желаемое_значение_в_МБ];
RECONFIGURE WITH OVERRIDE;
Мгновенная инициализация файлов (Instant File Initialization - IFI):
Включение этой опции может значительно ускорить операции с файлами данных, такие как создание новых баз данных, увеличение размеров существующих файлов данных или восстановление баз данных из резервных копий. IFI позволяет SQL Server не обнулять дисковое пространство при создании или расширении файлов, что экономит время. Для активации IFI необходимо предоставить учетной записи службы SQL Server разрешение Perform Volume Maintenance Tasks в групповых политиках безопасности Windows Server.
Для SQL Server 2019 и выше, а также для Enterprise Edition, доступны дополнительные функции, которые могут значительно упростить и ускорить обслуживание больших баз данных:
Возобновляемые операции с индексами (Resumable Index Operations):
В SQL Server 2019 появилась возможность приостанавливать и возобновлять перестроение индексов (ALTER INDEX ... REBUILD WITH (RESUMABLE = ON)). Это чрезвычайно удобно для очень больших баз данных, когда окно обслуживания ограничено. Мы можем запустить перестроение индекса, приостановить его, когда начнется пиковая нагрузка, и возобновить позже, без потери уже проделанной работы. Это позволяет укладываться в короткие ночные или выходные окна обслуживания.
Онлайн-операции с индексами (Online Operations):
В Enterprise Edition SQL Server доступны онлайн-операции перестроения индексов (ALTER INDEX ... REBUILD WITH (ONLINE = ON)). При их использовании запросы к таблице продолжают работать во время перестроения индекса, что минимизирует время простоя для пользователей. Это позволяет выполнять обслуживание даже в рабочее время с минимальным влиянием.
Постоянный мониторинг производительности является ключом к успешной оптимизации. Мы используем инструменты мониторинга, такие как:
Query Store:
В SQL Server 2016 и новее Query Store (Хранилище запросов) позволяет нам отслеживать историю планов выполнения запросов, их статистику использования ресурсов (ЦП, ввод/вывод, память) и выявлять регрессии производительности. Это бесценный инструмент для анализа самых медленных запросов и причин их замедления.
Системные счетчики производительности (Perfmon):
Perfmon в Windows Server позволяет отслеживать различные метрики SQL Server и операционной системы в реальном времени: загрузку ЦП, использование дисков (IOPS, задержка), объем свободной памяти, количество блокировок и ожидания в SQL Server. Эти данные помогают выявлять узкие места в системе.
DMV (Dynamic Management Views):
Динамические административные представления SQL Server предоставляют подробную информацию о текущем состоянии сервера, выполняющихся запросах, блокировках, ожиданиях и использовании ресурсов. Они позволяют проводить глубокий анализ производительности.
В некоторых случаях стандартный план обслуживания, включающий все шаги, может быть неоптимален. Мы можем рассмотреть следующие подходы:
Создание отдельных планов или заданий для каждого шага:
Вместо одного большого плана обслуживания мы можем создать отдельные задания SQL Server Agent для бэкапа, реорганизации индексов, обновления статистики и очистки. Каждое задание может иметь свое расписание, но важно настроить их последовательность выполнения с помощью шагов задания или зависимостей. Это дает больше контроля над временем выполнения каждого типа операции.
Использование батников или PowerShell скриптов:
Как упоминалось в обсуждении, мы можем использовать батники или PowerShell скрипты, которые явно вызывают каждую команду или хранимую процедуру SQL Server для обслуживания. Это обеспечивает максимальную гибкость в логике выполнения и обработки ошибок, а также позволяет интегрировать дополнительные проверки или действия.
Необходимо постоянно помнить, что базы данных 1С, особенно крупные и активно используемые (как 1С:Зарплата и Управление Персоналом 3.1 или 1С:Бухгалтерия 3.0), являются одними из самых требовательных к SQL Server. Они часто приводят к:
Поэтому крайне важно не только оптимизировать сам SQL Server, но и координировать его обслуживание с регламентными заданиями 1С. Мы должны стремиться проводить самые ресурсоемкие операции SQL Server (полное перестроение индексов, обновление статистики с FULLSCAN) во время минимальной активности пользователей и сервера 1С.
Проблема длительных пауз между шагами регламентных заданий MS SQL Server для баз 1С — это сложная, но решаемая задача. Применяя комплексный подход, включающий установку последних накопительных обновлений SQL Server, переход на кастомные скрипты для более гибкого обслуживания, активацию асинхронного обновления статистики и тщательную настройку параметров SQL Server, мы можем значительно улучшить производительность, сократить время простоя и обеспечить стабильную работу бухгалтерских и других систем 1С.
Помним, что каждый шаг по оптимизации должен сопровождаться мониторингом и анализом, чтобы убедиться в положительном эффекте и, при необходимости, скорректировать наши действия. Мы всегда стремимся к тому, чтобы наши системы работали максимально эффективно, а пользователи не сталкивались с задержками в самый неподходящий момент.