В продолжение темы о промежуточной материализации (сбросе) в TempDB

Автор: Luca Biondi, Why SQL Server Starts Spilling to TempDB

Как мы говорили в предыдущей статье: когда загрузка ЦП в порядке, операции ввода-вывода в порядке, а запросы выглядят «нормально», но производительность нестабильна: иногда быстро, иногда медленно – настоящая проблема часто кроется в сбросах в TempDB. В этой второй части мы глубже разберём первопричину!

🧪 Индикатор в реальном времени (использование TempDB)

SELECT 
    session_id,
    internal_objects_alloc_page_count * 8 / 1024.0 AS InternalMB
FROM sys.dm_db_session_space_usage
ORDER BY InternalMB DESC;

Посмотрите на результаты. Если у вас:

👉 Высокое значение InternalMB = высока вероятность сбросов.

🚀 Моя работающая стратегия

НЕ нужно «оптимизировать TempDB».

👉 Идите прямо к причине:

• ✔️ Определите запросы, вызывающие сбросы
• ✔️ Проанализируйте фактический план выполнения
• ✔️ Сравните оценочное и фактическое количество строк
• ✔️ Проверьте распределение памяти

💣 Ключевое соображение:

TempDB – это следствие, а не причина.

Если вы исправите план – сбросы исчезнут.

📢 Заключение. Как действовать дальше

Чтобы было понятно:

Сбросы – не редкость.
Они повсюду.
...и большинство людей их никогда не замечают.

Это происходит потому, что они смотрят на ЦП, ввод-вывод и планы, вместо поведения во время выполнения. Но если ваши запросы нестабильны – проверяйте сбросы.

Потому что когда у SQL Server заканчивается память… TempDB сообщит вам об этом первой 😉

---

💬 Комментарий ИИ (в дополнение к статье)

Какое значение можно считать высоким?

Однозначного порога «высокого значения» InternalMB, который подходил бы для всех систем без исключения, не существует. Это значение сильно зависит от объема доступной серверу оперативной памяти (RAM), общей загруженности TempDB и характера выполняющихся запросов.

Однако на практике можно использовать следующие ориентиры, чтобы отличить типичную активность от потенциальной проблемы.

Диапазоны значений InternalMB и их интерпретация

• 0 – 50 МБ: Нормальная активность. Небольшие объемы памяти для сортировки или хеширования — это нормально для большинства рабочих нагрузок. Наблюдение не требуется.
• 50 – 500 МБ: Повышенная активность / Требует внимания. Значение выходит за рамки типичных фоновых операций. Это может указывать на неоптимальные запросы или временные всплески. Стоит проверить запросы, генерирующие такое потребление.
• > 500 МБ – 1 ГБ: Высокая / Потенциально критическая. Очень вероятны сбросы (spills) из-за нехватки памяти. Это создает значительную нагрузку на диск и замедляет работу. Требует немедленного анализа.
• > 1 ГБ (и более): Критическая / Аварийная ситуация. Запросы потребляют огромные объемы TempDB. Это может привести к нехватке места на диске и сильной деградации производительности всего сервера. Действуйте незамедлительно.

Важные уточнения

• Это значение не является «средним»: internal_objects_alloc_page_count показывает общее количество страниц, выделенных с момента начала сессии. Если у вас есть долгоживущее соединение, которое выполняет тяжелые запросы раз в час, InternalMB будет накапливаться и показывать огромную цифру, даже если в данный момент сессия неактивна. Чтобы увидеть картину «здесь и сейчас», нужно отслеживать разницу между alloc и dealloc, либо смотреть на представление sys.dm_db_task_space_usage, которое отражает текущие, выполняющиеся задачи.
• Учитывайте ресурсы сервера: На сервере с 64 ГБ ОЗУ потребление в 1 ГБ может быть заметной, но не катастрофической проблемой. На сервере с 8 ГБ ОЗУ — это почти наверняка критический сброс.
• Всегда проверяйте контекст: Обнаружив высокое значение, следующий шаг — найти сам запрос. Высокое значение InternalMB — это симптом, а первопричина кроется в неоптимальном плане запроса, нехватке памяти или устаревшей статистике.

Как получить более точную картину
Вы можете модифицировать свой запрос, чтобы учитывать только текущие операции, и добавить сортировку для выявления самых «прожорливых» сессий:

SELECT 
    session_id,
    CAST ((internal_objects_alloc_page_count - internal_objects_dealloc_page_count) * 8 / 1024.0 AS bigint) AS CurrentInternalMB
FROM sys.dm_db_session_space_usage
WHERE (internal_objects_alloc_page_count - internal_objects_dealloc_page_count) > 0
ORDER BY CurrentInternalMB DESC;