Tempdb Architecture, Metadata, Files, Allocation Contention, and Version Store

Learning outcomes

ServiceHub’s overnight reports begin failing with “could not allocate space in tempdb,” while another dashboard shows plenty of free space in the primary application database. A team member proposes adding sixteen tempdb files because “that is the rule.” Another wants to shrink tempdb immediately. Both reactions skip the first question: what is actually consuming or contending on the shared workspace database?

tempdb is a system database recreated whenever the Database Engine starts. SQL Server uses it for explicit temporary objects, internal query-processing structures, and row-versioning infrastructure. Because all user databases on an instance can compete for the same resource, a local symptom can have an instance-wide cause.

1. Start with the instance and file reality

The first operational distinction is capacity versus contention. Capacity asks whether data/log files and the underlying volume can satisfy allocations. Contention asks whether concurrent sessions are waiting to update shared allocation or metadata structures even when free space exists. The remedies differ: more disk fixes neither a hot PFS latch nor a bad query plan, and more files do not create storage capacity by themselves.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,       SERVERPROPERTY('ProductUpdateLevel') AS update_level,       SERVERPROPERTY('Edition') AS edition,       SERVERPROPERTY('EngineEdition') AS engine_edition,       SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS tempdb_metadata_memory_optimized;GOUSE tempdb;GOSELECT DB_NAME() AS database_name, recovery_model_desc,       is_read_committed_snapshot_on, is_accelerated_database_recovery_onFROM sys.databases WHERE database_id = 2;SELECT file_id,name,type_desc,size*8.0/1024 AS size_mb,       growth,is_percent_growth,physical_nameFROM sys.database_filesORDER BY type,file_id;GO

Microsoft’s current SQL Server guidance keeps all tempdb data files at the same initial size and growth increment. Setup chooses a starting file count based on logical processors, capped at eight. That is a starting configuration, not a lifetime prescription. If measured PAGELATCH_* waits point to PFS/GAM/SGAM allocation pages, additional equally sized files can spread allocation activity; if those waits are absent, adding files may only add management complexity.

2. One database, several consumer classes

Use the supported tempdb.sys.dm_db_file_space_usage view to separate free space, user objects, internal objects, and the traditional version store. A user object includes local/global temporary tables and table variables. Internal objects include sort runs, hash work files, cursor/spool worktables, and related engine workspace. Version-store pages support row-versioned behaviors when versions use the traditional store.

USE tempdb;GOSELECT  SUM(unallocated_extent_page_count)*8.0/1024 AS free_data_space_mb,  SUM(user_object_reserved_page_count)*8.0/1024 AS user_object_space_mb,  SUM(internal_object_reserved_page_count)*8.0/1024 AS internal_object_space_mb,  SUM(version_store_reserved_page_count)*8.0/1024 AS traditional_version_store_mb,  SUM(mixed_extent_page_count)*8.0/1024 AS mixed_extent_space_mbFROM sys.dm_db_file_space_usage;GO

Take a before/after snapshot rather than treating one number as self-explanatory. A rise in internal_object_space_mb during an expensive sort is different from sustained version-store growth caused by a long-running snapshot reader. A large local temp table is different again. The incident timeline must connect the consumer class to sessions and plans.

USE tempdb;GOSELECT session_id,request_id,       SUM(user_objects_alloc_page_count-user_objects_dealloc_page_count)*8 AS user_kb,       SUM(internal_objects_alloc_page_count-internal_objects_dealloc_page_count)*8 AS internal_kbFROM sys.dm_db_task_space_usageGROUP BY session_id,request_idHAVING SUM(user_objects_alloc_page_count-user_objects_dealloc_page_count) <> 0    OR SUM(internal_objects_alloc_page_count-internal_objects_dealloc_page_count) <> 0ORDER BY internal_kb DESC,user_kb DESC;GO

Task counters start at request scope and aggregate to session counters when work completes. Temp-table caching, worktable caching, deferred deallocation, and parallel tasks affect the shape of the numbers. They are attribution evidence, not an accounting invoice accurate to the byte.

3. Reproduce user-object allocation safely

The following lab deliberately allocates a temporary table only in your session. It does not add files, change server configuration, or create a persistent object in tempdb. Capture session counters before and after, then drop the table.

