Когда TempDB растёт «вширь»: как RCSI и длинные транзакции незаметно разрушают SQL Server

Автор: Steve Stedman, When TempDB Grows “Up and to the Right”: How RCSI and Open Transactions Quietly Break SQL Server

Одна из самых опасных проблем SQL Server — та, которую вы не замечаете, пока диск не заполнится, а производственная система не упадёт. TempDB особенно хороша в таком типе отказа — она незаметно растёт в фоновом режиме, пока внезапно не становится проблемой для всех.

Недавно мы просматривали отчёт о распределении пространства TempDB (TempDB Allocation History Report), и он прекрасно проиллюстрировал проблему, с которой мы сталкиваемся всё чаще по мере того, как растёт применение READ COMMITTED Snapshot Isolation (RCSI). RCSI обычно является правильным выбором, но когда что-то идёт не так, TempDB может расти взрывным образом, и большинство команд не понимают почему, пока не становится слишком поздно.

Почему отчёт о распределении пространства TempDB важен

Отчёт о распределении пространства TempDB наиболее ценен на производственных системах с реальной рабочей нагрузкой. Тестовые серверы редко показывают что-то интересное, потому что на них нет длительных транзакций, высокой конкурентности или продолжительной пользовательской активности.

Однако на производственных серверах этот отчёт рассказывает подлинную историю. Он показывает, как используется пространство TempDB с течением времени, включая:

• Хранилище версий (version store)
• Пользовательские объекты (user objects)
• Внутренние объекты (internal objects)
• Временные таблицы (temporary tables)

Когда TempDB начинает неожиданно расти, этот отчёт позволяет визуально определить коренную причину вместо того, чтобы гадать.

RCSI — не враг

Давайте проясним: RCSI — это не плохая функция. На самом деле, мы рекомендуем её примерно для 99% производственных сред SQL Server. Она кардинально снижает блокировки, позволяя читателям обращаться к версиям строк вместо ожидания блокировок.

Но у RCSI есть цена, и эта цена оплачивается в TempDB.

Когда RCSI включена, SQL Server хранит старые версии строк в хранилище версий (version store), которое находится в TempDB. В нормальных условиях эти версии автоматически удаляются после завершения транзакций.

Проблема возникает, когда они не завершаются.

Открытые транзакции и хранилище версий

Открытая транзакция при включённой RCSI предотвращает очистку старых версий строк в хранилище версий. Пока эта транзакция остаётся открытой, SQL Server должен сохранять каждую версию строки, необходимую для поддержки согласованного чтения.

Именно здесь всё идёт наперекосяк.

Мы видели клиентов, у которых приложение оставляло транзакцию открытой на часы или даже дни, в то время как другая активность продолжалась нормально. Каждая операция обновления, удаления или вставки в течение этого времени создавала всё новые версии. Хранилище версий продолжало расти, и TempDB следовала за ним.

В одном реальном случае у клиента образовалась TempDB размером четыре терабайта в системе, где ни одна из пользовательских баз данных даже близко не была такого размера. Виновником оказалась одна единственная открытая транзакция в сочетании с RCSI.

Что показал бы график

Когда вы смотрите на отчёт о распределении пространства TempDB в подобном сценарии, проблема выявляет себя мгновенно.

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

То, что вы часто увидите, — это шаблон роста «вверх и вправо» (up and to the right growth pattern). Это хорошо, когда вы смотрите на цифры продаж. Это ужасно, когда вы смотрите на размер TempDB.

TempDB должна колебаться. Она должна расти и сжиматься. Когда она только растёт — и никогда не возвращается обратно — у вас проблема с очисткой.

Связываем с открытыми транзакциями

Именно здесь объединение отчётов становится мощным инструментом.

Когда проблема заключается в росте хранилища версий TempDB, отчёт об открытых транзакциях (Open Transactions Report) почти всегда подтверждает это. Обычно вы найдёте:

• Длительные транзакции
• Спящие сеансы (sleeping sessions), удерживающие транзакции открытыми
• Код приложения, который никогда не выполняет фиксацию (COMMIT) или откат (ROLLBACK) должным образом

Пока эти транзакции остаются открытыми, TempDB будет продолжать расти. Резервные копии не помогут. Перезапуск заданий не поможет. Единственное реальное исправление — это выявление и устранение открытой транзакции.

Почему большинство команд упускают это из виду

Большинство ИТ-команд не следят за TempDB достаточно внимательно. Ещё меньше понимают, как взаимодействуют RCSI, очистка хранилища версий и открытые транзакции.

К тому времени, когда TempDB заполняет диск, ущерб уже нанесён. Сервер может перестать отвечать, приложения падают, и внезапно все спрашивают: «Как это произошло?»

Ответ почти всегда один: это произошло медленно, тихо и без надлежащего мониторинга.

Как мы выявляем это до того, как это перерастёт в кризис

Именно такие проблемы предотвращаются с помощью проактивного мониторинга SQL Server, нужно непрерывно отслеживать использование TempDB, рост хранилища версий и открытые транзакции. Когда TempDB начинает демонстрировать тенденцию к росту «вширь», мы расследуем это немедленно — задолго до того, как диск заполнится.

Такая видимость позволяет нам:

• Выявлять раздувание хранилища версий, связанное с RCSI, на раннем этапе
• Точно определять открытые транзакции, вызывающие задержки очистки
• Работать с командами разработчиков для исправления коренной причины
• Предотвращать сбои вместо того, чтобы реагировать на них

Пример запроса по использованию tempdb:

SELECT
    SUM(user_object_reserved_page_count) * 8 / 1024.0 AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024.0 AS internal_objects_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024.0 AS version_store_mb,
    SUM(unallocated_extent_page_count) * 8 / 1024.0 AS unallocated_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024.0 / NULLIF(
        (SUM(user_object_reserved_page_count) +
         SUM(internal_object_reserved_page_count) +
         SUM(version_store_reserved_page_count) +
         SUM(unallocated_extent_page_count)) * 8 / 1024.0, 0) * 100 AS version_store_pct
FROM tempdb.sys.dm_db_file_space_usage;

Чтобы найти виновную открытую транзакцию, из-за которой пухнет хранилище версий, выполните следующий запрос. Он покажет все открытые транзакции, которые держат версии в TempDB, отсортированные по длительности:

SELECT
    tst.session_id,
    tst.transaction_id,
    tat.transaction_begin_time,
    DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS open_duration_seconds,
    tat.transaction_state,
    s.program_name,
    s.host_name,
    s.login_name,
    s.status,
    tst.session_id AS blocker_session_id
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst
    ON tst.transaction_id = tat.transaction_id
JOIN sys.dm_exec_sessions s
    ON s.session_id = tst.session_id
WHERE tat.transaction_state = 2     -- активная (не зафиксирована)
  AND tat.transaction_begin_time IS NOT NULL
ORDER BY tat.transaction_begin_time ASC;   -- самые старые сверху

Что делать

1. Найти проблемную транзакцию по session_id.
2. Проверить, можно ли её закрыть (KILL <session_id> — крайняя мерия, если это не системный процесс).
3. Исправить код приложения, который не завершает транзакцию (добавить COMMIT или ROLLBACK).
4. Настроить мониторинг, чтобы алерт срабатывал при version_store_mb > 10 ГБ или при наличии транзакций старше N секунд.