SQL Server TempDB Internals: Version Store, Spills and Optimization (2026)

What Uses TempDB

TempDB is a shared resource consumed by every database on the instance simultaneously. Its users fall into three categories:

User Workload Objects

• Local temp tables (#table) — visible only to the creating session
• Global temp tables (##table) — visible to all sessions
• Table variables (@table) — scoped to the batch, stored in TempDB when they grow beyond a threshold
• Temp stored procedures (#proc)
• DBCC work tables

Internal Query Processor Objects

• Sort spill files — when an ORDER BY or GROUP BY exceeds its memory grant
• Hash join / hash aggregate spill files — when a hash build side exceeds memory
• Work tables for cursors and spool operators
• Online index rebuild work space
• LOB (Large Object) intermediate storage

Version Store

• Row versions for Read Committed Snapshot Isolation (RCSI)
• Row versions for Snapshot Isolation (SI)
• Row versions for online index operations
• Row versions for triggers (the inserted/deleted pseudo-tables)
• Row versions for Multiple Active Result Sets (MARS)

TempDB Architecture: One Database, Many Files

TempDB is a single SQL Server database recreated from scratch every time the instance starts — it does not survive restarts. All temp objects, version store data, and spill files are lost on restart.

By default, SQL Server 2016 Setup configures TempDB with one data file per logical CPU (up to 8). Earlier versions default to a single data file. The number of files matters because of how space allocation works.

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

-- Current TempDB space usage
SELECT
    SUM(unallocated_extent_page_count) * 8 / 1024   AS free_mb,
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_mb,
    SUM(user_object_reserved_page_count) * 8 / 1024   AS user_objects_mb,
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS internal_objects_mb,
    SUM(mixed_extent_page_count) * 8 / 1024           AS mixed_extents_mb
FROM sys.dm_db_file_space_usage;

PFS, GAM and SGAM Pages: Allocation Metadata

To understand TempDB contention you need to understand three special page types that SQL Server uses to track free space:

• PFS (Page Free Space) — one byte per data page, recording what percentage of the page is used (empty, 1–50%, 51–80%, 81–95%, 96–100%). One PFS page covers the 8,088 pages following it. SQL Server updates PFS on every allocation and deallocation.
• GAM (Global Allocation Map) — one bit per extent (8 pages). Bit=1 means the extent is free. One GAM page covers about 64,000 extents (4 GB of data). Read when allocating uniform extents.
• SGAM (Shared Global Allocation Map) — one bit per extent. Bit=1 means the extent is a mixed extent with at least one free page. Used for single-page allocations (new small objects).

When a session allocates a page in TempDB, SQL Server must update the PFS byte for that page under a latch. Only one thread can update a given PFS page at a time. On a busy server with hundreds of concurrent sessions all creating temp tables simultaneously, this single-page bottleneck becomes the limiting factor.

Allocation Contention: PAGELATCH_UP

PAGELATCH_UP on specific TempDB page IDs is the signature of allocation contention. The wait resource looks like:

2:1:1     -- database_id=2 (tempdb), file_id=1, page_id=1 (PFS page)
2:1:2     -- GAM page
2:1:3     -- SGAM page

-- Check if PAGELATCH_UP is the problem and which pages are hot
SELECT
    r.session_id,
    r.wait_type,
    r.wait_resource,
    r.wait_time / 1000.0 AS wait_s
FROM sys.dm_exec_requests r
WHERE r.wait_type = 'PAGELATCH_UP'
  AND r.wait_resource LIKE '2:%'  -- tempdb
ORDER BY r.wait_time DESC;

-- Aggregate: how much total PAGELATCH_UP time is there?
SELECT wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms / NULLIF(waiting_tasks_count,0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'PAGELATCH_UP';

If you see dozens of sessions waiting on pages 1, 2, or 3 of TempDB file 1, allocation contention is your problem. The fix is more data files.

Fixing Allocation Contention: Multiple Data Files

Adding TempDB data files distributes allocation activity across multiple sets of PFS/GAM/SGAM pages. Each file has its own page 1 (PFS), page 2 (GAM), page 3 (SGAM). With 8 files, the contention is divided 8 ways.

Rules of thumb:

• Start with one data file per logical CPU core, up to a maximum of 8
• If you have more than 8 logical cores, add files in multiples of 4 until contention resolves
• All data files should be equally sized with equal autogrowth settings — SQL Server uses proportional fill across files of equal size
• Add the files to the same filegroup (PRIMARY in TempDB)

-- Add TempDB data files (adjust paths and sizes for your environment)
-- Run in the context of the master database (TempDB cannot ALTER itself)
USE master;

ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev2', FILENAME = 'D:\TempDB\tempdev2.ndf',
          SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev3', FILENAME = 'D:\TempDB\tempdev3.ndf',
          SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev4', FILENAME = 'D:\TempDB\tempdev4.ndf',
          SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb
ADD FILE (NAME = 'tempdev5', FILENAME = 'D:\TempDB\tempdev5.ndf',
          SIZE = 4096MB, FILEGROWTH = 512MB);

-- Make all files equal size (resize the original tempdev to match if needed)
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', SIZE = 4096MB, FILEGROWTH = 512MB);

Version Store: How Snapshot Isolation Uses TempDB

When RCSI or Snapshot Isolation is enabled, SQL Server stores the before-image of every modified row in TempDB's version store. Readers access these versions to get a consistent snapshot without taking shared locks.

The version store is a chain of version records linked backward through time:

Current row (in data page) → Version N (TempDB) → Version N-1 (TempDB) → ...
                              ↑ timestamp: 10:00:05   ↑ timestamp: 10:00:01

Each version record contains:

• The transaction sequence number (XSN) of the transaction that created it
• A pointer to the previous version
• The actual before-image data of the modified columns

Versions are cleaned up by a background cleanup task when no active transaction needs them anymore (i.e., when the oldest active snapshot transaction has a sequence number higher than the version's XSN). A long-running snapshot transaction blocks version cleanup and causes TempDB to grow.

Monitoring and Controlling Version Store Size

-- Version store current size and cleanup rate
SELECT
    SUM(version_store_reserved_page_count) * 8 / 1024   AS version_store_mb,
    SUM(version_store_reserved_page_count)               AS version_store_pages
FROM sys.dm_db_file_space_usage;

-- Active snapshot transactions (what's keeping the version store alive)
SELECT
    transaction_id,
    transaction_sequence_num,
    elapsed_time_seconds,
    is_snapshot,
    is_user_transaction,
    session_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;

-- Version store space per database
SELECT
    DB_NAME(database_id)                            AS db_name,
    SUM(reserved_page_count) * 8 / 1024             AS version_store_mb,
    SUM(reserved_page_count)                         AS pages
FROM sys.dm_tran_version_store_space_usage
GROUP BY database_id
ORDER BY version_store_mb DESC;

If the version store is growing uncontrollably, the first step is to find the oldest active snapshot transaction (highest elapsed_time_seconds in the query above) and determine why it has been open so long. A forgotten BEGIN TRAN with snapshot isolation in an application connection pool is a common culprit.

Sort and Hash Spills: Why They Happen

When SQL Server compiles a query with a sort (ORDER BY, DISTINCT, GROUP BY) or hash operation (hash join, hash aggregate), it requests a memory grant from the workspace memory pool. The grant is calculated from the optimizer's cardinality estimate.

If the estimate is too low — because statistics are stale, parameters were sniffed badly, or the data distribution is unusual — the query receives a grant smaller than the actual data it needs to process. Mid-execution, when the in-memory workspace fills up, SQL Server spills the overflow to TempDB.

A sort spill writes sorted runs to TempDB and merges them. A hash spill writes the build-side hash table to TempDB when it exceeds memory, then re-reads it during the probe phase. Both add significant I/O latency proportional to spill size.

Detecting Spills in Execution Plans and XE

In SSMS actual execution plan: Sort and Hash Match operators show a yellow warning triangle when they spilled. Hover over the operator to see "Number of rows written to tempdb" and spill level (level 1 = single spill, level 2+ = multiple merge passes).

Via Extended Events (continuous monitoring):

CREATE EVENT SESSION [SpillDetection] ON SERVER
ADD EVENT sqlserver.sort_warning(
    ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.query_hash)
),
ADD EVENT sqlserver.hash_warning(
    ACTION(sqlserver.sql_text, sqlserver.database_name, sqlserver.query_hash)
)
ADD TARGET package0.ring_buffer(SET max_memory = 51200)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

ALTER EVENT SESSION [SpillDetection] ON SERVER STATE = START;

-- Query spill events from ring buffer
SELECT
    xdr.value('@timestamp', 'datetime2(3)')                   AS spill_time,
    xdr.value('(action[@name="sql_text"]/value)[1]','nvarchar(max)') AS sql_text,
    xdr.value('(action[@name="database_name"]/value)[1]','nvarchar(100)') AS db_name,
    xdr.value('@name', 'varchar(50)')                          AS event_type
FROM (
    SELECT CAST(target_data AS XML) AS xml_data
    FROM sys.dm_xe_session_targets t
    JOIN sys.dm_xe_sessions s ON t.event_session_address = s.address
    WHERE s.name = 'SpillDetection' AND t.target_name = 'ring_buffer'
) d
CROSS APPLY xml_data.nodes('//RingBufferTarget/event') AS e(xdr)
ORDER BY spill_time DESC;

Via Query Store (SQL 2019+):

SELECT
    qt.query_sql_text,
    rs.avg_num_physical_io_reads,
    rs.avg_tempdb_space_used,
    rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan p ON rs.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
WHERE rs.avg_tempdb_space_used > 1000  -- pages
ORDER BY rs.avg_tempdb_space_used DESC;

Fixing Spills

Root cause 1: Stale statistics → bad cardinality estimate → insufficient memory grant

-- Update statistics on the table(s) involved in the spilling query
UPDATE STATISTICS dbo.LargeTable WITH FULLSCAN;

Root cause 2: Parameter sniffing → compiled for small row count

-- Force recompile so memory grant matches current data
SELECT ... OPTION (RECOMPILE);

Root cause 3: Correct estimate, but the query genuinely needs more memory

-- SQL Server 2012+: hint a minimum memory grant (percentage of max server memory)
SELECT ... OPTION (MIN_GRANT_PERCENT = 5);

-- SQL Server 2019+: cap the maximum grant to prevent monopolizing memory
SELECT ... OPTION (MAX_GRANT_PERCENT = 25);

Root cause 4: Sort on a very large result set

• Add a covering index whose key order matches the ORDER BY — the optimizer can use an index scan in order and eliminate the sort operator entirely
• Use pagination (OFFSET ... FETCH) to process smaller subsets

Temp Tables vs Table Variables vs CTEs

TempDB File Placement and Sizing

Placement:

• Dedicated fast storage — NVMe SSD preferred; not shared with user database files
• Separate volume from the transaction log (different I/O patterns)
• On VMs: local NVMe ephemeral disk is ideal (TempDB is recreated on restart anyway)

Sizing:

• Pre-size TempDB data files to avoid autogrowth during business hours — autogrowth on TempDB freezes all TempDB allocations while the file extends
• A reasonable starting point: observe peak sys.dm_db_file_space_usage.user_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count over a full business day, multiply by 1.5, pre-size to that
• Set autogrowth to a fixed MB value (e.g., 512 MB), not a percentage

-- Change autogrowth to fixed size on all TempDB files
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev',  FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev2', FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev3', FILEGROWTH = 512MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev4', FILEGROWTH = 512MB);

-- Ensure all files are the same size (required for proportional fill to balance I/O)
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev',  SIZE = 4096MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev2', SIZE = 4096MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev3', SIZE = 4096MB);
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev4', SIZE = 4096MB);

SQL Server 2019: TempDB Metadata Optimization

SQL Server 2019 introduced TempDB Metadata Optimization, which moves system table data for TempDB objects (sysschobjs, syscolpars, sysidxstats, etc.) into memory-optimized tables. This eliminates the latch contention on system table pages that was a secondary source of TempDB contention on busy OLTP systems.

-- Enable TempDB metadata optimization (SQL Server 2019+)
ALTER SERVER CONFIGURATION SET TEMPDB METADATA MEMORY_OPTIMIZED = ON;

-- Verify (requires restart to take effect)
SELECT SERVERPROPERTY('IsTempDbMetadataMemoryOptimized');

After enabling this setting and restarting, system catalog page latches on TempDB objects are replaced by lock-free memory-optimized table operations. On highly concurrent OLTP workloads with frequent temp object creation, this can reduce TempDB-related waits by 30–80%.

TempDB Optimization Checklist

1. Multiple data files — equal count to logical CPU cores (up to 8), all equal size, equal autogrowth

   -- Verify: all files same size?
   SELECT name, size * 8 / 1024 AS size_mb FROM tempdb.sys.database_files WHERE type = 0;

2. Pre-sized files — no autogrowth during peak hours. Pre-size based on observed peak usage
3. Dedicated fast storage — NVMe or SSD, separate volume from user database files
4. Check PAGELATCH_UP waits — if present on TempDB pages, add more data files

   SELECT wait_type, waiting_tasks_count, wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type = 'PAGELATCH_UP';

5. Monitor version store growth — find and close long-running snapshot transactions

   SELECT TOP 5 elapsed_time_seconds, session_id FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC;

6. Fix spilling queries — update statistics, fix parameter sniffing, add covering indexes
7. Enable TempDB Metadata Optimization (SQL 2019+) if OLTP workload creates many temp objects
8. Enable Trace Flag 1118 (pre-SQL 2016 only) — forces uniform extent allocation, reduces SGAM contention
9. Review table variables — replace with temp tables for datasets > a few hundred rows where cardinality matters
10. Set IFI (Instant File Initialization) — allows TempDB autogrowth events to complete instantly rather than zeroing the new space

    -- Check if IFI is enabled (SQL Server 2016+)
    SELECT instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server%';

Read Next

• Wait Statistics: sys.dm_os_wait_stats
• Blocking and Deadlocks: Detection and Resolution
• Storage Engine: Data Pages, Extents and Heaps
• Transaction Log Internals: WAL and Checkpoints
• All SQL Server Articles