TempDB Performance Crisis: Diagnosis and Solutions

Why TempDB Matters

TempDB is SQL Server's scratch space. It stores:

Temporary tables (#temp, ##global temp)
Table variables (@table)
Sorting and hashing operations (ORDER BY, GROUP BY, hash joins)
Spills from memory (when working sets exceed memory grants)
Row versioning (for snapshot isolation, online index operations)

If TempDB is slow or contended, everything slows down. Queries that use temp tables, sorts, or hash joins all back up waiting for TempDB.

Unlike user databases, you can't tune TempDB queries because you don't control how SQL Server uses it. Your only option is to configure TempDB properly and fix contention at the storage level.

Common Symptoms of TempDB Problems

Global slowdown: All queries are slow, but user database queries appear fine when run in isolation
Disk I/O spike: TempDB disks show high utilization, while application disks are idle
Waits on PAGELATCH_EX (exclusive latch): Sessions waiting on TempDB allocation pages
High CXPACKET waits: Queries spilling to TempDB instead of computing in memory
Runaway TempDB growth: TempDB data file grows rapidly and doesn't shrink
Allocation page contention (GAM, SGAM, PFS): Multiple threads contending for the same allocation pages
TempDB database offline: Server crash or restart, TempDB fails to start (corrupted)

Diagnosing TempDB Contention

Check Wait Statistics

-- Which waits are the biggest problem?
SELECT TOP 20
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS NUMERIC(5,2)) AS pct_wait
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'QDS_CLEANUP_STALE_QUERIES',
    'BROKER_EVENTHANDLER', 'CLR_AUTO_EVENT'
)
ORDER BY wait_time_ms DESC;

-- Look for:
-- PAGELATCH_EX, PAGELATCH_SH on TempDB
-- WRITELOG on TempDB log file
-- CXPACKET (queries spilling)

Check TempDB Space Usage

