Каждый администратор баз данных и разработчик 1С рано или поздно сталкивается с классической проблемой производительности: запрос начинает работать крайне медленно, а анализ плана выполнения показывает катастрофическое расхождение между ожидаемым и фактическим количеством строк. Например, СУБД ожидает получить всего 3 700 строк, выделяет под этот объем оперативную память (Memory Grant), а в реальности при выполнении запроса выбирается 20 000 или даже 100 000 строк. Оперативной памяти не хватает, СУБД сбрасывает промежуточные результаты сортировки или соединения в медленную базу tempdb (происходит так называемый TempDB Spill), и производительность системы падает в разы.
Очевидное и стандартное решение в таких случаях — обновить статистику по таблице с помощью команды UPDATE STATISTICS и очистить кэш планов через DBCC FREEPROCCACHE. Но что делать, если после выполнения этих регламентных операций повторный запрос выдает точно такой же неоптимальный план выполнения и СУБД продолжает «промахиваться» в оценках? Проанализируем эту ситуацию детально, разберем глубокие технические причины такого поведения СУБД в связке с платформой «1С:Предприятие» и найдем работающие методы решения проблемы.
Для того чтобы понять, почему обновление статистики не приносит желаемого результата, нам необходимо заглянуть в структуру индексов, которые платформа 1С автоматически создает на уровне СУБД MS SQL Server. В типовых конфигурациях 1С (особенно построенных на базе Библиотеки стандартных подсистем — БСП) по умолчанию включен общий реквизит ОбластьДанныхОсновныеДанные. Этот реквизит выступает в роли разделителя данных для поддержки облачной модели сервиса (например, технологии 1С:Фреш).
Разберем, к каким критическим последствиям для оптимизатора MS SQL Server это приводит:
_AreaNo или соответствующее поле вида _FldXXXX) в качестве первой (ведущей) колонки практически во все создаваемые индексы таблиц._AreaNo во всех без исключения строках таблицы имеет одно и то же значение (как правило, 0). В результате детальная гистограмма статистики такого индекса вырождается в единственный шаг (один бакет) с нулевой селективностью.Таким образом, оптимизатор MS SQL Server становится абсолютно «слепым» к распределению реальных и важных для запроса данных (таких как даты документов, ссылки на справочники, числовые значения), которые находятся на вторых, третьих и последующих позициях в составном индексе. Обслуживать и обновлять такие индексные статистики практически бесполезно — они все равно не дают оптимизатору распределения.
Выясним причину, почему система вообще сохраняет работоспособность при такой схеме. Нас спасает включенная по умолчанию опция СУБД AUTO_CREATE_STATISTICS. Когда оптимизатор SQL анализирует запрос, в условиях (предикатах) которого фигурируют вторые или третьи поля индексов 1С, он «на лету» создает автоматические одноколоночные статистики (single-column statistics) по этим конкретным полям. Именно по этим автосозданным статистикам строится корректная детальная гистограмма, на которую опирается оптимизатор при выборе плана запроса. Но если СУБД по какой-то причине использует именно индексную статистику, план будет неоптимальным.
Дополнительную сложность накладывает внутреннее архитектурное ограничение MS SQL Server: гистограмма любого объекта статистики не может содержать более 200 шагов (интервалов/бакетов). Это жестко зашитый в СУБД лимит, созданный ради обеспечения высокой скорости компиляции планов запросов. Если бы гистограммы содержали тысячи шагов, фаза оптимизации запросов требовала бы слишком много оперативной памяти и процессорного времени.
Но что происходит, когда таблица разрастается до огромных масштабов, например до 183 миллионов строк? Распределить такой гигантский объем данных всего по 200 интервалам без катастрофической потери точности невозможно. СУБД вынуждена сильно усреднять данные. Особенно сильно страдают самые свежие данные, расположенные на «хвосте» распределения (например, документы за текущий день или неделю), так как они сливаются в один широкий шаг гистограммы с историческими данными.
Разберем эффективные методы обхода этого ограничения:
Одним из лучших решений для борьбы с неточными планами запросов на больших таблицах является создание отфильтрованной статистики. Мы можем принудительно заставить MS SQL Server построить детальную гистограмму из 200 шагов не по всей таблице, а только по ее актуальной части (например, по записям за последний месяц или год).
Посмотрим на пример SQL-скрипта для создания такой статистики:
CREATE STATISTICS Stat_RecentDocuments
ON dbo._Document150 (_Period)
WHERE _Period > '2026-01-01T00:00:00';
Благодаря этому предикату WHERE оптимизатор получит в свое распоряжение свежую и максимально точную гистограмму распределения данных по полю периода за указанный промежуток времени. При компиляции запросов, затрагивающих оперативные данные, СУБД будет использовать именно эту отфильтрованную статистику, что позволит избежать некорректной оценки количества строк.
Также в качестве альтернативного трюка можно рассмотреть создание отфильтрованных индексов (Filtered Indexes). Они работают аналогичным образом: индекс строится по предикату, и СУБД автоматически создает для него точную отфильтрованную статистику.
Когда мы выполняем стандартную регламентную процедуру обновления статистики на таблицах объемом свыше 100 миллионов строк, СУБД по умолчанию запускает этот процесс в режиме случайной выборки (Sampled). Чтобы сэкономить время, SQL Server может просканировать менее 1% от общего объема таблицы.
При таком низком проценте выборки на сверхбольших таблицах гистограмма получается крайне искаженной, а данные о плотности распределения — недостоверными. В результате, даже если мы только что выполнили UPDATE STATISTICS, оптимизатор продолжает строить неверные планы.
Для решения этой проблемы применим следующие шаги:
UPDATE STATISTICS dbo._Document150 WITH FULLSCAN;
UPDATE STATISTICS dbo._Document150 WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
Ниже рассмотрим пример скрипта, который позволяет автоматизировать обновление статистики с режимом FULLSCAN для всех крупных таблиц документов и регистров в базе 1С:
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL = @SQL + N'UPDATE STATISTICS '
+ QUOTENAME(schema_name(t.schema_id)) + N'.' + QUOTENAME(t.name)
+ N' WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;' + CHAR(13) + CHAR(10)
FROM sys.tables t
WHERE t.name LIKE '_Document%'
OR t.name LIKE '_AccumReg%'
OR t.name LIKE '_InfoReg%';
EXEC sp_executesql @SQL;
В современных версиях MS SQL Server (начиная с версии 2017) реализована замечательная технология — Memory Grant Feedback (обратная связь по выделению памяти). Она призвана автоматически исправлять ошибки планирования, подобные описанной автором в начале темы.
Если при первом запуске запроса СУБД выделила мало памяти и произошел сброс в tempdb, механизм Memory Grant Feedback зафиксирует это событие. При следующем запуске этого же запроса СУБД автоматически выделит больше оперативной памяти, предотвращая медленный сброс на диск.
Однако в реальных базах данных 1С этот механизм часто оказывается бессилен. Разберем причину:
Платформа 1С при работе с базой данных активно использует временные таблицы СУБД. При этом имена временных таблиц генерируются динамически при каждом новом сеансе работы или запуске алгоритма (например, создаются таблицы с уникальными именами вроде #tt1, #tt2 и так далее). Для оптимизатора MS SQL Server запросы с разными именами временных таблиц — это абсолютно уникальные, новые запросы. СУБД не может сопоставить их с предыдущими запусками, кэш планов для них не используется повторно в полной мере, и механизм адаптивного выделения памяти (Memory Grant Feedback) просто не успевает сработать.
Подведем итог и составим подробный план действий для исправления планов запросов на больших таблицах в среде 1С:
CREATE STATISTICS ... WHERE для актуального интервала дат.FULLSCAN и опцией PERSIST_SAMPLE_PERCENT = ON.Применяя эти методы в комплексе, мы сможем гарантировать стабильную и быструю работу даже самых нагруженных запросов к гигантским таблицам в базах данных 1С.