Part of the DBA-Tools Project.
Temp DB is shared by every database and every session on the instance: temp tables, table variables, sort and hash spills, version store for snapshot isolation, all of it lands in the same handful of files. When Temp DB is under pressure, contention or growth events, the first question is always “who’s actually using it right now,” and that’s not obvious from a single system-wide view.
This script identifies exactly which sessions are consuming the most Temp DB space at the moment it’s run, with what they’re waiting on if anything, so the investigation starts from a specific session instead of a guess.
Why Temp DB Hotspots Matters
A single runaway session (a bad query building a huge worktable, a poorly-scoped batch process, a spill from an underestimated memory grant) can dominate Temp DB and slow down every other session sharing it:
- Temp DB contention (
PAGELATCHwaits on allocation pages) affects every session using it, not just the one causing it - A session holding a large amount of Temp DB space is directly identifiable, rather than inferred from aggregate file growth
- Distinguishing user objects (explicit temp tables/variables) from internal objects (spills, sort/hash work) narrows down whether the cause is application code or a query plan problem
- The
wait_typeandelapsed_seccolumns show whether the top consumer is actively running or just holding space from earlier work
When to Run This Script
- Investigating Temp DB contention or unexpected growth
- Routine SQL Server health checks on Temp DB-heavy workloads
- Diagnosing a sudden slowdown affecting many sessions at once
- Reviewing a server you’ve just inherited, to understand its Temp DB usage pattern
The Script
The full script, with its repo header and the exact commands to run it from the repo, is on GitHub Gist:
It joins sys.dm_db_session_space_usage to session and request DMVs, and returns every session currently holding Temp DB space, ranked by total allocation, split into user objects versus internal objects.
Example Output
To get a real, non-empty result (usage is transient by nature, most sessions release it as soon as they finish), this was captured with a session actively holding a SELECT ... INTO #temp of about half a million rows:
| session_id | login_name | program_name | user_objects_mb | internal_objects_mb | total_tempdb_mb | wait_type |
|---|---|---|---|---|---|---|
| 60 | HPAI01\Peter | Core .Net SqlClient Data Provider | 71.44 | 0.00 | 71.44 |
Understanding the Results
- user_objects_mb — space used by explicit temp tables and table variables created by application or ad-hoc code. High values here usually trace back to specific queries or procedures.
- internal_objects_mb — space used by SQL Server itself for sort/hash spills, worktables, and similar internal work. High values here often mean a query’s memory grant was too small for what it actually needed.
- total_tempdb_mb — the sort key. The session at the top is the one to investigate first.
- wait_type — blank/
NULLmeans the session isn’t actively running a request right now (it may be holding space from a completed statement in an open transaction). A wait type present means it’s actively running and worth checking what it’s waiting on. - An empty result set is normal and expected between busy periods; usage is transient, most sessions release it as soon as they finish.
Best Practices
- When investigating contention, run this repeatedly (or capture a snapshot during the actual slow period) since usage is transient and can be gone by the time you look.
- A consistently high
internal_objects_mbon the same query points at a memory grant sizing problem, worth investigating with the query’s actual execution plan. - Pair this with Temp DB file configuration checks; even a legitimate workload can suffer if it isn’t sized or striped appropriately for the load it carries.
Related Scripts
You may also find these scripts useful:
- Temp Db Configuration
- Temp Db File Balance
- Temp DB Usage
Frequently Asked Questions
Why did the script return nothing when I ran it?
Most likely because nothing was using significant Temp DB space at that exact moment; usage is transient. Run it again during the actual period of contention or slowness, or while the suspect workload is running.
Is internal object usage always a problem?
No. Every complex query that sorts, hashes, or spills uses Temp DB internally as a normal part of execution. It’s only worth investigating when it’s unusually high, sustained, or tied to a specific query causing contention for others.
Summary
Temp DB contention affects everyone sharing the instance, which makes finding the actual source fast a real priority, not just a nice-to-have. Knowing exactly which session is holding the space turns a vague “it’s slow” into a specific, actionable investigation.
Run this script while a suspected issue is actively happening, since usage is transient and the picture changes quickly.
Leave a Reply