Welcome back. tempdb is one of the most important databases in SQL Server—and one of the most misunderstood.
Many production slowdowns are not caused by “the application database” at all.
They are caused by tempdb pressure: contention, spills, excessive temp object creation, or storage latency.
In this lesson, you will learn what tempdb is for, why it is shared, why it gets hammered under load,
and how DBAs identify the most common bottlenecks using evidence (not guessing).
Lesson description: You will learn what tempdb is (a shared system database recreated on startup),
the real workloads that use it (sort/hash spills, temp tables, row versioning, index operations, triggers, and internal worktables),
and the most common bottlenecks (allocation contention, latch waits, file configuration mistakes, storage latency).
You will also run safe diagnostic queries to check tempdb configuration, size, file count, and active usage patterns.
tempdb is a system database used as SQL Server’s shared workspace for temporary data and internal operations.
Every database and every user session can use tempdb. It is not “per application.”
Critical fact: tempdb is recreated every time SQL Server starts.
That means:
DBA mindset: tempdb is a shared performance resource like CPU and memory.
When it becomes a hotspot, everything can slow down.
tempdb is used by many features and query operators. The most common:
If a query needs to sort or hash a large dataset and doesn’t have enough memory grant,
SQL Server spills intermediate results to tempdb. That adds tempdb writes and reads.
#Temp##Temp@t (still uses tempdb for storage in many cases)Heavy use of temp tables, especially under concurrency, can create allocation contention in tempdb.
Features such as Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation
store row versions in tempdb. This reduces blocking in many workloads, but increases tempdb usage.
Other features that use row versioning include:
SQL Server sometimes creates internal worktables for query processing (spools, intermediate results).
These can end up in tempdb even when your query doesn’t explicitly create temp tables.
Some index rebuilds, sorts, and operations can use tempdb depending on options and execution behavior.
This is why DBAs schedule maintenance intelligently and size tempdb appropriately.
tempdb contention often happens at the allocation metadata level.
Many sessions concurrently create/drop temp objects, causing heavy access to allocation pages.
Historically, the biggest pain points were latch contention on allocation bitmaps such as:
PFS, GAM, and SGAM pages.
When this occurs, symptoms include:
DBA note: SQL Server versions have added improvements to reduce tempdb allocation contention,
but poor configuration or heavy temp workloads can still cause problems.
tempdb is frequently written to and read from during spills and temp operations.
If tempdb storage is slow, the impact can be severe.
Symptoms of tempdb I/O bottlenecks:
sys.dm_io_virtual_file_statsDBA best practice: Put tempdb on fast storage and configure it properly.
tempdb is often more performance-sensitive than user database data files.
Some tempdb performance problems are self-inflicted by configuration:
DBA rule: tempdb data files should usually be equal size and have equal growth settings.
This helps SQL Server spread allocations more evenly.
These are common, widely-used best practices. Your goal is not to memorize numbers,
but to understand the reasoning behind them.
For systems with concurrency pressure, DBAs often add multiple tempdb data files to reduce allocation contention.
The guiding rule is:
Many DBAs start with 4 or 8 files depending on CPU core count and workload,
but the correct answer is always: measure and adjust.
tempdb should not be constantly growing during normal workload.
Growth events cause overhead and can contribute to storage fragmentation.
Equal sizes help avoid one file becoming “hot” while others sit idle.
tempdb is used for short-lived, high-volume operations. Fast storage reduces latency impact.
These queries help you inspect tempdb file layout and settings.
Run them exactly as written.
SELECT
DB_NAME(mf.database_id) AS DatabaseName,
mf.file_id,
mf.name AS LogicalFileName,
mf.type_desc,
mf.size * 8 / 1024 AS SizeMB,
mf.growth,
mf.is_percent_growth,
mf.physical_name
FROM sys.master_files AS mf
WHERE mf.database_id = DB_ID('tempdb')
ORDER BY mf.type_desc, mf.file_id;
Interpretation:
ROWS files = multiple tempdb data filesDBCC SQLPERF(LOGSPACE);
This lets you see if tempdb files are experiencing significant I/O stalls.
SELECT
DB_NAME(vfs.database_id) AS DatabaseName,
mf.name AS LogicalFileName,
mf.type_desc,
mf.physical_name,
vfs.num_of_reads,
vfs.num_of_writes,
vfs.io_stall_read_ms,
vfs.io_stall_write_ms,
CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_stall_ms,
CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_stall_ms
FROM sys.dm_io_virtual_file_stats(DB_ID('tempdb'), NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY (vfs.io_stall_read_ms + vfs.io_stall_write_ms) DESC;
DBA note: “High stall” is not a single magic number. You compare across drives and workloads.
But consistently high average stall times can indicate storage or saturation problems.
A high-throughput OLTP application that creates and drops temp tables constantly can generate heavy tempdb allocation work.
DBAs may optimize by:
Poor indexing or poor estimates can force expensive sorts/hashes that overflow to tempdb.
Fixing the query/index/stats often reduces tempdb usage dramatically.
If snapshot-based isolation is enabled, long-running transactions can keep row versions alive longer,
increasing tempdb load. DBAs monitor long-running transactions and cleanup pressure.
C:\DBA\Logs\section6-lesson6-tempdb-files.txtDBCC SQLPERF(LOGSPACE) and save output to:C:\DBA\Logs\section6-lesson6-logspace.txtC:\DBA\Logs\section6-lesson6-tempdb-io-stalls.txtNext, you will learn what each system database is responsible for, what is safe to change, what is dangerous,
and the most common mistakes that cause instance-wide outages.
Not a member yet? Register now
Are you a member? Login now