Design and Validate tempdb Layout, Capacity, Monitoring, and Incident Response

Learning outcomes

At 02:15, ServiceHub alerts on tempdb free space, several API requests time out, and an operator proposes three emergency actions at once: add files, shrink files, and restart SQL Server. A production runbook must turn that panic into an evidence sequence. The design objective is not “make tempdb as large as possible”; it is enough capacity, predictable growth, healthy latency, low allocation contention, observable consumers, and reversible configuration.

1. Inventory the persistent configuration and the recreated database

tempdb content is recreated at startup, but its configured file definitions survive through the instance’s system metadata and are applied when tempdb is recreated. Therefore file size/path/growth changes are operational configuration, not “temporary because tempdb is temporary.” Moving files or enabling restart-dependent features needs maintenance planning.

USE tempdb;GOSELECT df.file_id,df.name,df.type_desc,       df.size*8.0/1024 AS size_mb,       CASE WHEN df.is_percent_growth=1 THEN CONCAT(df.growth,'%')            ELSE CONCAT(df.growth*8.0/1024,' MB') END AS growth_setting,       df.max_size,df.physical_name,       vs.volume_mount_point,       vs.available_bytes/1024.0/1024/1024 AS volume_free_gbFROM sys.database_files AS dfCROSS APPLY sys.dm_os_volume_stats(DB_ID(),df.file_id) AS vsORDER BY df.type,df.file_id;GOSELECT vfs.file_id,mf.name,       vfs.num_of_reads,vfs.io_stall_read_ms,       vfs.num_of_writes,vfs.io_stall_write_ms,       CASE WHEN vfs.num_of_reads=0 THEN NULL            ELSE 1.0*vfs.io_stall_read_ms/vfs.num_of_reads END AS avg_read_stall_ms,       CASE WHEN vfs.num_of_writes=0 THEN NULL            ELSE 1.0*vfs.io_stall_write_ms/vfs.num_of_writes END AS avg_write_stall_msFROM sys.dm_io_virtual_file_stats(2,NULL) AS vfsJOIN sys.master_files AS mf  ON mf.database_id=vfs.database_id AND mf.file_id=vfs.file_idORDER BY vfs.file_id;GO

Average stall is a coarse cumulative signal whose counter window begins at engine/file lifecycle points; it is not a latency percentile and can hide bursts. Persist periodic deltas in monitoring if you need operational SLOs.

2. Capacity baseline: measure peak and growth, not folklore

Pre-size data and log files to accommodate representative recurring demand with safety margin appropriate to your recovery/operations model. Autogrowth should absorb unexpected excursions, not be the normal minute-by-minute allocator. Frequent small growth adds overhead; giant growth can take time and unexpectedly consume volume capacity. Equal data-file size/growth keeps proportional-fill behavior balanced.

USE tempdb;GOSELECT  SUM(unallocated_extent_page_count)*8.0/1024 AS free_mb,  SUM(user_object_reserved_page_count)*8.0/1024 AS user_mb,  SUM(internal_object_reserved_page_count)*8.0/1024 AS internal_mb,  SUM(version_store_reserved_page_count)*8.0/1024 AS traditional_version_mbFROM sys.dm_db_file_space_usage;SELECT COUNT(*) AS data_file_count,       MIN(size)*8.0/1024 AS smallest_data_file_mb,       MAX(size)*8.0/1024 AS largest_data_file_mbFROM sys.database_files WHERE type=0;SELECT log_reuse_wait_desc FROM sys.databases WHERE database_id=2;GO

Store this snapshot over time. Capacity planning needs the daily/weekly peaks, incident peaks, growth frequency, underlying free space and correlated workload. One sample after the incident cannot tell you how close the system normally runs to capacity.

3. Allocation waits: identify the page before adding files

SELECT r.session_id,r.wait_type,r.wait_time,r.wait_resource,r.page_resource,       prc.db_id,prc.file_id,prc.page_id,       dpi.page_type_desc,dpi.object_idFROM sys.dm_exec_requests AS rOUTER APPLY sys.fn_PageResCracker(r.page_resource) AS prcOUTER APPLY sys.dm_db_page_info(prc.db_id,prc.file_id,prc.page_id,'LIMITED') AS dpiWHERE r.wait_type LIKE N'PAGELATCH%'  AND (prc.db_id=2 OR r.wait_resource LIKE N'2:%')ORDER BY r.wait_time DESC;GO

