Tempdb Latch Contention on SQL Server 2019 and 2025

The server has plenty of idle CPU, and the application is slow anyway. sys.dm_exec_requests shows dozens of sessions suspended on PAGELATCH_EX, and every wait_resource starts with 2:. Database 2 is tempdb. The page number after the second colon is where I start.

Illustration of a long queue of small robot couriers waiting at a single open drawer of a card catalogue while a woman DBA notes the busy drawer, representing sessions waiting on one tempdb page latch.

I wanted to see allocation and metadata contention on my own instances, so I wrote a workload that creates temporary tables from many sessions at once and recorded where the waits landed.

Reading the Wait Resource

A page wait resource has the form database:file:page. In every data file, page 1 is a Page Free Space (PFS) page, page 2 is the Global Allocation Map (GAM) and page 3 is the Shared Global Allocation Map (SGAM). The GAM tracks uniform extents, the SGAM tracks mixed extents, and another PFS page follows every 8,088 pages. Waits on these pages are allocation contention. Microsoft describes a second kind, metadata contention, where the waits land on higher page numbers such as 2:1:118 that belong to system tables like sysschobjs.[1] If you want to see the allocation pages up close, I took them apart in A Tour of SQL Server Page Types.

Microsoft’s list of tempdb improvements for SQL Server 2016 includes caching of temporary tables and table variables, which it says makes dropping and recreating them fast and reduces allocation and metadata contention. The same list says that from 2016 on, all tempdb allocations use uniform extents. SQL Server 2019 updates PFS pages under a shared latch instead of an exclusive one. SQL Server 2022 added concurrent updates to GAM and SGAM pages.[2]

The Test

I used two instances on one workstation, each with 14 logical processors: SQL Server 2019 (15.0.4500.2, Developer) and SQL Server 2025 (17.0.5015.4, Enterprise Developer). I didn’t change any instance setting. The 2019 instance has four tempdb data files and the 2025 instance has eight. Memory-optimized tempdb metadata was off on both, which you can check like this:

A scratch database held three procedures. Each one puts four 2,000-byte rows into a temporary object:

  1. sat_temp_cached creates a #temp table and inserts the rows.
  2. sat_temp_uncached is the same, with one CREATE INDEX after the CREATE TABLE. That index stopped the table from being cached. In every run of this procedure, the Temp Tables Creation Rate counter ended within 10 of the call count.
  3. sat_tablevar uses a table variable instead.

A PowerShell script opened 48 connections with pooling off, each named sat_tdb_load_01 and up through Application Name. Each connection called one procedure in a loop for 60 seconds, from its own runspace. I measured each run four ways:

  • Each load session’s own PAGELATCH waits from sys.dm_exec_session_wait_stats, read just before it disconnected.
  • A before and after snapshot of sys.dm_os_wait_stats for the PAGELATCH_% wait types, as a cross-check. The wait stats capture script uses the same delta idea over a longer period.
  • The Temp Tables Creation Rate counter from sys.dm_os_performance_counters, to confirm whether caching was happening.
  • A sampler connection that recorded the PAGELATCH waits in progress on the load sessions, from sys.dm_os_waiting_tasks and sys.dm_exec_requests. It aimed for every 100 ms. In the later runs, when it also classified each page, it managed about one sample every 140 to 300 ms.

I ran the instances one at a time, never both together. Each procedure ran twice per instance, and the uncached one ran three times on 2025.

Results

The wait column is the total PAGELATCH wait time the 48 load sessions reported for themselves in sys.dm_exec_session_wait_stats. For every run with more than 100 seconds of waits, the instance-wide sys.dm_os_wait_stats delta was within 4 percent of it. Where a cell shows two or three numbers, they’re the separate runs.

Instance Procedure Calls in 60 s PAGELATCH wait (s) Sampled waits (page)
2019 #temp, cached 424,751 / 505,647 2,592 / 2,516 sysschobjs, PAGELATCH_EX
2019 #temp, uncached 106,444 / 107,212 2,661 / 2,670 One SGAM page, PAGELATCH_UP
2019 table variable 1,730,799 / 1,931,744 9 / 0 63 GAM samples, then none
2025 #temp, cached 354,149 / 329,691 2,646 / 2,602 sysschobjs, PAGELATCH_EX
2025 #temp, uncached 124,064 / 134,607 / 129,765 2,344 / 2,434 / 2,489 sysschobjs index pages
2025 table variable 2,009,056 / 1,753,199 0.5 / 0.6 None, then 3 on sysschobjs

Forty-eight sessions for 60 seconds is 2,880 session-seconds. In the cached #temp runs, 87 to 92 percent of that time went to page latch waits. In the first 2019 run, that’s an average of 54 seconds out of each session’s 60.

Cached temp tables. The creation counter moved by 66 and 15 on 2019 and by 48 on 2025, while the procedure ran hundreds of thousands of times, so the tables were being cached. The waits still landed on sysschobjs pages on both versions: all 6,585 classified samples on 2019, and 9,435 of 9,438 on 2025. Caching removed the table creation from each call, and on this workload the sessions still spent most of their time waiting on sysschobjs.

Uncached temp tables. The creation counter tracked the call count, so every call created a new table. On 2019, 98.7 and 99.7 percent of the sampled waits were on a single SGAM page, almost all of them PAGELATCH_UP (2:4:3 in the first run, 2:5:3 in the second). On 2025, the wait stats delta showed no PAGELATCH_UP at all, and 96 to 98 percent of the classified samples were on sysschobjs index pages. The 2025 instance completed about a fifth more calls.

