Разработчики крупных информационных систем на платформе 1С часто сталкиваются с крайне загадочным явлением. Один и тот же запрос, обрабатывающий неизменный объем данных во временных таблицах, в 95% случаев «летает» за несколько секунд, но периодически намертво зависает на час, два или даже на весь рабочий день. При этом перезапуск фонового задания или очистка кэша мгновенно возвращает скорость к эталонным секундам.
В этой статье мы подробно проанализируем эту ситуацию на реальном примере, выясним скрытые технические причины такого поведения СУБД, разберем влияние настроек сервера MS SQL Server и рассмотрим пошаговые методы оптимизации, которые позволят навсегда стабилизировать скорость работы системы.
Представим сценарий: в фоновом задании выполняется запрос, который соединяет несколько временных таблиц. Объемы данных в этих таблицах стабильны и выглядят следующим образом:
Сам запрос в исходном виде решает задачу фильтрации и сопоставления иерархических структур данных (так называемой родословной). Давайте посмотрим на исходный код этого запроса:
Выбрать
втОтбор.Номенклатура,
втОтбор.Размещение,
втОтбор.СтруктурнаяЕдиница
Из
вт_ТаблицаОтбораЛокальная как втОтбор
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Номенклатура КАК вт_Родословная_Номенклатура
ПО вт_Родословная_Номенклатура.ссылка = втОтбор.Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Размещение КАК вт_Родословная_Размещение
ПО вт_Родословная_Размещение.ссылка = втОтбор.Размещение
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_СтруктурнаяЕдиница КАК вт_Родословная_СтруктурнаяЕдиница
ПО вт_Родословная_СтруктурнаяЕдиница.ссылка = втОтбор.СтруктурнаяЕдиница
Внутреннее соединение втОтборПредковПоЗначениям КАК втРодители
По вт_Родословная_Номенклатура.Предок = втРодители.НоменклатураПредок
И вт_Родословная_Размещение.Предок = втРодители.РазмещениеПредок
И вт_Родословная_СтруктурнаяЕдиница.Предок = втРодители.Структурная_единицаПредок
Сгруппировать по
втОтбор.Номенклатура,
втОтбор.Размещение,
втОтбор.СтруктурнаяЕдиница
На первый взгляд запрос написан вполне корректно: используются только внутренние соединения, таблицы предварительно подготовлены. Но именно здесь кроется ловушка, в которую периодически попадает оптимизатор MS SQL Server.
Когда запрос выполняется за 5 секунд, СУБД строит оптимальный план выполнения. Она использует быстрые физические операторы соединения, такие как Hash Match (соединение хэшированием) или Merge Join (соединение слиянием). Однако при определенных условиях оптимизатор меняет свое решение и строит катастрофически неэффективный план. Проанализируем, почему это происходит.
sp_executesql. При первом запуске запроса оптимизатор оценивает переданные параметры (включая размер временных таблиц в данный момент) и строит план, который сохраняется в кэше. Если при первом запуске временные таблицы были пустыми или содержали мало записей, оптимизатор выберет алгоритм Nested Loops (вложенные циклы). Когда в следующий раз в таблице окажется 1.3 миллиона строк, СУБД продолжит использовать план с вложенными циклами из кэша. Вместо быстрого однократного сопоставления СУБД начнет выполнять миллионы итераций, что приведет к зависанию на часы.Размещение имеет составной тип данных. В СУБД MS SQL Server составной тип транслируется в несколько колонок (например, идентификатор типа _TYPE, реквизит ссылки _RTRef и двоичные данные _RRRef). Соединение по такому полю порождает сложные логические конструкции в SQL-коде с использованием условий CASE WHEN или множественных логических операторов AND/OR.Nested Loops.Max Degree of Parallelism установлен в значение 0, СУБД пытается распараллелить выполнение «зависшего» неоптимального запроса на все доступные ядра процессора. При обработке миллионов строк в параллельных потоках оптимизатор часто вынужден использовать операторы Table Spool (запись промежуточных данных во временную базу tempdb на диске). В результате гигантские объемы данных начинают непрерывно записываться и считываться с диска. Это мгновенно перегружает дисковую подсистему (возникает высокая очередь к диску), сводя на нет даже производительность скоростных SSD-накопителей.Зависание происходит потому, что в процессе неоптимального соединения количество промежуточных строк лавинообразно увеличивается. В нашем примере при детальном анализе выяснилось, что на одном из этапов неявного перемножения таблиц промежуточный результат раздувался до 85 миллионов строк!
Самый надежный и архитектурно правильный способ борьбы с нестабильными планами оптимизатора СУБД в 1С — это разбиение сложных соединений на последовательные шаги с созданием промежуточных временных таблиц. Временная таблица в 1С служит жесткой «границей планирования» для СУБД. При переходе к следующему шагу СУБД точно знает реальное количество записей в созданной таблице и строит абсолютно точный план.
Рассмотрим, как мы можем переписать исходный запрос. Сначала выделим соединение основных больших таблиц в отдельную промежуточную временную таблицу вт_ПромежуточныйОтбор. Мы также сразу решим проблему с составным типом поля Размещение, применив функцию ВЫРАЗИТЬ.
// Шаг 1: Готовим плоский промежуточный результат без иерархии предков
ВЫБРАТЬ
втОтбор.Номенклатура КАК Номенклатура,
вт_Родословная_Номенклатура.Предок КАК НоменклатураПредок,
ВЫРАЗИТЬ(втОтбор.Размещение КАК Справочник.Склады) КАК Размещение,
вт_Родословная_Размещение.Предок КАК РазмещениеПредок,
втОтбор.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
вт_Родословная_СтруктурнаяЕдиница.Предок КАК Структурная_единицаПредок
ПОМЕСТИТЬ вт_ПромежуточныйОтбор
ИЗ
вт_ТаблицаОтбораЛокальная КАК втОтбор
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Номенклатура КАК вт_Родословная_Номенклатура
ПО вт_Родословная_Номенклатура.ссылка = втОтбор.Номенклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_Размещение КАК вт_Родословная_Размещение
ПО вт_Родословная_Размещение.ссылка = ВЫРАЗИТЬ(втОтбор.Размещение КАК Справочник.Склады)
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_Родословная_СтруктурнаяЕдиница КАК вт_Родословная_СтруктурнаяЕдиница
ПО вт_Родословная_СтруктурнаяЕдиница.ссылка = втОтбор.StructureUnit;
// Обязательно индексируем поля связи созданной таблицы
ИНДЕКСИРОВАТЬ ПО
НоменклатураПредок,
РазмещениеПредок,
Структурная_единицаПредок;
После того как промежуточные данные отфильтрованы и сохранены во временную таблицу, мы выполняем финальное соединение с небольшой таблицей предков втОтборПредковПоЗначениям. Этот шаг гарантированно выполнится оптимально:
// Шаг 2: Финальный отбор по предкам и группировка результатов
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПромежуточныйОтбор.Номенклатура,
ПромежуточныйОтбор.Размещение,
ПромежуточныйОтбор.СтруктурнаяЕдиница
ИЗ
вт_ПромежуточныйОтбор КАК ПромежуточныйОтбор
ВНУТРЕННЕЕ СОЕДИНЕНИЕ втОтборПредковПоЗначениям КАК втРодители
ПО ПромежуточныйОтбор.НоменклатураПредок = втРодители.НоменклатураПредок
И ПромежуточныйОтбор.РазмещениеПредок = втРодители.РазмещениеПредок
И ПромежуточныйОтбор.Структурная_единицаПредок = втРодители.Структурная_единицаПредок
Благодаря разделению запроса на два независимых шага СУБД полностью защищена от эффекта «прослушивания параметров». Время выполнения такого пакета запросов становится стабильным и предсказуемым.
Давайте подробнее разберем, почему составные типы данных так сильно вредят производительности запросов. Когда мы пишем условие соединения по полю составного типа без явного ограничения типа, платформа 1С генерирует SQL-код с перебором всех возможных таблиц-справочников и документов, входящих в этот составной тип.
Если поле Размещение может содержать ссылки на справочники Склады, СтруктурныеЕдиницы и Контрагенты, СУБД сгенерирует сложнейшую конструкцию CASE с множеством неявных левых соединений (LEFT JOIN) к физическим таблицам этих справочников.
Чтобы избежать деградации производительности, мы должны следовать двум правилам:
ВЫРАЗИТЬ(Поле КАК Тип) непосредственно в секциях соединения или выборки. Это заставит платформу 1С обращаться напрямую к единственной физической таблице конкретного справочника.ОБЪЕДИНИТЬ ВСЕ, где в каждой части будет происходить явное приведение к своему типу. Это исключит использование тяжелой логики ИЛИ на уровне СУБД.Индексы во временных таблицах — мощный инструмент ускорения поиска, но использовать их нужно обдуманно. Создание индекса требует ресурсов процессора и диска на его построение. Проанализируем правила эффективного индексирования:
ИНДЕКСИРОВАТЬ ПО должен соответствовать порядку полей в условиях связи последующих соединений.tempdb и замедлить запись данных во временную таблицу. Ограничьтесь только ключевыми полями связи.Иногда корни проблемы лежат в системных настройках СУБД, которые были установлены по умолчанию. Для информационных баз 1С крайне важна правильная настройка параллелизма.
Проанализируем две ключевые настройки сервера MS SQL Server, которые должен проверить системный администратор:
1. Параметр Max Degree of Parallelism (MAXDOP). Настройка определяет максимальное количество ядер процессора, задействованных для выполнения одного запроса. Для СУБД, обслуживающих базы 1С, настоятельно рекомендуется устанавливать значение MAXDOP = 1 (полное отключение параллелизма для отдельных запросов) на этапе внедрения или при нестабильном поведении системы. В крайнем случае, для очень мощных серверов, это значение можно ограничить числом физических ядер в рамках одного NUMA-узла (обычно от 2 до 4), но ни в коем случае не оставлять равным 0.
2. Параметр Cost Threshold for Parallelism. Если параллелизм все же разрешен (MAXDOP > 1), этот параметр определяет «стоимость» запроса, начиная с которой СУБД будет пытаться его распараллелить. Значение по умолчанию (равное 5) слишком мало для современных серверов — СУБД будет пытаться распараллелить даже относительно простые запросы, что приведет к перегрузке памяти и tempdb. Рекомендуется установить значение данного параметра в диапазоне от 30 до 50.
3. Регулярное обслуживание СУБД. На сервере обязательно должны быть настроены регламентные задания по обновлению статистики (Update Statistics) и дефрагментации/перестроению индексов. Устаревшая статистика — вторая по частоте причина, заставляющая оптимизатор строить неэффективные планы запросов.
Мы детально изучили проблему нестабильного времени выполнения запросов к временным таблицам в 1С. Мы выяснили, что причина плавающего времени работы кроется в непредсказуемом поведении оптимизатора СУБД под влиянием устаревшего кэша планов, сложных соединений по составным полям и некорректных настроек параллелизма сервера.
Для гарантированного устранения подобных зависаний мы рекомендуем применять комплексный подход:
ВЫРАЗИТЬ в условиях связи.MAXDOP на сервере СУБД значением 1 или строго контролируемым числом ядер.