3

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. no employeeid in no shift for particular production area have significantly more records than the others
  • attached screenshot is a table showing snapshot from DBA Dash at a time of the problem manifesting itself fully
  • tempDB, despite RCSI+ snapshot does not grow much, it's a few GBs on a 60GB drive (stable status for several months and after few episodes of what I describe here)
  • all of the SELECT queries in the screenshot have PAGELATCH_UP wait, roughly half of them has following additional info:
    • wait resource type: PAGE
    • wait resource: 2:3:2 (I reckon the meaning is: tempdb database, 3rd file in tempdb, 2nd page we're waiting for)
    • wait file: PRIMARY | temp2
    • page type: GAM
    • Wait is Compile: False

Besides obvious effort to get to the root cause and fix it permanently, my main questions would be:

  • how come SELECT queries can access GAM pages heavily in tempdb?
  • if I admit SELECT can touch GAM page frequently, what can stop query (head blocker) from progressing (i.e. reading GAM page and making it immediately available to others)?

Pointer to any relevant study resource / advice would be highly appreciated

query plan here

DBADash running queries snapshot

EDIT: To answer John's comment, I'd like to add following:

  • CTFP: 30
  • MAXDOP: 4 (set for both SQL servers although one has 6 cores, the other has 4)
  • there are 2 instances per SQL VM - this instance where the problem occured, then other "office" instance, that puts negligible load on the SQL server VM
  • Tables

ManualPanelEntries: 6933089 records , 1221 MB data, 2591 MB indexes - indeed, indexes consume more space than the data itself

Weights: 3108486 records, 178 MB data, 170MB indexes

  • The whole DB: 125GB

============

What I found out after writing the post is, that also the head blocker (one of many parallel identical SELECTs) waits for GAM, second page of particular tempdb file. As if something else (version store?) holds lock on particular GAM page preventing other queries using TempDB to proceed.

0

2 Answers 2

8

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.

0
3

Select queries don't usually block other selects. I suspect they might be waiting on compile locks.

If you are getting lots of queries executing around the same time with the same param values it might be a problem you can fix from the app side. Use caching. Make sure there are not issues with the code that causes queries to get executed more frequently than necessary. E.g calling inside a loop instead of outside.

As you are seeing tempdb GAM contention my first response would be to check if I'm on a recent patch level. There have been significant improvements to help with tempdb contention in various CUs. Next I would look to double the temp db files. If you have 6 cores with 6 files, double that to 12. It might be outside the default guidelines for number of tempdb files but if you are seeing tempdb GAM contention, more files will help.

I created DBA Dash. Hope you are finding it useful 🙂

5
  • Thank you for your response - My knowledge also dictates that SELECTs don't block other SELECTs. Hence the confusion on my side. It looks like first SELECT takes posession of the GAM page and does not let go for the subsequent selects. Your remarks are valid with caching and in long term, I'll try to push developers in this direction. In short term - I will try to experiment with TempDB filecount and learn more in this direction. P.S. DBA Dash absolutely rocks and you, sir, deserve utter respect and big thank you! Only thing I miss is alerting, but there are various ways.
    – Sranda
    Aug 3, 2023 at 9:42
  • 1
    Yeah the blocking will just be on waiting for the GAM pages in tempdb. It's probably not compile locks. Aug 3, 2023 at 9:56
  • Definitely also check the patch level of your SQL instances. If you are on a recent CU you should be good. If not there might be some fixes that significantly improve tempdb contention. Aug 3, 2023 at 10:00
  • 1
    On alerts DBA Dash doesn't have anything built in but this might be worth a read to provide a starting point for creating your own alerts dbadash.com/docs/help/alerts Aug 3, 2023 at 10:02
  • Thank you for the feedback provided. Both servers are SQL 2019 CU 21, so multiple TempDB files it is. Also the link to DBADash alerts is very helpful. Thanks a lot.
    – Sranda
    Aug 3, 2023 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.