Как снизить нагрузку на MS SQL Server и кластер 1С при выполнении тяжелых отчетов?

При разработке и вызове сложных аналитических отчетов на рабочей информационной базе разработчики часто сталкиваются с ситуацией, когда ресурсоемкий запрос замедляет работу всех остальных пользователей системы. Выполнение сложных выборок приводит к высокой загрузке процессора, забиванию оперативной памяти и задержкам на уровне СУБД. Проанализируем, почему возникает эта проблема и какими инструментами можно ограничить влияние тяжелых запросов на пользовательские сеансы.

Ограничение степени параллелизма на уровне MS SQL Server (MAXDOP)

Одной из главных причин замедления работы пользователей при выполнении тяжелого запроса является использование СУБД всех доступных ядер процессора для распараллеливания операции. По умолчанию параметр MAXDOP (Max Degree of Parallelism) в MS SQL Server может быть установлен в 0, что разрешает СУБД задействовать абсолютно все логические ядра для одного запроса.

Рассмотрим, как ограничить этот параметр по шагам:

1. Откроем SQL Server Management Studio (SSMS) и подключимся к нашему экземпляру СУБД.
2. Нажмем правой кнопкой мыши на имя сервера и перейдем в Свойства (Properties).
3. Перейдем на вкладку Дополнительно (Advanced).
4. Найдем параметр Максимальная степень параллелизма (Max Degree of Parallelism).
5. Изменим значение с 0 на ограниченное число (например, 2 или значение, равное общему количеству ядер, разделенному на 4).

Также рекомендуется скорректировать параметр Cost Threshold for Parallelism (порог стоимости для параллелизма). По умолчанию он равен 5. Повышение этого значения до 20–50 запретит серверу распараллеливать простые и средне-сложные запросы, сохраняя ресурсы для основных пользователей.

Изоляция нагрузки в кластере серверов 1С

Выполнение огромной выборки нагружает не только SQL Server, но и рабочий процесс платформы 1С — rphost. Если на одном процессе rphost находятся сеансы оперативных пользователей и одновременно выполняется тяжелый аналитический отчет, этот процесс раздувается по памяти и вызывает подтормаживание у всех активных сессий.

Для предотвращения этой проблемы разберем варианты настройки кластера серверов 1С:

1. Разделение рабочих процессов: В свойствах кластера серверов установим ограничение на количество соединений на один рабочий процесс. В этом случае система сформирует несколько процессов rphost. Запрос отчета займет лишь один из них, а остальные пользователи продолжат работу без задержек.
2. Настройка ТНФ (Технологическая настройка функциональности): С помощью механизмов назначения требований функционала вынесем выполнение тяжелых отчетов или фоновых задач на отдельный рабочий сервер в кластере 1С.
3. Ограничение расхода памяти: Зададим параметры «Предельный объем выделенной памяти» и «Превышение объема памяти» в настройках кластера, чтобы предотвратить ситуативную нехватку оперативной памяти.

Оптимизация структуры запроса и работы с временными таблицами

Часто причиной повышенной нагрузки является неоптимальный код самого запроса 1С. Выясним, какие правила написания кода позволяют минимизировать нагрузку на системные ресурсы:

1. Очистка временных таблиц: Если в пакете запросов используется несколько временных таблиц, необходимо явно удалять их сразу после использования с помощью инструкции УДАЛИТЬ. Это освобождает ресурсы базы данных TempDB.
2. Индексация временных таблиц: Все временные таблицы, участвующие в последующих соединениях или условиях сгруппированной выборки, должны быть проиндексированы по ключевым полям с помощью конструкции ИНДЕКСИРОВАТЬ ПО.... Это избавляет MS SQL Server от сканирования всей таблицы (Table Scan).
3. Избегание считывания ненужных полей: Для формирования печатных форм и таблиц отчета следует выводить представления ссылок, а не тащить тяжелые объекты целиком.

Посмотрим на пример правильного оформления пакета запросов с очисткой и индексацией временной таблицы:

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	Продажи.Номенклатура КАК Номенклатура,
    |	СУММА(Продажи.Количество) КАК Количество
    |ПОМЕСТИТЬ ВТ_Продажи
    |ИЗ
    |	РегистрНакопления.Продажи КАК Продажи
    |СГРУППИРОВАТЬ ПО
    |	Продажи.Номенклатура
    |ИНДЕКСИРОВАТЬ ПО
    |	Номенклатура;
    |////////////////////////////////////////////////////////////////////////////////
    |ВЫБРАТЬ
    |	ВТ_Продажи.Номенклатура,
    |	ВТ_Продажи.Количество
    |ИЗ
    |	ВТ_Продажи КАК ВТ_Продажи;
    |////////////////////////////////////////////////////////////////////////////////
    |УДАЛИТЬ ВТ_Продажи";

Использование фоновых заданий и репликации данных

Проанализируем архитектурные подходы, которые позволяют полностью изолировать ресурсоемкие аналитические операции от оперативной работы пользователей:

1. Запуск в фоновом режиме: Выполнение отчета следует выносить в объект ФоновыеЗадания. Это позволяет не блокировать пользовательский интерфейс и передать управление кластеру 1С для асинхронного расчета.
2. Настройка базы для отчетов (Read-Only Replica): При высокой аналитической нагрузке на предприятии настраивают репликацию базы данных с помощью технологии Always On Availability Groups в MS SQL Server. Все аналитические отчеты перенаправляются на вторичную реплику с параметром подключения ApplicationIntent=ReadOnly, что полностью снимает нагрузку с основной рабочей базы.