SQL Server TempDB Contention: Diagnosis and Fix | Server Side Technology Solutions

A client called me because their application response times had doubled after a server migration. SQL Server was the same version. The application code hadn't changed. Same hardware profile. I pulled up a wait stats query and the answer was right there: PAGELATCH_UP on page 1:1:2. Classic tempdb contention. Every session competing for the same allocation page.

They'd migrated to a new server with more CPUs — which made the problem worse, not better. More cores means more concurrent requests means more threads competing for the same pages in tempdb. The old server had been bottlenecked on CPU. The new server removed that bottleneck and exposed the tempdb one hiding underneath.

TempDB contention is one of those problems that punishes you for upgrading your hardware. Here's how to diagnose it and fix it permanently.

What TempDB Contention Actually Is

TempDB is a system database that every session on the instance shares. SQL Server allocates space inside TempDB on special pages called PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map) pages. Every time a session needs to create a temp table, spill a hash join, or store row versions for RCSI, it has to update one of these allocation pages.

On a busy server with a single TempDB data file, hundreds of sessions are serializing writes to the same three page numbers: 1:1:1 (PFS), 1:1:2 (GAM), and 1:1:3 (SGAM). Each thread waits for an exclusive latch before updating the page. This is PAGELATCH_UP and PAGELATCH_EX — latch waits, not lock waits, which is why they often get missed in wait stats reviews that focus only on lock contention.

Step 1: Confirm the Diagnosis

Free · 2 Minutes

How healthy is your database, really?

Get your free database health score — spot risks before they become incidents.

Get my health score

Run this query on the instance you suspect is having contention issues:

-- Current wait stats — looking for PAGELATCH on tempdb allocation pages
SELECT TOP 20
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  max_wait_time_ms,
  signal_wait_time_ms,
  wait_time_ms - signal_wait_time_ms        AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN (
  'PAGELATCH_EX', 'PAGELATCH_UP', 'PAGELATCH_SH',
  'PAGEIOLATCH_EX', 'PAGEIOLATCH_SH'
)
ORDER BY wait_time_ms DESC;

High PAGELATCH_UP or PAGELATCH_EX counts point to allocation contention. To confirm it's specifically TempDB (not a user database), identify which pages are causing the waits:

-- Identify which pages are being waited on — confirms tempdb vs. user db
SELECT
  session_id,
  wait_type,
  wait_duration_ms,
  resource_description,
  -- Parse the database ID from resource_description (format: d:f:p)
  CAST(SUBSTRING(resource_description, 1, CHARINDEX(':', resource_description) - 1) AS INT) AS database_id,
  blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE wait_type LIKE 'PAGELATCH%'
  AND resource_description IS NOT NULL
ORDER BY wait_duration_ms DESC;

TempDB is always database_id = 2. If the database_id column shows 2 and the page numbers are small integers (1, 2, 3), you've confirmed tempdb allocation page contention. Those are your PFS, GAM, and SGAM pages.

Step 2: Check How Many TempDB Data Files You Have

The primary fix is adding more TempDB data files. But first, check what you have:

-- Current TempDB file configuration
SELECT
  file_id,
  name,
  physical_name,
  type_desc,
  state_desc,
  size * 8.0 / 1024                         AS size_mb,
  max_size,
  growth,
  is_percent_growth
FROM tempdb.sys.database_files
ORDER BY type_desc, file_id;

What you're looking for:

Number of data files — if you see 1 or 2, that's almost certainly your problem on a server with 8+ logical processors
File sizes — if the files aren't equal in size, SQL Server won't distribute allocations evenly. All files must be the same size with the same growth settings.
Auto-growth enabled — if growth is set and files aren't equal, proportional fill will skew to the largest file, eliminating the benefit of multiple files

The Fix: Multiple Equal-Sized TempDB Data Files

The rule of thumb from Microsoft is: one data file per logical processor, up to 8 files. On servers with more than 8 logical processors, start at 8 and add more in groups of 4 if contention persists.

Here's the rationale: each data file gets its own PFS/GAM/SGAM allocation pages. Adding files distributes allocation contention across multiple page sets. SQL Server's proportional fill algorithm routes new allocations across files based on free space, spreading the latch waits.

-- Example: Add 3 more TempDB data files to reach 4 total
-- Adjust path to match your TempDB location (same drive, same size as existing file)
ALTER DATABASE [tempdb]
  ADD FILE (NAME = N'tempdev2', FILENAME = N'D:\TempDB\tempdev2.ndf', SIZE = 8192MB, FILEGROWTH = 512MB),
  ADD FILE (NAME = N'tempdev3', FILENAME = N'D:\TempDB\tempdev3.ndf', SIZE = 8192MB, FILEGROWTH = 512MB),
  ADD FILE (NAME = N'tempdev4', FILENAME = N'D:\TempDB\tempdev4.ndf', SIZE = 8192MB, FILEGROWTH = 512MB);

Critical: the new files must match the size of your existing primary TempDB data file. If your existing file is 8GB, all new files must be 8GB. Growth increments should also match. If they don't match, proportional fill favors the larger files and you don't get even distribution.

Trace Flag 1118: Uniform Extents

Pre-SQL Server 2016, there's an additional fix: trace flag 1118. By default, SQL Server uses mixed extents for small allocations — multiple objects share a single 64KB extent. This concentrates allocation activity on SGAM pages. TF 1118 forces uniform extent allocation (one object per extent) which reduces SGAM contention.

