В этой статье я покажу вам, как обнаружить нагрузку на TempDB менее чем за 45 секунд и, что ещё важнее, — как правильно её интерпретировать.
Потому что выявление истинного узкого места — это то, что отличает реактивных администраторов баз данных от инженеров по производительности.
Когда сервер работает медленно, но метрики CPU и ввода-вывода выглядят нормально. Когда планы выполнения выглядят приемлемо, но что-то всё равно не так. Когда запросы нестабильны — то быстры, то еле ползут — и пользователи жалуются, хотя вы не видите ничего очевидного...
Вот где многие администраторы баз данных тратят часы.
👉 Помните: TempDB часто оказывается замешана, но почти никогда не является первопричиной.
🧠 Сначала: измените своё мышление
TempDB — это НЕ «служебная база данных».
👉 Это скрытый операционный двигатель SQL Server.
— Промежуточные операции (SORT, HASH, SPOOL)
— Обработка нехватки памяти
— Управление версиями строк (Snapshot Isolation)
— Временные структуры
💣 TempDB — это место, где SQL Server компенсирует что-то неэффективное.
👉 Если TempDB испытывает нагрузку… настоящая проблема обычно находится в другом месте.
🧪 Шаг 1 — Поймите, КАК используется TempDB
SELECT
SUM(user_object_reserved_page_count) * 8 / 1024.0 AS UserObjectsMB,
SUM(internal_object_reserved_page_count) * 8 / 1024.0 AS InternalObjectsMB,
SUM(version_store_reserved_page_count) * 8 / 1024.0 AS VersionStoreMB,
SUM(unallocated_extent_page_count) * 8 / 1024.0 AS FreeSpaceMB
FROM sys.dm_db_file_space_usage;
Это простое, но фундаментальное динамическое административное представление (DMV), поскольку оно показывает:
✔️ UserObjectsMB
Этот показатель представляет собой общий объём дискового пространства (в МБ), занятого объектами, явно созданными пользователями или приложениями в рамках сеанса.
Хотя большинство администраторов баз данных думают только о временных таблицах (#temp) и табличных переменных, здесь есть распространённое упущение: индексы.
Имейте в виду: если разработчик создаёт временную таблицу на 10 МБ, но применяет к ней пять тяжёлых индексов, это число взлетит до небес! 🚀
Если быть более точным: сюда входит базовая куча/кластерный индекс, а также все некластерные индексы и статистика, связанные с этими пользовательскими объектами.
💣 Моё экспертное мнение
Высокое значение здесь — «дымящийся пистолет» для проблем логики приложения или ETL. Это означает, что ваш код либо материализует слишком много данных, либо не удаляет большие временные структуры, что приводит к ненужной нагрузке на диск.
👉 Итак, высокое значение = проблема логики приложения или ETL.
✔️ InternalObjectsMB
Это ваш ключевой показатель. InternalObjectsMB представляет пространство, которое SQL Server занимает потому, что у него закончилось рабочее пространство в памяти (RAM).
Хотя термин «плохие оценки кардинальности» является распространённым, давайте будем точнее: это значение является окончательным сигналом того, что предоставление памяти (Memory Grant), назначенное запросу, было слишком маленьким.
Это называется сценарием «Liar» (Лжец), потому что если SQL Server запрашивает 50 МБ на основе ошибочной оценки, а реальная рабочая нагрузка требует 500 МБ, то огромная разница в 450 МБ не исчезает бесследно — она сбрасывается прямо сюда, в TempDB.
Эта категория включает:
- Сортировки: операции ORDER BY, которые не помещаются в выделенную память.
- Хеш-соединения и агрегаты: большие соединения или операции GROUP BY.
- Spool и рабочие таблицы: промежуточное хранение, используемое во время выполнения запроса.
👉 Если это значение растёт:
Памяти недостаточно: ваш буферный пул испытывает нагрузку.
Плохие оценки кардинальности: оптимизатор ошибается из-за устаревшей статистики.
Неэффективные планы выполнения: движок выбирает дорогостоящие операторы, которые вынуждают сбрасывать данные на диск.
💥 Вот где производительность умирает, потому что вы меняете оперативную память с наносекундной скоростью на дисковый ввод-вывод с миллисекундной скоростью.
⚠️ VersionStoreMB
Если это значение не желает снижаться, ваш поток очистки хранилища версий, вероятно, заблокирован «фантомной» или осиротевшей транзакцией (например, BEGIN TRAN без COMMIT в забытом сеансе). Это классическая причина, почему TempDB достигает 100% заполнения, даже когда нет активной нагрузки запросов.
— Изоляция моментальных снимков (Snapshot Isolation) и RCSI.
👉 Рост = долго выполняющиеся транзакции + отставание очистки.
🚨 FreeSpaceMB
Никогда не забывайте, что автоматический рост (Autogrow) в TempDB — это последовательная операция!
Если у вас 8 файлов данных и одному из них нужно вырасти, вся система испытывает «микрозависание» или остановку на доли секунды. В средах с высоким уровнем параллелизма, где важна каждая миллисекунда, такие остановки убивают производительность.
👉 Мало свободного места = авторасширение → скачки задержек.
🧪 Шаг 2 — Конкуренция (скрытое узкое место)
SELECT
wait_type,
waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH_%'
ORDER BY wait_time_ms DESC;
Это динамическое административное представление показывает PAGELATCH_UP или PAGELATCH_EX.
Когда вы выполняете приведённый выше запрос и видите PAGELATCH_UP или PAGELATCH_EX на вершине ваших ожиданий, вашей первой реакцией может быть обвинение подсистемы хранения. Эй, остановитесь прямо здесь... это НЕ ожидания диска. Если бы у вас была проблема с диском, вы бы видели PAGEIOLATCH (обратите внимание на IO).
Ожидание PAGELATCH происходит полностью в памяти. Мы сталкиваемся с конкуренцией за структуры в памяти.
🔍 Что на самом деле происходит?
Представьте TempDB как скоростную автостраду. Чтобы выехать на скоростную автостраду, вам нужно проехать через пункт взимания платы (это внутренние страницы метаданных, такие как PFS, GAM и SGAM, которые отслеживают, где свободно место).
Если у вас 64 ядра CPU пытаются одновременно втиснуть данные в TempDB, но есть только один «пункт взимания платы» (одна страница метаданных), CPU начинают выстраиваться в очередь. Они не ждут диск; они ждут своей очереди, чтобы обновить карту TempDB в оперативной памяти.
💣 Почему возникает конкуренция:
— Слишком много одновременных выделений: ваша рабочая нагрузка порождает десятки потоков, которые все хотят создать временные таблицы в одну и ту же миллисекунду.
— «Горячие» страницы: несколько сеансов борются за одну и ту же страницу данных в памяти.
— Неправильная конфигурация TempDB: вы не следовали «про-правилу» использования нескольких файлов данных.
💥 «Призрачный» результат:
Это самая неприятная часть конкуренции. Если вы посмотрите на свои инструменты мониторинга, вы увидите:
— Низкий CPU: процессоры простаивают, потому что они застряли в ожидании захвата краткой блокировки (latch).
— Низкая пропускная способность: сервер едва перемещает данные.
— Нет видимой блокировки: вы проверяете sys.dm_exec_requests и не видите стандартной блокировки SQL (LCK_M_...), но всё равно всё еле движется.
💣 Моё экспертное мнение:
Если вы видите PAGELATCH_UP / PAGELATCH_EX, ваша TempDB «упирается в потолок» из-за собственного внутреннего управления. Движок спотыкается сам о себя. Это часто является основной причиной, по которой добавление большей мощности CPU не ускоряет медленный SQL Server.
💣 Если вы видите PAGELATCH_UP / PAGELATCH_EX, это означает конкуренцию в TempDB:
— Слишком много одновременных выделений
— «Горячие» страницы
— Неправильная конфигурация TempDB
💥 Результат:
— Низкий CPU
— Низкая пропускная способность
— Нет видимой блокировки
🧪 Шаг 3 — Сбросы (тихие убийцы)
SELECT TOP 10
qs.total_spills,
qs.execution_count,
qs.total_spills / qs.execution_count AS avg_spills,
qt.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_spills > 0
ORDER BY avg_spills DESC;
Это динамическое административное представление показывает сбросы...
Если InternalObjectsMB — это дым, то сбросы — это огонь. «Сброс» происходит, когда оптимизатор SQL Server оценивает, что определённой операции (например, сортировке или хеш-соединению) требуется определённый объём памяти (Memory Grant). Если эта оценка неверна и данные не помещаются в выделенную RAM, SQL Server не останавливает запрос. Вместо этого он «сбрасывает» избыточные данные в TempDB.
Это «тихий» убийца, потому что вы не получаете ни ошибок, ни предупреждений (если только вы не смотрите на фактический план выполнения в SSMS и не наводите курсор на конкретный оператор, вы не увидите жёлтый треугольник с предупреждением). Вместо этого возникает огромная задержка, потому что вы переходите со скорости света (память) на скорость велосипеда (дисковый ввод-вывод).
👉 Сброс происходит, когда памяти недостаточно и SQL Server выполняет запись в TempDB. Это случается, когда:
— Неверная статистика: оптимизатор думает, что придёт 100 строк, а появляется 1 000 000.
— Плохая оценка кардинальности: неточная математика в плане выполнения приводит к крошечному предоставлению памяти.
— Sniffing параметров: план, оптимизированный для небольшого набора данных, повторно используется для огромного.
— Скошенные данные: высокая вариативность распределения данных сбивает с толку оценки движка.
💥 Нет ошибок. Нет предупреждений. Просто медленные запросы.
💥 Экспертное мнение!
Когда вы выполняете приведённый выше запрос и видите высокое значение avg_spills, вы нашли своего «вампира производительности»!
Перестаньте смотреть на конфигурацию TempDB: если запрос сбрасывает данные, добавление дисков или файлов не решит основную проблему. Вместо этого вам нужно исправить план. Обновите статистику, используйте подсказку плана или перепишите запрос, чтобы избежать больших неиндексированных сортировок.
🧪 Шаг 4 — Кто ПРЯМО СЕЙЧАС использует TempDB
SELECT
session_id,
user_objects_alloc_page_count * 8 / 1024.0 AS UserMB,
internal_objects_alloc_page_count * 8 / 1024.0 AS InternalMB
FROM sys.dm_db_session_space_usage
ORDER BY InternalMB DESC;
Когда сервер «кричит» и вам нужен виновник, это ваш взгляд на активность в реальном времени. Этот запрос изолирует конкретные сеансы, которые в данный момент истощают ресурсы вашей TempDB.
👉 Как интерпретировать данные в реальном времени
— UserMB высок: это конкретный сеанс, выполняющий огромную временную таблицу или табличную переменную. Это проблема логики приложения: вам нужно поговорить с разработчиком о его стратегии промежуточного хранения данных.
— InternalMB высок: это сеанс, страдающий от массового сброса. Он, вероятно, застрял в огромной сортировке или хеш-соединении. Это проблема плана. Добавление дисков НЕ исправит проблему, потому что узкое место не в ёмкости диска.
📢 Заключение
Вам не нужно отслеживать всё: вам нужно отслеживать правильные вещи.
TempDB — это первое место, где SQL Server показывает признаки истощения: если вы можете прочитать эти признаки за 45 секунд, вы уже опережаете 90% администраторов баз данных!
Можно сказать, что TempDB НЕ замедляет SQL Server, она выявляет, что SQL Server уже испытывает трудности.
🚀 Моя работающая стратегия
— Сначала выявите сбросы
— Проанализируйте ФАКТИЧЕСКИЙ план выполнения
— Проверьте кардинальность и статистику
— Проверьте предоставления памяти
👉 TempDB — это следствие... Не причина.
✔️ Никогда не игнорируйте InternalObjectsMB
✔️ Сбросы — это всегда сигнал
✔️ Хранилище версий = проблема транзакций
✔️ PAGELATCH = проблема конкурентного доступа
Комментариев нет:
Отправить комментарий