Вы когда-нибудь получали предупреждение о том, что ваша tempdb в SQL Server почти заполнена? Если да, то вы не одиноки. В этой статье мы рассмотрим реальный сценарий, когда tempdb заполнялась быстро, и как это было решено.

Проблема

Однажды, во время мониторинга экземпляра SQL Server 2014, на котором размещались вторичные базы данных AlwaysOn, я получил предупреждение о том, что tempdb занимает 90 процентов размера диска, оставляя только 10 процентов. После входа на сервер и проверки tempdb все казалось нормальным. Рутинные запросы выполнялись, и проблем с использованием памяти или процессора не было. Однако tempdb продолжала заполняться.

Для дальнейшего исследования я выполнил запрос для проверки использования пространства tempdb:

SELECT GETDATE() AS runtime,
    SUM(user_object_reserved_page_count) * 8 AS usr_obj_kb,
    SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb,
    SUM(version_store_reserved_page_count) * 8 AS version_store_kb,
    SUM(unallocated_extent_page_count) * 8 AS freespace_kb,
    SUM(mixed_extent_page_count) * 8 AS mixedextent_kb
FROM sys.dm_db_file_space_usage;

Результаты показали, что Version Store использовал значительное количество места в tempdb. Это привлекло мое внимание, так как Version Store связан с уровнем изоляции снимка, который копирует версии строк в tempdb.

Исследование

Затем я проверил активные сеансы на сервере, чтобы узнать, вызывают ли какие-либо запросы проблему. Удивительно, что выполнялись только рутинные запросы, которые раньше не вызывали проблем.

С помощью счетчиков производительности я отслеживал следующие метрики:

• SQLServer:Transactions\Longest Transaction Running Time – показывает самую длительную транзакцию, которая в настоящее время выполняется
• SQLServer:Transactions\Version Store Size (KB) – показывает текущий размер всех версионных хранилищ
• SQLServer:Transactions\Version Cleanup rate (KB/s) – показывает скорость очистки версий в tempdb
• SQLServer:Transactions\Version Generation rate (KB/s) – показывает скорость генерации версий

Счетчик Longest Transaction Running Time указывал на то, что старая транзакция все еще использует версионное хранилище. Размер Version Store быстро рос, в то время как скорость очистки версий была равна нулю, что указывало на отсутствие очистки.

Чтобы определить сеанс, вызывающий проблему, я сопоставил время самой старой транзакции с помощью следующего запроса:

SELECT DATEADD(ss, -637803, GETDATE())

Затем я сравнил это время с колонкой last_batch в таблице master.sys.sysprocesses, чтобы найти соответствующий SPID. SPID находился в состоянии ожидания и был 7 дней старым.

Как только я завершил ожидающий сеанс, началась очистка версий, и размер Version Store начал уменьшаться.

Вывод

Мониторинг роста tempdb на вторичных серверах AlwaysOn является важным, так как tempdb используется для хранения версий строк и временной статистики только для чтения. Счетчики производительности, такие как Version Store Size, Version Cleanup rate, Version Generation rate и Longest Transaction Running Time, полезны при устранении проблем с ростом версионного хранилища в tempdb.

Кроме того, хорошей практикой является завершение неактивных сеансов, которые работали в течение продолжительного времени.

Надеюсь, что эта статья дала вам ценные сведения о устранении проблем с ростом tempdb в SQL Server. Если у вас есть вопросы или вы хотите поделиться своими собственными опытами, не стесняйтесь оставить комментарий ниже.