Do not confuse PAGELATCH with storage PAGEIOLATCH. Allocation-page latch contention is in-memory synchronization; storage latency is different evidence. If allocation contention is proven, make a controlled file-count change, keep data files equally sized, measure again, and stop adding files when contention is acceptable.

4. SQL Server 2025: optional tempdb space resource governance

SQL Server 2025 adds Resource Governor limits for tempdb data-space consumption by workload group. This is a containment mechanism for runaway workload groups, not a substitute for sizing tempdb. Microsoft exposes current/peak usage and violation counts even before a limit is configured, which can help establish a baseline. Resource Governor is available in SQL Server 2025 Enterprise and Standard families (including their free Developer counterparts), but not Express.

SELECT name,tempdb_data_space_kb,peak_tempdb_data_space_kb,       total_tempdb_data_limit_violation_countFROM sys.dm_resource_governor_workload_groupsORDER BY peak_tempdb_data_space_kb DESC;GOSELECT name,group_max_tempdb_data_mb,group_max_tempdb_data_percentFROM sys.resource_governor_workload_groupsORDER BY group_id;GO

If you later configure GROUP_MAX_TEMPDB_DATA_MB or percentage limits, requests that exceed the enforced group limit can be aborted with error 1138. That is an intentional availability tradeoff: protect the instance from a runaway workload by failing the offender. Test classification and failure handling before production use. Version-store/PVS space is not governed in the same way because versions can serve requests across workload groups.

5. The incident runbook: preserve evidence before surgery

The deliberately wrong emergency move is repeated DBCC SHRINKFILE during pressure. Shrinking requires page movement and can add work exactly when the system is stressed; because tempdb grows to satisfy workload, a shrink/grow cycle often recreates the same problem. Shrink only for a justified one-time capacity/layout correction, with a tested target and enough volume headroom—not as routine maintenance.

6. Rollback from a bad file-layout change

A file added to mitigate contention may later prove unnecessary or be placed on an unsuitable volume. Removing a tempdb data file requires emptying it safely and ensuring it is not the only/required file; path changes typically take effect after restart. The exact rollback depends on what was changed, so capture the pre-change file list and scripted ALTER DATABASE tempdb MODIFY FILE definitions before maintenance.

USE tempdb;GOSELECT file_id,name,type_desc,       size*8.0/1024 AS size_mb,       growth,is_percent_growth,max_size,physical_nameFROM sys.database_filesORDER BY file_id;GO-- Save this result with the change ticket and write explicit ALTER DATABASE-- rollback statements for the exact files you plan to change.

This script intentionally does not automate filename/path restoration because file moves and restart semantics require platform-aware maintenance. The runbook should pair the logical definition with validated OS paths and service-account permissions.

7. Monitoring acceptance criteria

A healthy post-change state is not “tempdb is 80% free.” It is that recurring workloads complete within their service objectives, growth is infrequent/predictable, underlying volumes retain safe headroom, no sustained allocation/metadata latch bottleneck exists, version stores clean up as expected, query grants do not create pathological spill or semaphore queues, and file I/O is within storage capabilities. Define alert levels from your own baseline and recovery time—not borrowed percentages.

USE master;GOIF DB_ID(N'ServiceHubWorkspaceLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubWorkspaceLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubWorkspaceLab;END;GOSELECT DB_ID(N'ServiceHubWorkspaceLab') AS should_be_null;GO

This cleanup does not alter tempdb configuration. Any optional server/database configuration experiment—Resource Governor limits, ADR in tempdb, memory-optimized tempdb metadata, file additions or path changes—must be rolled back separately according to the exact experiment plan.

Bridge to Chapter 13

Chapter 12 connected optimizer choices to shared workspace resources. Chapter 13 moves back up the programmability stack: stored procedures, functions, views, triggers, safe dynamic SQL and CLR boundaries. The same lessons continue to matter—procedure design can influence recompilation, temp objects, grants and plan reuse, so programmability is not separate from performance mechanics.

Authoritative references

• tempdb database — file configuration, monitoring and SQL Server 2025 features
• Reduce tempdb allocation contention — evidence-driven file-count guidance
• Tempdb space resource governance — SQL Server 2025 workload-group limits and accounting
• sys.dm_resource_governor_workload_groups — current/peak tempdb group consumption
• sys.dm_io_virtual_file_stats — file I/O counters
• SQL Server 2025 editions and features — Resource Governor and memory-optimized metadata edition boundaries
• SQL Server 2025 build versions — servicing baseline