My two instances differ in build, file count and starting tempdb size, so I can’t attribute the difference to one cause, but the 2022 change to GAM and SGAM latching fits what I saw on 2025. I also can’t explain from these DMVs why 2019 latched an SGAM page at all, given that tempdb has used only uniform extents since 2016.

PFS pages. No sample on either instance landed on page 1 of any file, or on any other page number divisible by 8,088. That matches the shared-latch PFS change in SQL Server 2019.

Table variables. With the same four rows, the table variable procedure ran about four times as often as the cached #temp procedure on 2019, and five to six times as often on 2025. Latch wait stayed under 10 seconds per run. The creation counter moved by 8 to 52 per run, which suggests the table variable was cached too. I tested one shape of table variable with no indexes, so I wouldn’t generalize this to every table variable.

In two of the three uncached runs on 2025, at least one session hit my 30-second client timeout. The script didn’t reconnect, so later calls on that session failed and its waits are missing from the total. I don’t know what the call was waiting on. For the third run I added a reconnect and had the sampler record any wait over 2 seconds, and that run had no timeouts. The call counts include successful calls only.

Naming the Page

On SQL Server 2019 and later, sys.fn_PageResCracker turns the page_resource column of sys.dm_exec_requests into a database, file and page ID.[3] sys.dm_db_page_info then reads that page’s header, including its page type and owning object_id.[4] This is close to what my sampler ran:

Run it while the waits are happening. In my first set of runs I stored the wait resources and classified them after the load stopped. On 2025, the samples from the first uncached run pointed at 824 distinct pages, and sys.dm_db_page_info returned no page type for any of them after the run. In the later runs I classified each page at sample time, and those waits were on sysschobjs index pages, so I expect the earlier ones were too.

On 2025, resource_description in sys.dm_os_waiting_tasks adds latch details after the page ID, such as 2:1:351 (LATCH 0x...: Class: BUFFER ...). On 2019 it’s only 2:1:118. If you parse that column, take the text before the first space.

What I Didn’t Change

Memory-optimized tempdb metadata. SQL Server 2019 added an option that turns the system tables that manage temporary object metadata into latch-free, non-durable, memory-optimized tables. Microsoft’s diagnostic query for this kind of contention includes object_id 34, which resolves to sysschobjs on my instances. Turning the option on with ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON needs a service restart, and Microsoft recommends it only when metadata contention significantly affects the workload, because of current limitations.[5] The sysschobjs waits in my cached runs, and in the 2025 uncached runs, are the kind it’s aimed at. I didn’t enable it or restart either instance, so I haven’t measured its effect.

Number of data files. For SQL Server 2016 and later, Microsoft’s starting point is one equally sized data file per logical processor, up to eight, then more in groups of four, up to the number of logical processors, if contention continues.[6] My 2019 instance has four files on 14 logical processors. I didn’t add any. In each 2019 uncached run, almost all the SGAM waits landed on one file’s page 3, and I can’t tell from this test whether more files would have spread them.

Limits

  • I tested one build of each version, on one workstation, with a synthetic workload. I didn’t test SQL Server 2022.
  • The two instances differ in tempdb file count as well as version. The 2025 data files also started at 8 MB each and autogrew during the runs, while the 2019 files were 640 MB each.
  • Two or three runs per case can’t measure run-to-run variation well.
  • The sampler reads page headers while the load runs, so it adds a small amount of latch traffic of its own.
  • Other test work was running on these instances during some runs. At the start of each run, no more than one other user request was active.

Try It Yourself

Use a non-production instance and a scratch database. Run the three procedures from 32 to 64 sessions for a minute each, take a wait stats delta, and run the classification query during the load. If your waits land on page 1, 2 or 3 of a file, or on a page number divisible by 8,088, look at allocation. If the query names sysschobjs or other system tables, look at how your code creates temporary objects and at memory-optimized tempdb metadata. Write down your build, file count and core count with the results.

Related: Spinlock Contention for DBAs: Measure a Delta, Not a Total (available 2026-11-23).

If you’ve measured tempdb contention before and after memory-optimized metadata or the 2022 latch changes, I’d like to see your numbers. Find me on Bluesky or LinkedIn.

References

  1. Improve scalability with system page latch concurrency enhancements in SQL Server 2022 - Microsoft SQL Server Blog, July 21, 2022. Describes pages 1, 2 and 3 of a data file as PFS, GAM and SGAM, the GAM for uniform extents and the SGAM for mixed extents, a PFS page every 8,088 pages, and metadata contention on pages such as 2:1:118 in system tables like sysschobjs. ↩
  2. tempdb database: Improvements in tempdb for SQL Server - Microsoft Learn. Lists caching of temporary tables and table variables and uniform extents in tempdb (2016), concurrent PFS updates under a shared latch (2019), and concurrent GAM and SGAM updates (2022). ↩
  3. sys.fn_PageResCracker (Transact-SQL) - Microsoft Learn. Returns db_id, file_id and page_id for a page_resource value from sys.dm_exec_requests, for use with sys.dm_db_page_info. ↩
  4. sys.dm_db_page_info (Transact-SQL) - Microsoft Learn. Returns page header information, including object_id and index_id, on SQL Server 2019 and later. ↩
  5. tempdb database: Memory-optimized TempDB metadata - Microsoft Learn. Describes the latch-free, non-durable, memory-optimized system tables, the diagnostic query (which lists object_id 34), the restart requirement, and the recommendation to enable the feature only when metadata contention significantly affects the workload. ↩
  6. Recommendations to reduce allocation contention in SQL Server tempdb database - Microsoft Learn. Gives the tempdb data file guidance for SQL Server 2016 and later: one file per logical processor up to eight, then groups of four. ↩