Почему после тестирования и исправления (ТиИ) стала сильно тормозить 1С и как вернуть прежнюю скорость?

← На главную

Многие технические специалисты сталкиваются с парадоксальной ситуацией: после проведения регламентного тестирования и исправления (ТиИ) информационной базы 1С с включенными опциями реиндексации и реструктуризации вместо ожидаемого прироста производительности система начинает работать крайне медленно. Пользователи жалуются на «зависания» интерфейса при открытии журналов документов, проведении операций или формировании отчетов, а простые запросы, которые ранее выполнялись мгновенно, начинают выполняться десятки секунд.

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

Что происходит на физическом уровне СУБД во время реструктуризации таблиц

Чтобы понять первопричину деградации производительности, давайте заглянем «под капот» процесса реструктуризации, выполняемого в рамках ТиИ:

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

Главное последствие этого процесса: для СУБД (например, MS SQL Server) пересозданная таблица является абсолютно новым объектом. С удалением старой таблицы бесследно уничтожаются и все накопленные ранее базы статистик распределения данных (карты распределения значений в колонках таблиц, гистограммы и плотности распределения).

Хотя в настройках баз данных MS SQL Server обычно включен параметр автоматического обновления статистики (AUTO_UPDATE_STATISTICS), данный механизм работает пороговым методом. Автообновление запускается только тогда, когда в таблице меняется значительный процент строк (обычно около 20% для старых версий SQL Server или по динамическому алгоритму в более новых версиях). Сразу после реструктуризации для СУБД таблица выглядит «чистой» и только что созданной, статистика по ней либо отсутствует вовсе, либо построена на основе случайной и крайне малой выборки строк (Default Sampling). В результате оптимизатор запросов СУБД теряет ориентиры и начинает строить неоптимальные планы выполнения запросов.

Разбор аномального поведения запросов: почему фильтр в параметрах виртуальной таблицы проигрывает секции ГДЕ

Рассмотрим два примера запросов, которые привел автор темы. Они наглядно демонстрируют, как отсутствие актуальной статистики «ослепляет» оптимизатор СУБД.

Первый вариант (фильтрация внутри параметров виртуальной таблицы):


ВЫБРАТЬ
    ЗаказыПокупателейОстатки.ЗаказПокупателя КАК ЗаказПокупателя,
    ЗаказыПокупателейОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки( , 
        ЗаказПокупателя В (
            ВЫБРАТЬ ПЕРВЫЕ 1000 
                ЗаказПокупателя.Ссылка 
            ИЗ 
                Документ.ЗаказПокупателя КАК ЗаказПокупателя 
            УПОРЯДОЧИТЬ ПО 
                ЗаказПокупателя.Дата УБЫВ
        )
    ) КАК ЗаказыПокупателейОстатки

Этот запрос до выполнения ТиИ работал быстро, а после стал выполняться целых 11 секунд.

Второй вариант (фильтрация перенесена в секцию ГДЕ):


ВЫБРАТЬ
    ЗаказыПокупателейОстатки.ЗаказПокупателя КАК ЗаказПокупателя,
    ЗаказыПокупателейОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки(, ) КАК ЗаказыПокупателейОстатки
ГДЕ
    ЗаказыПокупателейОстатки.ЗаказПокупателя В (
        ВЫБРАТЬ ПЕРВЫЕ 1000 
            ЗаказПокупателя.Ссылка 
        ИЗ 
            Документ.ЗаказПокупателя КАК ЗаказПокупателя 
        УПОРЯДОЧИТЬ ПО 
            ЗаказПокупателя.Дата УБЫВ
    )

Этот запрос даже на «ослепшей» СУБД продолжает отрабатывать менее чем за 1 секунду. Выясним, почему так происходит.

Когда СУБД выполняет первый запрос, оптимизатор видит подзапрос внутри параметров виртуальной таблицы остатков. Не имея актуальной статистики по таблице итогов регистра накопления и таблице документов, оптимизатор делает ложное предположение (Cardinality Estimation), что данный подзапрос вернет всего 1 строку (это стандартное значение по умолчанию при неопределенности).

