The clearest tempdb lesson I have came from an ETL pipeline we scaled from 4 concurrent workers to 32 and watched get slower. Not flat. Slower. Duration doubled, CPU dropped, and the waits piled up on exactly three pages: 2:1:1, 2:1:2, and 2:1:3. Those are the PFS, GAM, and SGAM pages of tempdb's first data file, and thirty-two sessions were standing in line to flip bits on them. The fix cost nothing and took twenty minutes. The diagnosis took a day, mostly because half the team was reading the wrong wait type.
tempdb is the one database every session shares. Temp tables, table variables, worktables for sorts and hashes, snapshot isolation row versions, online index rebuild sort spills: all of it lands in the same place, and all of it competes for the same allocation machinery. Here is how I work through tempdb contention on SQL Server 2016 through 2022, in the order that has actually paid.
Is it PAGELATCH or PAGEIOLATCH, and why does the difference matter?
PAGELATCH is a short wait for an in-memory latch on a page that is already in the buffer pool, while PAGEIOLATCH is a wait for storage to deliver a page, and the fixes for the two have almost nothing in common. People conflate them because the names differ by two letters, and then they buy faster disks for a latch problem or add data files for a disk problem. Check which one you actually have before touching anything.
The mechanism behind tempdb's famous PAGELATCH waits is the allocation bitmaps. The PFS page tracks free space and allocation state for roughly 8,000 pages at a time, the GAM and SGAM pages track which extents are free or shared, and every temp table creation, every insert into one, every worktable a sort operator builds has to touch these structures to find space. The pages themselves live in memory, so this is never about disk speed. It is a queue at a single door, and the answer is more doors, which is what multiple data files give you.
How do I confirm allocation contention in sys.dm_os_waiting_tasks?
Look for many sessions simultaneously waiting with a PAGELATCH type on resources in database 2, which is tempdb, and specifically on the early pages of a file. The wait_resource column encodes it as database:file:page, so 2:1:1 is the PFS page of tempdb file 1, 2:1:2 is its GAM page, and 2:1:3 its SGAM page. The PFS page recurs every 8,088 pages further into the file, while the GAM and SGAM pair only repeats about every 511,232 pages, roughly four gigabytes, so early-page contention is the shape you will actually meet. If you see 2:3:1 and 2:5:1 as well, the contention spans files and the story is bigger than one file.
SELECT wt.session_id,
wt.wait_type,
wt.wait_duration_ms,
wt.wait_resource,
st.text
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE wt.wait_type LIKE N'PAGELATCH%'
AND wt.wait_resource LIKE N'2:%';The wait type suffix tells you the direction. PAGELATCH_UP on a PFS page is a session that wants to update allocation state, PAGELATCH_EX is an exclusive latch, PAGELATCH_SH means even the readers are queuing behind the writers. Run the query a few times during the slow window. A one-off row is weather; a dozen sessions all parked on 2:1:1 across several samples is the diagnosis. If instead the resources point at user tables or data pages in your application database, this is a different problem entirely, like last-page insert contention on a hot index, and tempdb is innocent.
How many tempdb data files, and what size?
Start with one file per logical core up to eight, all exactly the same size with exactly the same autogrowth setting, and add more in steps of four only if the waits persist. SQL Server 2016 made most of the old folklore built-in: setup now offers to configure multiple tempdb files out of the box, and the behaviors that trace flags 1117 and 1118 used to enable, growing all files together and favoring uniform extents, became the default for tempdb. If you are on 2016 or later, the main mistake left to make is file count and sizing, not trace flags.
The reason equal size matters is proportional fill. The allocator prefers the file with the most free space, so equally sized files get roughly equal allocation traffic, which spreads the PFS and GAM load across a bitmap per file instead of one. If one file is twice the size of the others, it attracts twice the allocation work and you have quietly rebuilt the bottleneck. Autogrowth settings differ per file too, and a file that grows ahead of its siblings becomes the new hot spot. In that ETL incident, going from one file to eight on a 16-core box took the PAGELATCH waits on 2:1:1 from about 40 percent of tempdb wait time down to noise, and the 32-worker run beat the original 4-worker time. What did not help was going to 32 files: beyond roughly core count, you are usually treating a query problem with a config change.
Should I turn on memory-optimized tempdb metadata in 2019?
If your contention survives a sane file count and the waits are on tempdb system catalog pages rather than PFS or GAM pages, then yes, it is the right tool, but walk in knowing it is a restart both ways and it comes with documented limits. SQL Server 2019's memory-optimized tempdb metadata moves the system tables that track temp objects into latch-free memory-optimized structures, which removes the catalog contention that data files cannot fix. You enable it with one command and a service restart:
ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;
-- requires a restart; verify afterwards with:
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');The caveats are real. Because it needs a restart to enable and another to disable, you cannot flip it on at noon to see what happens; it belongs in a change window with a tested rollback plan. Columnstore indexes cannot be created on temporary tables while the feature is on, which bites some reporting workloads. And a transaction that touches memory-optimized tables in a user database cannot also touch tempdb metadata in the same transaction, so shops using In-Memory OLTP need to test their exact code paths. There was also an early-build reliability bug that could make the server fail to start with the feature enabled, so apply current cumulative updates before you consider it. Tested on 2019 and 2022 with current patches and a workload that actually exercises your edge cases, it is a solid fix. Enabled blind during an incident, it is a new incident.
Temp tables or table variables: which hammers tempdb less?
Neither, physically: both are backed by tempdb storage, and the myth that table variables live in memory should have died a decade ago. Both allocate pages, both touch the allocation bitmaps, both can contend. The differences that matter are elsewhere. Temp tables get real statistics and can have indexes beyond the primary key, so the optimizer estimates them honestly. Table variables historically got a one-row estimate no matter how many rows they held, which produced plans that were fine at ten rows and catastrophic at a million; SQL Server 2019's deferred compilation for table variables fixes that by waiting for the actual cardinality before picking a plan. Table variables do less transaction logging and ignore rollback, which makes them cheaper in tight loops and occasionally surprising in error handling.
My rule of thumb has held for years: small and short-lived, a table variable is fine; anything that joins to big tables or holds thousands of rows, use a temp table with a deliberate index. And if your profiler shows millions of temp object creates per hour, the problem is the pattern, not the object type. A procedure that builds the same scratch table on every execution, called a thousand times a second, will thrash tempdb metadata no matter what you call the table. Cache it, make it a real staging table, or question why it exists per-call at all.
Why does RCSI fill tempdb's version store?
Because read committed snapshot isolation keeps old row versions in tempdb so readers can see the last committed state without blocking, and every one of those versions is tempdb space held until no open transaction could still need it. One exception deserves naming up front: with Accelerated Database Recovery enabled, which it always is in Azure SQL Database and Managed Instance and can be per database on 2019 and later on-prem, row versions live in the user database's own persistent version store instead of tempdb, and this whole monitoring story moves there with them. Each versioned row carries a 14-byte tag pointing into the chain, and a long-running transaction pins everything generated after it started. One forgotten SSMS window with an open transaction can grow the version store for hours. Watch it directly:
USE tempdb;
SELECT DB_NAME(database_id) AS database_name,
SUM(reserved_space_kb) / 1024.0 AS version_store_mb
FROM sys.dm_tran_version_store_space_usage
GROUP BY database_id;On SQL Server 2016 SP2 and later that DMV gives you per-database version store consumption, and pairing it with the oldest open snapshot transaction tells you whether growth is workload or a stuck session. The full story of turning RCSI on and living with the version store is in my notes on READ_COMMITTED_SNAPSHOT migration. The tempdb-specific takeaway here is simpler: once versioning is on, tempdb free space becomes a first-class metric, and "tempdb full" at 2 a.m. is usually a version store nobody was watching.
Where MonPG's SQL Server support stands
MonPG monitors PostgreSQL in production today, and SQL Server support is on the roadmap and in active development, not shipped. The tempdb story is a good example of why we want it: PAGELATCH versus PAGEIOLATCH splits, per-file allocation waits, and version store size trended next to tempdb free space are exactly the graphs that turn a 2 a.m. page into a five-minute diagnosis. The SQL Server monitoring (coming soon) page tracks where that work stands. Until it ships, the queries above in an agent job, dumping samples into a table, will get you most of the way there on your own.