In SQL Server, sessions creating temp tables pile up on PAGELATCH_UP waits in tempdb; what is contending and how do you fix it?
answer
- latches on pages already in memory
- the pages that track allocation
- PFS, GAM and SGAM
- equal-size files, up to eight first
basics
~10 sSessions queue on latches for tempdb's allocation pages (PFS, GAM, SGAM) because there are too few data files. Add equal-size files, one per logical processor up to eight, then more in fours.
solid answer
~50 sEvery allocation in a data file updates its allocation pages: the PFS page (page 1, then every 8,088 pages), the GAM page (page 2) and the SGAM page (page 3), with GAM and SGAM repeating every interval of about 4 GB. When hundreds of sessions create and drop temp tables in a `tempdb` with one or two files, they all latch the same few pages: `PAGELATCH_UP` or `PAGELATCH_EX` on database 2, not I/O waits. The fix is more data files with the same size and growth, because proportional fill favours the file with most free space: one per logical processor up to eight, then more in multiples of four. SQL Server 2016 made uniform extents and growing all files together the default, so trace flags 1117 and 1118 are unnecessary. SQL Server 2019 lets PFS pages update under a shared latch, and 2022 allows concurrent GAM and SGAM updates.
code
sql · 11 linesSELECT r.session_id,
r.wait_type,
r.wait_resource,
pi.page_id,
pi.page_type_desc,
OBJECT_NAME(pi.object_id, pi.database_id) AS object_name
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS pi
WHERE prc.db_id = 2
AND r.wait_type LIKE N'PAGELATCH[_]%';go deeper
Recall that tempdb is shared by the whole instance and that SQL Server sets it up with several data files.
Explain what PFS, GAM and SGAM pages do and why many sessions allocating in one file contend on latches.
Confirm allocation contention from wait resources and page types, size the file count, and separate it from metadata contention.
Set a standard tempdb layout for every instance and decide when memory-optimized metadata is worth its limits and restart.
## What is actually contending `tempdb` is one database shared by the whole instance. Temporary tables, table variables, sort and hash spills, and the row-version store all allocate pages in it. Every allocation and deallocation in a data file updates that file's **allocation pages**: - **PFS (Page Free Space)**: page 1 of the file, then one every 8,088 pages. It records whether each page is allocated and how full it is. - **GAM (Global Allocation Map)**: page 2, covering an interval of about 64,000 extents (about 4 GB), with one bit per extent saying whether it is free. - **SGAM (Shared Global Allocation Map)**: page 3, same interval, tracking mixed extents that still have a free page. These pages are protected by **buffer latches**, short-lived locks on in-memory pages. With few data files and many cores, a burst of sessions creating and dropping temp objects all want the same handful of pages. They wait on `PAGELATCH_UP` (update mode, typical for allocation pages) or `PAGELATCH_EX`. These are **not** `PAGEIOLATCH` waits: the pages are in memory, and faster disks will not help. ## Confirming it 1. In `sys.dm_exec_requests`, many sessions show `wait_type` `PAGELATCH_UP` or `PAGELATCH_EX`, with a `wait_resource` in database ID 2, which is `tempdb`. 2. From SQL Server 2019, pass the `page_resource` column to `sys.fn_PageResCracker` and then to `sys.dm_db_page_info` to see the page ID and page type. Page 1 or a multiple of 8,088 is a PFS page; pages 2 and 3 are GAM and SGAM. 3. If the latched pages belong to `tempdb` **system tables** instead, the bottleneck is temporary-object metadata, a different problem with a different fix. ## Fixing allocation contention **Add data files.** Each file has its own PFS, GAM and SGAM pages, so allocations spread across more latches. The guidance: - Up to eight logical processors: as many data files as logical processors. - More than eight: start with eight files. - Still contended: add files in multiples of four, or change the workload. **Keep the files identical.** SQL Server uses a **proportional-fill** algorithm that favours files with more free space, so one larger file draws most allocations and recreates the hotspot. All `tempdb` data files should have the same initial size and growth settings. Too many files is not free either: the documentation warns that more files per filegroup can hurt loads with many large sorts that spill to disk. ## What the releases changed | Release | Change that matters here | | --- | --- | | SQL Server 2016 | Setup creates as many `tempdb` data files as logical processors or eight, whichever is lower. All allocations use uniform extents, so trace flag 1118 is no longer needed. All files in the filegroup grow together (`AUTOGROW_ALL_FILES`), so trace flag 1117 is no longer needed. Temp tables and table variables are cached. | | SQL Server 2019 | PFS pages can be updated under a shared latch instead of an exclusive one, in all databases. **Memory-optimized TempDB metadata** became available. | | SQL Server 2022 | Concurrent updates to GAM and SGAM pages reduce allocation latch contention, especially in `tempdb`. | | SQL Server 2025 | `tempdb` space resource governance and accelerated database recovery in `tempdb`; neither targets allocation latches. | An instance upgraded in place keeps the `tempdb` file layout it had, so an old single-file `tempdb` does not fix itself on a new release. ## Metadata contention is a separate fix When the contended pages belong to system tables that store temporary-object metadata, extra data files do not help. SQL Server 2019 and later can turn those tables into latch-free, non-durable memory-optimized tables: - `ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;` followed by a service restart. - `SERVERPROPERTY('IsTempdbMetadataMemoryOptimized')` confirms it after the restart. - Because of its limitations, the documentation recommends enabling it only when metadata contention is real and significant, and suggests binding `tempdb` to a resource pool that caps its memory. Two neighbouring problems look similar and belong elsewhere: the row-version store that read-committed snapshot and snapshot isolation keep in `tempdb` is part of the row-versioning material, and `PAGELATCH_EX` on the last page of an ever-increasing clustered key is a clustering-key design problem.
- You added files but one tempdb file is still far hotter than the rest. What went wrong?The files are probably not the same size or growth setting. Proportional fill sends allocations to the file with the most free space, so a larger or recently grown file takes most of them and its allocation pages stay hot. Resize all data files to the same initial size and growth so allocations spread evenly.
- Should you enable trace flags 1117 and 1118 on SQL Server 2019 for tempdb?No. Since SQL Server 2016, `tempdb` always allocates uniform extents, which was 1118's job, and all files in the filegroup autogrow together, which was 1117's. Their behaviour is built in for `tempdb`, so the flags add nothing. Older articles and checklists still recommend them because they predate that change.
- Adds one tempdb data file per logical processor however many processors exist.
- Says tempdb data files can differ in size because SQL Server balances them evenly.
- Thinks PAGELATCH waits in tempdb mean the tempdb disk is too slow.
- Still requires trace flags 1117 and 1118 for tempdb on SQL Server 2019.
- Expects memory-optimized tempdb metadata to fix PFS and GAM contention.
Assumes SQL Server 2025; uniform extents came in 2016, concurrent PFS updates and memory-optimized metadata in 2019, GAM and SGAM concurrency in 2022.