TempDB GAM contention for SELECT queries

I have problem manifesting itself in following fashion, my TempDB knowledge does not cover this (yet):

• An analytical query, that runs in SSMS for about 200ms, keeps running on SQL server, when launched from application, for 60+ seconds - this happens only occasionally, most of the time the problem is not present

• The queue of the runnable/suspended queries can grow up to tens of queries of the same query text with one of them SELECT queries being a head blocker blocking other identical SELECTs

• most dominant waits are SOS_SCHEDULER_YIELD and PAGELATCH_UP for the particular queries of the same text and parameter values, when the runnable/suspended queues start growing significantly in comparison to baseline

• when the problem occurs, the tens of queries queueing up have the same literal parameter values (shift start timestamp, employeeid and production area)

• the queries being suspended (during the time of the snapshot of our monitoring - DBA Dash) have PAGELATCH_UP wait as the most dominant and they're waiting for GAM page in tempdb

• query is not spilling to tempdb when I check execution plan in SSMS, using the parameters of the query that keeps piling up in the runnable/suspended queues

Configuration of the server, the database and the DB traffic:

• 4 to 6 cores (problem happened independently on two different servers with different core count)

• 4 - 6 uniformly sized TempDB files

• 40GB of RAM assigned to the instance

• in the particular database, RCSI + Snapshot is enabled (hence TempDB is getting hit)

• no deletions are occuring on the two tables against which the query runs, only INSERTs and SELECTs - only 1 row at the time is inserted

• SELECTs are hitting usually the youngest records (those inserted recently)

• sever is doing usually 400 - 700 batch requests/sec ; When the problem strikes, it peaks up to 1500 creating high IO + CPU load in comparison to normal operation

• the data in the table have more or less uniform distribution, i.e.

The core of your issue is covered by the documentation in Recommendations to reduce allocation contention in SQL Server tempdb database. You're running SQL Server 2019, which means you'll see contention on GAM pages instead of SGAM.

Your sort and hashing operations may not be spilling (though several queries in your screenshot do show writes) but SQL Server still has to allocate structures (workfiles and worktables) to support the sorting and hashing, some of which are required in case there is a spill at runtime. If you do enough of these operations quickly enough, you can see GAM page latch contention.

Latch waits are typically short, but it is possible to encounter latch convoys, resulting in (much) longer waits on occasion.

As noted in the documentation linked above, the primary solution is to add files to tempdb until the contention goes away or reduces to an acceptable level.

Probably unrelated to the specific issue here, you also have the option to enable memory-optimized metadata for tempdb in SQL Server 2019. That option is not without its drawbacks and has been the subject of a few bugs.

SQL Server 2022 introduces concurrent GAM and SGAM updates, which replaces the need for an UP (update) latch with a SH (shared) latch. This reduces the scope for problems of this nature considerably.

StackExchange Database Administrators Q#329931, answer score: 8