-- How much TempDB is being used?
SELECT
    CAST(SUM(user_object_reserved_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS user_objects_GB,
    CAST(SUM(internal_object_reserved_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS internal_objects_GB,
    CAST(SUM(version_store_reserved_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS version_store_GB,
    CAST(SUM(mixed_extent_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS mixed_extent_GB
FROM sys.dm_db_session_space_usage;

-- Which sessions are using the most?
SELECT TOP 20
    session_id,
    login_name,
    host_name,
    CAST(user_objects_alloc_page_count * 8 / 1024.0 AS NUMERIC(10,2)) AS user_objects_MB,
    CAST(internal_objects_alloc_page_count * 8 / 1024.0 AS NUMERIC(10,2)) AS internal_objects_MB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count DESC;

Check TempDB File Configuration

-- How many files does TempDB have? Are they equally sized?
SELECT
    file_id,
    name,
    physical_name,
    CAST(size * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS size_GB,
    CAST(max_size * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS max_size_GB
FROM tempdb.sys.database_files
ORDER BY file_id;

Look for: Only 1 file (contention bottleneck), unequal sizes (load imbalance), or max_size limited.

Check for Allocation Page Contention

-- GAM, SGAM, PFS page contention
SELECT
    session_id,
    wait_type,
    wait_duration_ms,
    blocking_session_id,
    last_wait_type
FROM sys.dm_exec_requests
WHERE database_id = 2  -- TempDB = database_id 2
AND wait_type LIKE 'PAGELATCH%';

-- More detailed: sys.dm_os_latch_stats
SELECT
    latch_class,
    waiting_requests_count,
    wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS NUMERIC(5,2)) AS pct_wait
FROM sys.dm_os_latch_stats
WHERE latch_class LIKE '%temp%'
ORDER BY wait_time_ms DESC;

Root Causes and Fixes

Root Cause 1: Single TempDB File

If TempDB has only 1 data file, all allocation contention goes to that one file's GAM/SGAM pages.

Diagnosis

Check file count: Only 1 file = problem.

Fix: Add TempDB Files

-- Add multiple data files (rule of thumb: 1 per 4 CPU cores, up to 8-10)
-- For an 8-core server, add 2 files (total 3)

USE master;
GO

ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev2,
    FILENAME = 'D:\SQLData\tempdb_2.ndf',
    SIZE = 10 GB,
    FILEGROWTH = 100 MB
);

ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev3,
    FILENAME = 'E:\SQLData\tempdb_3.ndf',
    SIZE = 10 GB,
    FILEGROWTH = 100 MB
);

-- Requires restart to take effect
-- Restart SQL Server

Ideal configuration: One file per 4 CPU cores (max 8 files).

Root Cause 2: TempDB Files on Slow Storage

TempDB is I/O-intensive. If it's on slow SSD or shared storage with other databases, contention is inevitable.

Fix: Dedicated Fast Storage

TempDB files on separate NVMe SSD (not shared with user databases)
TempDB log file on same fast storage
Use high-performance storage pool (don't mix with archive data)

Root Cause 3: Unequal File Sizes

If TempDB files are different sizes, SQL Server's proportional fill algorithm allocates more to larger files, causing imbalance.

Fix: Equalize File Sizes

-- Check current sizes
SELECT file_id, name, CAST(size * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS size_GB
FROM tempdb.sys.database_files
WHERE type = 0;  -- Data files

-- Shrink larger files to match smallest
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 50 GB);
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev2, SIZE = 50 GB);
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev3, SIZE = 50 GB);

-- Or shrink all and grow equally
USE master;
DBCC SHRINKFILE(tempdev, 50000);  -- MB
DBCC SHRINKFILE(tempdev2, 50000);
DBCC SHRINKFILE(tempdev3, 50000);

Root Cause 4: Memory Pressure (Spills)

If queries spill to TempDB (sort/hash operations exceeding memory grants), TempDB usage skyrockets.

Diagnosis

-- Check for spill events
SELECT
    query_hash,
    COUNT(*) AS spill_count,
    SUM(total_spilled_rows) AS total_spilled_rows
FROM sys.dm_exec_query_stats
WHERE total_spilled_rows > 0
GROUP BY query_hash
ORDER BY total_spilled_rows DESC;

Fix: Increase Memory or Optimize Queries

Increase SQL Server max server memory: More buffer pool = fewer spills
Add indexes: Reduce need for large sorts
Optimize queries: GROUP BY, ORDER BY on indexed columns
Check memory grants: Are they set to max with MAXDOP hints?

Root Cause 5: Large Temporary Tables

Queries creating large #temp tables unnecessarily.

Fix: Reduce Temp Table Usage

Use table variables for small working sets (<100 MB)
Use CTEs instead of temp tables when possible
Add indexes to temp tables if joining them
Drop temp tables immediately after use

Root Cause 6: TempDB Log File Issues

TempDB log file is on slow storage or too small, causing log write delays.

Fix: Optimize TempDB Log

-- Check log file size
SELECT file_id, name, CAST(size * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS size_GB
FROM tempdb.sys.database_files
WHERE type = 1;  -- Log file

-- Grow log file
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 10 GB);

-- Put log on fast storage (NVMe, separate from data files)

Comprehensive TempDB Configuration

Best Practice Setup

-- For an 8-core, CPU-bound server

-- 1. Stop SQL Server
-- 2. Move existing TempDB files to new locations
--    D:\ = NVMe for data files (tempdev, tempdev2, tempdev3)
--    E:\ = NVMe for log file (templog)

-- 3. Rebuild TempDB with optimal configuration
USE master;
GO

-- Add 3 equally-sized data files (8 cores / 4 = 2, plus 1 = 3)
ALTER DATABASE tempdb
REMOVE FILE tempdev2;  -- Remove existing if present
GO
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = tempdev,
    FILENAME = 'D:\SQLData\tempdev.ndf',
    SIZE = 50 GB,
    FILEGROWTH = 100 MB
);

ALTER DATABASE tempdb
MODIFY FILE (
    NAME = templog,
    FILENAME = 'E:\SQLData\templog.ldf',
    SIZE = 10 GB,
    FILEGROWTH = 100 MB
);

ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev2,
    FILENAME = 'D:\SQLData\tempdev2.ndf',
    SIZE = 50 GB,
    FILEGROWTH = 100 MB
);

ALTER DATABASE tempdb
ADD FILE (
    NAME = tempdev3,
    FILENAME = 'D:\SQLData\tempdev3.ndf',
    SIZE = 50 GB,
    FILEGROWTH = 100 MB
);

-- 4. Restart SQL Server
-- 5. Verify
SELECT file_id, name, CAST(size * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS size_GB
FROM tempdb.sys.database_files;

Monitor TempDB Health

Create a regular monitoring job:

-- Check daily:
-- 1. Total TempDB space usage
-- 2. Largest sessions using TempDB
-- 3. Wait stats (especially PAGELATCH*)
-- 4. File sizes (equal distribution)
-- 5. TempDB growth rate (healthy = minimal growth after initial setup)

Real-World Example: The Mystery Slowdown

A production server suddenly started performing poorly. All queries were slow, but no single query was obviously bad.

Diagnosis

-- Investigation showed:
-- - TempDB wait stats: 85% of waits were PAGELATCH_EX on TempDB
-- - TempDB had only 1 file (default)
-- - Server had 16 CPU cores
-- - File was 100 GB and on shared SAN with other databases

Root Cause

A new analytics job started creating large temporary sorting operations. All threads contended on the single TempDB file's GAM/SGAM allocation pages. The shared SAN couldn't handle the I/O spike.

Solution

Added 4 additional TempDB files (total 5) on dedicated NVMe storage
Restarted SQL Server (2 minutes downtime)
Wait times dropped 95%
Query performance returned to normal

Total fix time: 30 minutes. Total cost: One NVMe SSD ($200). Impact: Production restored.

TempDB Troubleshooting Checklist

[ ] Check wait stats — is PAGELATCH* the top wait?
[ ] Measure TempDB space usage — how much is actually used?
[ ] Count TempDB files — do you have 1 per 4 CPU cores?
[ ] Verify file sizes — are they equal?
[ ] Check storage performance — TempDB on dedicated fast storage?
[ ] Monitor spill events — are queries spilling to TempDB?
[ ] Review memory grants — are they appropriate?
[ ] Optimize temp table usage — necessary or can you use CTEs?
[ ] Check log file size — adequate for write volume?
[ ] Profile top TempDB-consuming queries — can they be optimized?
[ ] Monitor fragmentation — do you need to rebuild indexes?
[ ] Plan for growth — will TempDB size stay acceptable?

The Verdict

TempDB is not optional. Invest in proper configuration from the start: dedicated storage, multiple files, adequate sizing. When TempDB is optimized, you don't think about it. When it's not, it affects every query on the server.

Remember:

One file per 4 CPU cores (up to 8)
Dedicated fast storage (NVMe)
All files equal size
Monitor regularly
Fix spills early (they cascade)

TempDB configuration is one of the highest-impact optimizations you can make. Spend an hour getting it right, and you'll save hundreds of hours debugging performance issues later.

← Back to Blog