Исходя из предположения, что на входе будет лишь одна строка, оптимизатор SQL Server выбирает наиболее «дешевый», по его мнению, физический оператор соединения таблиц — Nested Loops (Вложенные циклы). Алгоритм его работы прост: для каждой строки из внешнего набора данных СУБД выполняет поиск во внутренней таблице. Но в реальности подзапрос возвращает не одну строку, а 1000 строк. В результате СУБД вынуждена выполнить поиск по индексу в огромной физической таблице остатков регистра ровно 1000 раз! Это приводит к колоссальному числу операций чтения с диска (или из оперативной памяти) и задержке в 11 секунд.

Во втором запросе условие отбора вынесено в секцию ГДЕ. Здесь оптимизатор СУБД строит план выполнения иначе. Он оценивает выборку из таблицы остатков в целом, понимает, что объем данных велик, и вместо вложенных циклов выбирает гораздо более эффективные для больших объемов операторы соединения — Hash Match или Merge Join. СУБД за один проход сканирует необходимые данные и быстро соединяет их в памяти, укладываясь в долю секунды.

Решение проблемы на стороне СУБД: возвращаем скорость работы 1С

Для того чтобы немедленно вернуть скорость выполнения запросов на прежний уровень без изменения конфигурации 1С, необходимо принудительно обновить статистику распределения данных в SQL-базе. Рассмотрим три последовательных шага для администратора баз данных.

Шаг 1. Быстрое обновление статистики СУБД

Для быстрого исправления ситуации запустите среду MS SQL Server Management Studio (SSMS), выберите вашу базу данных 1С, откройте окно создания нового запроса и выполните системную процедуру:

EXEC sp_updatestats;

Эта команда выполнит обход всех таблиц базы данных и обновит статистику по тем объектам, в которых происходили изменения. Время выполнения процедуры зависит от размера базы данных, но обычно занимает от нескольких секунд до пары минут. После этого оптимизатор SQL мгновенно «увидит» реальную картину распределения данных, заменит неэффективные планы с Nested Loops на Hash Match, и скорость работы запросов вернется к исходным значениям.

Шаг 2. Глубокое обновление статистик (рекомендуемый метод)

Системная утилита sp_updatestats использует быстрый алгоритм сбора информации, сканируя таблицы выборочно (Default Sampling). Чтобы гарантировать построение идеальных планов запросов после масштабного ТиИ, рекомендуется выполнить полное сканирование таблиц с помощью команды UPDATE STATISTICS:


-- Для конкретной таблицы (например, таблицы остатков регистра накопления)
UPDATE STATISTICS [_AccumRgT12345] WITH FULLSCAN;

-- Или для всех таблиц базы данных с полным сканированием через курсор
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'UPDATE STATISTICS ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' WITH FULLSCAN;' + CHAR(13)
FROM sys.tables t
JOIN sys.schemes s ON t.schema_id = s.schema_id;
EXEC sp_executesql @sql;

Параметр WITH FULLSCAN заставляет СУБД прочитать 100% строк в таблицах, что гарантирует абсолютную точность гистограмм и исключает ошибки оптимизатора.

Шаг 3. Очистка кэша процедурных планов (Query Plan Cache)

Иногда даже после обновления статистик некоторые запросы продолжают тормозить. Это происходит из-за эффекта, известного как Parameter Sniffing: старый неоптимальный план выполнения запроса все еще хранится в оперативной памяти СУБД (в кэше планов), и SQL Server продолжает его использовать.

Чтобы сбросить кэш планов именно для вашей рабочей базы данных 1С (без перезапуска всей службы SQL Server, что могло бы прервать работу других баз), выполните команду:


-- Выясняем ID нашей базы данных
DECLARE @DBID INT = DB_ID();
-- Очищаем кэш планов для найденного ID
DBCC FLUSHPROCINDB (@DBID);

Решение проблемы на стороне разработчика 1С: пишем отказоустойчивый и быстрый код

