It was 11:40 in the morning on the last business day of the quarter when the reporting tier fell over. Median query time went from 40 milliseconds to nine seconds in about three minutes. The dashboard said CPU at 34 percent, disk latency under two milliseconds, memory fine — every light green, and the application was on its knees. sys.dm_os_waiting_tasks told the real story: 380 sessions, nearly all of them in PAGELATCH_EX, and the wait_resource column read like a stuck record — 2:1:1, 2:1:1, 2:1:2, 2:1:1. Page 1 of file 1 in database 2. tempdb's first PFS page. A sixteen-core instance with a single tempdb data file, and every session in the building was standing in one queue to ask that page for scratch space.
The fix took eleven seconds to issue: seven more tempdb files, equal size, equal growth. The latch queue drained before I finished refreshing the DMV. Nothing about the queries changed, nothing about the data changed — we just gave the allocator more doors. This is the write-up I wish someone had handed me before that morning: what those pages are, why one file serializes everything, the modern file-count guidance, and the version store case where extra files only half-help. Applies to SQL Server 2016 through 2022, on-prem or Azure SQL Managed Instance.
Why is tempdb a special case in the first place?
Because tempdb is one shared scratch database for the entire instance, so every session on every user database allocates from the same small pool of bookkeeping pages at the same time. Look at what actually lands there: local and global temp tables, table variables, work tables the engine builds for sorts, hash joins, and spools, spool space for eager index maintenance, the row version store that backs snapshot isolation, read committed snapshot, online index operations, and AFTER triggers, plus spill space when a sort or hash estimate is wrong and memory runs out mid-execution. A busy OLTP instance is creating and destroying objects in tempdb thousands of times per second even when nobody types CREATE TABLE with a hash prefix.
Two design facts turn that traffic into a bottleneck. First, tempdb is recreated from scratch at every instance start, so it is always young, always empty, and always being allocated into — there is no steady state where the allocator can coast. Second, tempdb operations are minimally logged and run under simple recovery, which means the engine optimizes the write path and leans even harder on fast allocation. Every other database can spread its allocation traffic across its own files; tempdb traffic is, by definition, everybody's traffic, concentrated.
What are PFS, GAM, and SGAM pages, and why does one file serialize them?
They are allocation bitmaps, and any session that needs space in tempdb has to latch one of them — often exclusively — before it can touch a data page. Page 1 of every data file is the PFS page, tracking which of the following 8,088 pages are allocated and how full they are; the pattern repeats, so pages 1, 8,089, 16,177 and so on are all PFS. Page 2 is the GAM, recording which extents are free, and page 3 is the SGAM, recording which mixed extents still have a free page. GAM and SGAM each cover roughly 64,000 extents and repeat on that cadence through the file. When forty sessions concurrently create temp tables, they all need to find and mark free space, which means reading and updating these bitmaps — and the updates are serialized by latches, short-lived in-memory locks that protect physical page structures. One data file means one PFS page 1, one GAM, one SGAM, and therefore one queue.
The wait types are the tell: PAGELATCH_EX or PAGELATCH_UP against resources like 2:1:1, 2:1:2, or 2:1:3 — database 2, file 1, the bitmap pages. Note the word latch. These are not lock waits, there is no lead blocker to kill and no transaction to roll back, which is why the blocking-chain playbook from my lead blocker notes finds nothing here: no LCK_M waits, no chain, just a crowd of sessions each holding a hot page for microseconds while hundreds more pile up behind them. LCK waits point at data and transactions; PAGELATCH waits on low-numbered tempdb pages point at the allocation machinery itself.
How do I confirm allocation contention and not something else?
Look for PAGELATCH waits whose resource is a tempdb allocation page while CPU and I/O look under-used — that combination is nearly diagnostic on its own. sys.dm_os_waiting_tasks shows the live queue, and on current versions its wait_resource column gives you the database:file:page triple directly, so you do not have to parse resource_description by eye:
SELECT wt.session_id,
wt.wait_type,
wt.wait_duration_ms,
wt.wait_resource,
es.host_name,
es.program_name
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS es
ON es.session_id = wt.session_id
WHERE wt.wait_type LIKE N'PAGELATCH%'
AND wt.wait_resource LIKE N'2:%'
ORDER BY wt.wait_duration_ms DESC;What you want to see is the resource pattern, not any single wait. PAGELATCH on a data page in a user database is a hot-row problem with a completely different fix; PAGELATCH on 2:1:1 or 2:1:8089 is the allocation bitmap queue this article is about. For the accumulated view, sys.dm_os_wait_stats shows PAGELATCH waits broken out with their wait time and signal time since the instance started — high PAGELATCH wait time with high signal wait time (time spent runnable, waiting for CPU) would point back at CPU pressure, so allocation contention shows up as high resource wait time with signal time staying modest. The performance counters Temp Tables Creation Rate and Worktables Created/sec give you the demand side of the curve, and sys.dm_io_virtual_file_stats confirms the per-file part: one tempdb file carrying every allocation while the instance idles at a third of its cores.
The trap to avoid is fixing the wrong bottleneck. If your PAGELATCH pile-up sits on user-database pages, more tempdb files do nothing. If tempdb's bottleneck is genuinely I/O — worktables spilling to slow storage — more files on the same slow volume just multiply the queue. The resource pattern in the DMV is what separates these, which is why I look at it before touching anything.
How many tempdb data files should I actually create?
Start with one file per logical core up to eight, and if allocation contention persists after that, add files four at a time while you keep measuring. That is the current Microsoft guidance, and it matches what I have seen in the field: on the sixteen-core box from the opening story, eight files killed the PFS queue outright, and going to sixteen would have added nothing but administration. Each file carries its own PFS, GAM, and SGAM chains, and the allocator spreads new allocations across files round-robin, weighted by free space — proportional fill — so more files directly multiplies the number of bitmap pages the workload can hammer in parallel.
Two rules make or break the fix. First, every file must be the same size with the same growth increment, always: proportional fill sends allocations to the file with the most free space, so one file twice the size of the others quietly becomes the new bottleneck, and you are back to one queue wearing a trench coat. Second, size them generously up front so autogrowth is an emergency brake rather than a routine event — every growth is a serialized moment and, depending on instant file initialization, a stall. The ALTER is unglamorous:
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, SIZE = 4096MB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev2,
FILENAME = 'D:\SQLData\tempdb2.ndf',
SIZE = 4096MB, FILEGROWTH = 512MB);Repeat the ADD for however many files the count math says. Adding files to tempdb takes effect immediately and needs no restart; shrinking or removing one does.
If you have read older tuning guides, two trace flags deserve a footnote. Trace flag 1118 forced uniform extents for all allocations, eliminating SGAM and mixed-extent contention, and 1117 made all files in a filegroup grow together; pre-2016 servers needed both switched on as a matter of hygiene. Since SQL Server 2016 those behaviors are simply how tempdb works by default — uniform extents, all files growing as a group — and the setup wizard itself prompts you for a file count at install time. On anything current, the flags are history, not homework.
What if the real problem is the version store?
Extra files relieve version store latching too, but most version store pain is a growth problem, not a bitmap problem, and files alone will not fix it. Every row version written for snapshot isolation, read committed snapshot, online index operations, or AFTER triggers lands in the tempdb version store as an append-only chain, and cleanup can only discard versions older than the oldest active snapshot transaction. One reporting query that started at 8 AM under snapshot isolation pins every version generated after it; I have watched a single forgotten SSRS subscription grow the version store to 90 GB and autogrow tempdb until the volume filled. The PAGELATCH symptom and the growth symptom travel together often enough that people fix the files, declare victory, and get paged again at month-end.
The measurement side is two DMVs: sys.dm_tran_version_store_space_usage, available since SQL Server 2016, shows version store consumption per database, and sys.dm_tran_active_snapshot_database_transactions lists the transactions currently pinning it, oldest first. The fix is never in tempdb — it is the long transaction. Kill or reschedule the offender, put a max duration on reporting workloads, and consider whether that reporting query needs snapshot isolation at all. On SQL Server 2019 and later, Accelerated Database Recovery moves the persistent version store into the user database itself, which takes that traffic out of tempdb entirely; it is a real option with real costs — per-database storage overhead and background cleanup work — so enable it per database where the version store pain actually lives, not as a blanket default.
Watching tempdb contention with MonPG when SQL Server support lands
The counters that would have caught my 11:40 incident before the tickets did are not exotic: PAGELATCH wait time split by resource, with waits against tempdb allocation pages broken out from everything else; the ratio of tempdb data files to logical cores as a standing config fact; version store size next to the age of the oldest snapshot transaction; and temp table creation rate as the demand curve. Alert on the first, trend the rest. MonPG monitors PostgreSQL in production today, and SQL Server monitoring is on the roadmap and in active development — the SQL Server monitoring (coming soon) page carries the honest status. When it lands, those tempdb latch and version store counters are exactly what it should surface. Until then, the waiting-tasks query above on a thirty-second schedule into a logging table is a perfectly serviceable monitoring stack, provided someone looks at the chart before the last business day of the quarter.