Sample Header Ad - 728x90

SQL Server 2019 Memory-Optimized Tempdb Metadata doesn't eliminate PAGELATCH_* waits on GAM

2 votes
1 answer
252 views
We are using SQL Server 2019 with a third party application that makes heavy use of the tempdb database. And we have lot of PAGELATCH_xx waits on tempdb. Memory-Optimized Tempdb Metadata is enabled. Just checked that this query returns 1:
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');
But we still have tempdb contention. Example of [script from Madeira Toolbox](https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Identify%20Tempdb%20Contention.sql) output: script output Is this expected behavior? What could be done to eliminate PAGELATCH_xx waits on tempdb? Should increasing the number of tempdb files help? (the documentation says yes, but tried increasing it from 16 to 32, it doesn't seem to make much of a difference). Should faster tempdb storage (with low latency) help? (I think not, but I can be wrong) Found an interesting presentation: [SQL Server 2022: System Page Latch Concurrency Enhancements](https://www.youtube.com/watch?v=2FYyOk27ZxM)
Will upgrading to SQL Server 2022 solve the problem?
Asked by edo1 (121 rep)
Mar 27, 2024, 09:12 AM
Last activity: Mar 27, 2024, 10:02 AM