Почему растет tempdb до гигантских размеров при выполнении отчетов в 1С 7.7 на SQL Server?

← На главную

Ситуация, когда база данных tempdb начинает неконтролируемо расти, способна парализовать работу всей системы. Особенно остро это ощущается в связке старых платформ, таких как 1С:Предприятие 7.7, и MS SQL Server 2005. Представьте: основная база занимает 120 Гб, а временная база tempdb внезапно раздувается до 140 Гб и выше, съедая все свободное место на дисках. Разберем подробно, почему это происходит и как с этим бороться.

Анализируем основную причину: внешние функции в запросах

Проанализируем ситуацию, описанную в теме. Отчет, который успешно работал 8 лет, внезапно начал «вешать» сервер. Выяснилось, что в запрос была добавлена внешняя функция 1С для подсчета суммы. Рассмотрим пример кода, который стал «убийцей» производительности:


// Пример проблемного участка кода в тексте запроса
Сумма(ПолучитьСуммуПродаж(СуммаЗаяв, СуммаВклНДС, НДСЗаяв)) Когда (ФлагСписания <> 1)

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

В нашем случае отчет обрабатывает около 3000 документов в день, в каждом из которых в среднем 30 строк. За период в 2 месяца это миллионы итераций. Если SQL Server не может вычислить результат одним проходом, он начинает активно использовать tempdb для хранения промежуточных состояний, «разливая» данные из оперативной памяти на диск (это называется Spills).

Механизм роста tempdb: почему памяти не хватает?

Разберем по шагам, что именно заставляет tempdb пухнуть:

Временные таблицы и промежуточные результаты: Если запрос содержит сложные соединения (Joins) или группировки, а объем данных велик, SQL Server создает внутренние объекты в tempdb. Если план запроса неэффективен (как в случае с внешней функцией), объем этих объектов может в десятки раз превышать объем исходных данных.
Table Spools (ленивые таблицы): Это механизм, когда SQL Server сохраняет промежуточный результат запроса во временную таблицу в tempdb, чтобы использовать его повторно в рамках того же запроса. При неудачном стечении обстоятельств объем такой «прослойки» становится неуправляемым.
Версионность строк (Snapshot Isolation): Если на сервере включен режим Read Committed Snapshot Isolation, то при любой долгой транзакции (а наш отчет — это длинный SELECT) сервер обязан хранить в tempdb версии всех строк, которые изменяются другими пользователями в данный момент. На высоконагруженной базе это приводит к лавинообразному росту Version Store.
Сетевые задержки и открытые курсоры: Платформа 7.7 часто использует серверные курсоры. Если отчет «зависает» на обработке внешней функции или долго ждет ответа от клиента, SQL Server держит временные объекты в tempdb до тех пор, пока курсор не будет закрыт.

Как продиагностировать состояние tempdb?

Чтобы понять, чем именно занята временная база, воспользуемся диагностическим запросом. Рассмотрим, какие объекты занимают место:


SELECT
    (SUM(unallocated_extent_page_count)*1.0/128) AS FreeSpace_MB,
    SUM(user_object_reserved_page_count) * 8 / 1024 AS UserObjects_MB,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalObjects_MB,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS VersionStore_MB,
    SUM(mixed_extent_page_count) * 8 / 1024 AS MixedExtent_MB
FROM tempdb.sys.dm_db_file_space_usage

Если львиную долю занимают InternalObjects — проблема в планах запросов и нехватке памяти для сортировки. Если VersionStore — виноваты длинные транзакции и режим изоляции снимков.

Разберем шаги по исправлению ситуации

1. Оптимизация кода 1С

Первым делом необходимо удалить вызовы внешних функций из тела запроса. Весь расчет должен производиться либо средствами прямого SQL-запроса (используя стандартные агрегатные функции SUM, CASE WHEN), либо данные должны быть выгружены в Таблицу Значений (ТЗ) и обработаны кодом 1С уже после получения выборки. Это разгрузит SQL Server и позволит ему построить оптимальный план.

2. Ограничение прав и фильтрация

Запретите запуск тяжелых отчетов без установленных фильтров (период, контрагент, склад). Если пользователь запускает универсальный отчет «за все время», он гарантированно переполнит tempdb даже при идеальном коде.

3. Настройка SQL Server (MAXDOP)

Проанализируем настройку параллелизма. По умолчанию MAXDOP (Max Degree of Parallelism) часто равен 0 (использовать все ядра). Для баз 1С рекомендуется ограничивать это значение до 1 или 2. Это предотвратит ситуацию, когда один тяжелый запрос пытается захватить все ресурсы сервера и плодит множество параллельных потоков, каждый из которых требует места в tempdb.

4. Флаги трассировки и файлы tempdb

Для SQL Server 2005/2008 крайне важно настроить tempdb правильно:

Разбейте tempdb на несколько файлов (от 4 до 8, в зависимости от количества ядер процессора) одинакового размера.
Включите флаги трассировки 1117 (равномерное расширение файлов в группе) и 1118 (выделение полных экстентов, что снижает конкуренцию за страницы распределения).

5. Обновление статистики

Выясним причину, почему отчет «работал 8 лет и перестал». Скорее всего, объем данных перешагнул порог, при котором старая статистика позволяла строить эффективные планы. Выполните полное обновление статистики для всех таблиц базы данных:

EXEC sp_updatestats;

Заключение

Борьба с ростом tempdb — это всегда комплекс мер. В случае с 1С 7.7 основной удар должен быть направлен на качество кода запросов. Использование внешних функций в SQL-запросах — это «антипаттерн», который неизбежно ведет к деградации производительности. Сочетание чистого SQL-кода, правильной настройки параллелизма и регулярного обслуживания статистики позволит держать размер временной базы в разумных пределах.