Как выявить причины роста tempdb и отследить запросы с временными таблицами в 1С?

← На главную

При активной работе пользователей и выполнении тяжелых отчетов или регламентных заданий системная база tempdb в MS SQL Server может лавинообразно увеличиваться в размерах. Разберем, влияют ли на этот процесс вложенные запросы, почему прямое отслеживание таблиц вида #tt не дает желаемого результата и как правильно диагностировать проблемы производительности.

Вложенные запросы против временных таблиц: что сильнее нагружает tempdb?

Рассмотрим распространенное заблуждение: многие разработчики считают, что только физические временные таблицы расходуют дисковое пространство tempdb, а вложенные запросы работают исключительно в оперативной памяти. На практике ситуация выглядит иначе:

Вложенные запросы и внутренние рабочие таблицы (Worktables/Spills): Оптимизатор СУБД пытается выполнить вложенные запросы на лету. Однако если запрос содержит сложные соединения, группировки или сортировки больших массивов, а выделенной памяти (Memory Grant) оказывается недостаточно из-за устаревшей статистики, SQL Server выполняет сброс данных на диск (Spill). В результате во внутреннем пространстве tempdb создаются невидимые для пользователя рабочие таблицы (Worktables), что приводит к росту дисковой очереди и раздуванию базы.
Явные временные таблицы платформы 1С: При выполнении запросов с конструкцией ПОМЕСТИТЬ платформа 1С создает в tempdb таблицы вида #tt<N>. Если объем данных невелик, они могут кэшироваться в буферном пуле, но при больших выборках занимают реальное дисковое пространство.
Версионное хранилище (Version Store): 1С по умолчанию использует уровень изоляции Read Committed Snapshot Isolation (RCSI). Все предыдущие версии изменяемых строк сохраняются в tempdb. Длительные транзакции блокируют очистку устаревших версий, что является наиболее частой причиной неконтролируемого роста tempdb.

Почему сложно найти таблицу по имени вида #tt1

Проанализируем структуру именования временных таблиц в MS SQL Server. Когда в коде 1С мы помещаем данные во временную таблицу, SQL Server добавляет к ее имени уникальный суффикс из символов подчеркивания и шестнадцатеричного идентификатора (например, #tt1___________________000000000000001A). Это сделано для изоляции пространств имен разных пользовательских сессий.

Пытаться оценить размер конкретной временной таблицы через системные представления только по ее имени неэффективно: в системе одновременно могут существовать десятки таблиц с префиксом #tt1 от разных сеансов. Вместо ручного перебора имен таблиц необходимо исследовать план выполнения запроса и потребление ресурсов конкретными сессиями.

Как найти проблемный запрос в трассировщике SQL Server

Когда на сервере работает множество пользователей под технической учетной записью (например, sa или usr1cv8), отфильтровать нужный запрос стандартными средствами бывает непросто. Рассмотрим эффективные способы локализации запроса.

Способ 1. Использование уникального маркера в тексте запроса

Добавим в текст исследуемого запроса фиктивное поле с уникальной строкой. Это позволит мгновенно найти нужный SQL-батч в SQL Server Profiler или сессии Extended Events по фильтру текста:


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

Способ 2. Локализация через Технологический журнал (ТЖ) 1С

Настроим сбор событий технологического журнала для выявления контекста исполнения запроса. С помощью фильтра по имени процесса p:processname (имя рабочей базы) и событию DBMSSQL мы получим точный текст SQL-запроса, длительность его выполнения и номер системного соединения (t:connectID):


<log location="C:\LOGS\TJ_TEMPDB" history="4">
    <event>
        <eq property="name" value="DBMSSQL"/>
        <eq property="p:processname" value="ИмяНашейБазы"/>
        <ge property="duration" value="100000"/>
    </event>
    <property name="all"/>
</log>

Определение сессий, занимающих место в tempdb через системные представления (DMV)

Выясним, какие именно сессии создают нагрузку на tempdb, выполнив диагностический запрос в среде SQL Server Management Studio. Для этого используем динамические административные представления sys.dm_db_session_space_usage и sys.dm_exec_sessions:


SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    (su.user_objects_alloc_page_count - su.user_objects_dealloc_page_count) * 8 / 1024 AS UserObjectsMB,
    (su.internal_objects_alloc_page_count - su.internal_objects_dealloc_page_count) * 8 / 1024 AS InternalObjectsMB
FROM sys.dm_db_session_space_usage su
JOIN sys.dm_exec_sessions s ON su.session_id = s.session_id
WHERE (su.user_objects_alloc_page_count + su.internal_objects_alloc_page_count) > 0
ORDER BY UserObjectsMB + InternalObjectsMB DESC;

В выводе запроса обратим внимание на две колонки:

UserObjectsMB: Объем пользовательских объектов (явные временные таблицы 1С).
InternalObjectsMB: Объем внутренних служебных объектов (результаты сортировок, хэш-соединений и сбросов из-за неоптимальных планов).

Практические рекомендации по оптимизации работы с tempdb в 1С

Обязательное удаление временных таблиц: Если в коде используется МенеджерВременныхТаблиц, не ждем окончания работы процедуры или закрытия соединения. Как только данные таблицы обработаны, вызываем оператор УНИЧТОЖИТЬ ВТ_ИмяТаблицы. Это освободит страницы в tempdb немедленно.
Индексация временных таблиц: Всегда указываем предложение ИНДЕКСИРОВАТЬ ПО для временных таблиц, содержащих более сотни записей, если они участвуют в дальнейших соединениях или фильтрах. Без индексов СУБД выполняет полное сканирование, порождая паразитные сбросы данных в служебные таблицы.
Анализ плана запроса вместо поиска файлов: Вместо попыток замерить физический размер таблиц на диске включаем актуальный план выполнения (Actual Execution Plan). В плане наглядно видно несоответствие между оценкой строк (Estimated Rows) и реальным количеством (Actual Rows), а также предупреждения Sort Warnings или Hash Warnings, свидетельствующие о нехватке оперативной памяти.
Контроль длительности транзакций: Разделяем массивные пакетные обработки на небольшие порции (чанги) с фиксацией транзакций. Это предотвратит блокировку версионного хранилища (Version Store) и позволит сборщику мусора СУБД вовремя освобождать страницы tempdb.