Решение на стороне СУБД спасет ситуацию прямо сейчас, но при следующем ТиИ, обновлении конфигурации или резком скачке объема данных проблема может вернуться. Наша задача как разработчиков — писать код запросов так, чтобы оптимизатор СУБД физически не мог построить неверный план выполнения. Проанализируем, какие приемы программирования в 1С гарантируют стабильно высокую скорость.

1. Использование Временных Таблиц вместо подзапросов

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

2. Индексация временных таблиц

Если во временную таблицу помещается более 20–50 строк, её обязательно нужно индексировать по тем полям, по которым в дальнейшем будет происходить соединение или фильтрация. Наличие индекса во временной таблице дает оптимизатору точную статистику о распределении значений в ней.

3. Избавление от составных типов данных с помощью конструкции ВЫРАЗИТЬ

Если измерение регистра (например, ЗаказПокупателя) или реквизит документа имеет составной тип данных, СУБД хранит его в виде нескольких колонок (под тип, под уникальный идентификатор строки и т.д.). При соединении таблиц без явного указания типа СУБД генерирует тяжелые конструкции LEFT JOIN со всеми таблицами документов, входящими в этот составной тип.

Использование конструкции ВЫРАЗИТЬ(ДокументСсылка КАК Документ.ИмяДокумента) жестко отсекает лишние связи на уровне SQL-запроса, уменьшая количество физических чтений данных в разы.

Разбор оптимизированного кода запроса 1С

Рассмотрим реальный рабочий код из Сообщения 11, который используется в обработчике динамического списка или журнала документов для выделения просроченных заказов:


// Неоптимальный вариант, приводивший к зависаниям на 11 секунд после ТиИ:
ВЫБРАТЬ
    ЗаказыПокупателейОстатки.ЗаказПокупателя КАК Заказ
ИЗ
    РегистрНакопления.ЗаказыПокупателей.Остатки( , 
        ЗаказПокупателя В (
            ВЫБРАТЬ 
                Документы.Ссылка 
            ИЗ 
                Документы КАК Документы
        )
    ) КАК ЗаказыПокупателейОстатки

А теперь спроектируем и разберем идеально оптимизированный вариант этого запроса, который будет работать быстро всегда, независимо от состояния статистик СУБД:


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

Запрос.УстановитьПараметр("ТаблицаДокументов", ПолучитьТаблицуДокументовДляСписка());
РезультатЗапроса = Запрос.Выполнить();

Проанализируем преимущества этого подхода:

Конструкция ВЫРАЗИТЬ(...) убирает избыточные соединения таблиц на уровне SQL-запроса, если в исходной таблице были документы других типов.
Инструкция ИНДЕКСИРОВАТЬ ПО ЗаказПокупателя создает индекс для временной таблицы в СУБД. Оптимизатор мгновенно получает доступ к статистике этой таблицы.
Передача временной таблицы в параметры виртуальной таблицы остатков через конструкцию В (ВЫБРАТЬ ... ИЗ ВТ_ОтобранныеЗаказы) гарантирует, что СУБД выберет остатки только по нужным нам заказам, не тратя ресурсы на сканирование всей таблицы итогов регистра накопления.

Регламентное обслуживание базы данных после ТиИ

Чтобы обезопасить себя от внезапного падения производительности 1С в будущем, возьмем за правило выполнять обязательный регламент технического обслуживания базы данных СУБД сразу после проведения любых масштабных процедур (тестирование и исправление, обновление конфигурации, массовый импорт или перепроведение данных):

Обновление статистик с полным сканированием (Fullscan): не реже 1 раза в неделю, а также обязательно сразу после ТиИ.
Дефрагментация и перестроение индексов (Reorganize / Rebuild): регулярное выполнение скрипта обслуживания индексов в зависимости от степени их фрагментации (рекомендуется выполнять еженедельно ночью).
Очистка процедурного кэша: после глобального изменения структуры метаданных для удаления неактуальных планов выполнения.

Настроив один раз автоматические планы обслуживания в MS SQL Server (Maintenance Plans) или с помощью бесплатных скриптов (например, Ola Hallengren), вы навсегда избавите пользователей вашей системы 1С от неприятных «тормозов» и обеспечите стабильно высокую скорость работы программы.