USE ServiceHubLab;GOSELECT @@SPID AS session_id;SELECT user_objects_alloc_page_count,user_objects_dealloc_page_count,       internal_objects_alloc_page_count,internal_objects_dealloc_page_countFROM tempdb.sys.dm_db_session_space_usageWHERE session_id=@@SPID;GOSELECT TOP (20000)       ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n,       REPLICATE('x',200) AS payloadINTO #TempdbUserObjectFROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;GOSELECT user_objects_alloc_page_count,user_objects_dealloc_page_count,       internal_objects_alloc_page_count,internal_objects_dealloc_page_countFROM tempdb.sys.dm_db_session_space_usageWHERE session_id=@@SPID;DROP TABLE #TempdbUserObject;GO

You should see user-object allocation rise. Exact page counts vary with build, metadata/cache state, row layout, and source catalog size; the lab intentionally avoids a fake expected number. The reproducible expectation is categorical: this workload creates a user temp object and consumes tempdb data pages.

4. Allocation contention and metadata contention are different problems

Allocation contention commonly appears as PAGELATCH_* waits on allocation pages in database ID 2. Metadata contention can appear when many sessions create/drop temporary objects and contend on tempdb system metadata. A single “tempdb is slow” label hides these mechanisms.

SELECT r.session_id,r.status,r.wait_type,r.wait_time,r.wait_resource,       r.blocking_session_id,DB_NAME(r.database_id) AS database_nameFROM sys.dm_exec_requests AS rWHERE r.wait_type LIKE N'PAGELATCH%'  AND r.wait_resource LIKE N'2:%'ORDER BY r.wait_time DESC;GO

On SQL Server 2019+, memory-optimized tempdb metadata can remove a specific temporary-object metadata bottleneck by moving selected system tables to latch-free memory-optimized structures. It is not a universal tempdb accelerator. Microsoft currently recommends enabling it only when metadata contention is demonstrated, because it has memory implications and requires an engine restart to enable/disable. SQL Server 2025 supports it in Enterprise/Enterprise Developer, but not Standard or Express.

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS effective_state;SELECT type,SUM(pages_kb)/1024.0 AS mbFROM sys.dm_os_memory_clerksWHERE type IN ('MEMORYCLERK_XTP','MEMORYCLERK_SQLSTORENG')GROUP BY type;GO-- Optional, only after proving metadata contention and planning a restart:-- ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;-- Restart is required before the new state is effective.

5. Version store changed again in SQL Server 2025

The old sentence “row versions live in tempdb” is no longer universally correct. With Accelerated Database Recovery (ADR) enabled in a user database, its Persistent Version Store (PVS) lives in that database rather than the traditional tempdb version store. SQL Server 2025 goes further: ADR can now be enabled in tempdb itself, giving tempdb transactions faster rollback and more aggressive log truncation. When ADR is enabled in tempdb, monitor the PVS with ADR-specific DMVs rather than interpreting only version_store_reserved_page_count.

Enabling or disabling ADR in tempdb is a configuration operation that requires an engine restart to take effect. It is therefore discussed here as a design option, not performed by the mandatory lab.

Production judgment

For a tempdb incident, preserve evidence before changing files: consumer class, top sessions/requests, allocation waits, file free space/growth, volume free space and latency, traditional/PVS version-store state, and workload changes. Add files only to address measured allocation contention or a documented design need; pre-size based on observed demand; keep data-file sizes/growth aligned; and treat autogrowth as an emergency safety net rather than the capacity plan.

The mandatory examples require only a local SQL Server 2025 Developer or Express instance and read access to documented DMVs; some server-wide diagnostics require VIEW SERVER STATE/VIEW SERVER PERFORMANCE STATE. Memory-optimized tempdb metadata is edition-sensitive and restart-sensitive. No Azure service, cluster, or paid production edition is required.

Authoritative references

• tempdb database — architecture, files, version stores and SQL Server 2025 improvements
• Recommendations to reduce allocation contention — diagnosis and modern file-count guidance
• sys.dm_db_file_space_usage — tempdb free/user/internal/version space
• sys.dm_db_task_space_usage — task-level allocation attribution
• Accelerated Database Recovery — PVS and SQL Server 2025 tempdb ADR
• SQL Server 2025 editions and features — edition boundaries
• SQL Server 2025 build versions — servicing baseline