-- SQL Server 2014 and earlier: enable at startup in SQL Server Configuration Manager
-- Add -T1118 to startup parameters

-- SQL Server 2016+: MIXED_PAGE_ALLOCATION is a per-database setting
-- For TempDB it defaults to OFF (equivalent to TF1118 being active):
ALTER DATABASE [tempdb] SET MIXED_PAGE_ALLOCATION OFF;

In SQL Server 2016 and later, the MIXED_PAGE_ALLOCATION OFF database setting replaces TF 1118. It's the default for TempDB in new installations, but worth verifying if you've migrated from an older SQL Server version and carried forward the system database configuration.

TempDB Sizing: Pre-Allocate, Don't Auto-Grow

Auto-growth in TempDB causes a secondary problem: when TempDB needs to grow during peak load, every session waiting for space blocks until the growth completes. On busy servers this shows up as PREEMPTIVE_OS_GETPROCADDRESS or just as sessions piling up behind a GROWINGFILE wait.

Pre-size TempDB to cover your expected peak workload:

-- How much TempDB space is currently in use?
SELECT
  SUM(user_object_reserved_page_count) * 8.0 / 1024    AS user_objects_mb,
  SUM(internal_object_reserved_page_count) * 8.0 / 1024 AS internal_objects_mb,
  SUM(version_store_reserved_page_count) * 8.0 / 1024   AS version_store_mb,
  SUM(unallocated_extent_page_count) * 8.0 / 1024       AS free_space_mb,
  SUM(user_object_reserved_page_count
    + internal_object_reserved_page_count
    + version_store_reserved_page_count) * 8.0 / 1024   AS total_used_mb
FROM sys.dm_db_file_space_usage
WHERE database_id = 2;

Run this query at peak load — either capture it from a monitoring baseline or run it during your busiest hour. The total_used_mb is your minimum TempDB pre-allocation target. Add 20-30% headroom and set all data files to that size with auto-growth disabled or set to a large fixed increment as a safety net only.

Version Store Bloat: RCSI's Hidden Cost

If you're running Read Committed Snapshot Isolation (RCSI) — which you should be, to eliminate reader/writer blocking — the version store in TempDB can grow significantly under long-running transactions. A single uncommitted transaction that started 4 hours ago can hold version store entries for every row modified since it began.

-- Check for version store pressure
SELECT
  database_id,
  DB_NAME(database_id)                      AS database_name,
  reserved_page_count * 8.0 / 1024          AS version_store_mb,
  reserved_page_count
FROM sys.dm_db_file_space_usage
WHERE database_id = 2
  AND reserved_page_count > 0;

-- Find the oldest active transaction (version store anchor)
SELECT TOP 5
  t.transaction_id,
  t.transaction_begin_time,
  DATEDIFF(MINUTE, t.transaction_begin_time, GETDATE()) AS duration_minutes,
  s.session_id,
  s.login_name,
  s.program_name,
  r.blocking_session_id,
  r.wait_type,
  SUBSTRING(st.text, (r.statement_start_offset/2)+1,
    ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
      ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS current_statement
FROM sys.dm_tran_active_snapshot_database_transactions t
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) st
ORDER BY t.transaction_begin_time ASC;

A transaction open for 4+ hours with RCSI enabled is a version store anchor. The cleanup process can't reclaim any version store entries older than that transaction's start time. If TempDB is growing steadily and you can't figure out why, this is almost always the cause. Kill the session, monitor TempDB shrink, then find out why that transaction was left open.

After the Fix: Verify the Improvement

After adding TempDB files and pre-sizing, clear the wait stats baseline and check again after a representative period of load:

-- Reset wait stats baseline (do this AFTER the fix, before load)
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

-- After 30-60 minutes of representative load, check PAGELATCH waits again
SELECT
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  CAST(wait_time_ms * 100.0 /
    SUM(wait_time_ms) OVER () AS DECIMAL(5,2))          AS pct_total_wait
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('PAGELATCH_EX', 'PAGELATCH_UP', 'PAGELATCH_SH')
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

A successful fix should drop PAGELATCH_UP and PAGELATCH_EX by 80-90% or more. If contention persists after 8 data files, the problem may be version store growth (long transactions with RCSI), TempDB on a slow drive, or application code creating excessive temp objects in tight loops. Each of those has a different fix — but the file count and sizing issues account for 90% of what I see in the field.

The TempDB Configuration Checklist

Every SQL Server instance I review gets this checklist applied before anything else:

Data files = logical processors, capped at 8 — add more in groups of 4 if contention persists
All data files the same size — equal size, equal growth increment, proportional fill works correctly
MIXED_PAGE_ALLOCATION OFF (SQL Server 2016+) or TF 1118 (2014 and earlier)
Pre-allocated size covers peak usage + 30% — no auto-growth during business hours
TempDB on its own dedicated disk — not sharing I/O with user databases or logs
Long-running transactions monitored — version store bloat alert when TempDB grows beyond baseline

The manufacturing client who called me about slow response times after the server migration? Adding 7 more data files (matching the server's 8 logical processors) and pre-sizing TempDB to cover their peak workload cut their PAGELATCH_UP waits by 94%. Response times dropped back to baseline within an hour of the change going live. No application code changes. No schema changes. Just a properly configured TempDB.

It's one of the highest-ROI fixes in SQL Server performance tuning — and one of the most commonly missed because it's in a system database most DBAs don't look at until something's already on fire.