How do you use cloud databases? Take the survey.

Long-running monitor query creating tempdb locks

I am running SQL Monitor and I am encountering an issue similar to what is described at:

I am not getting deadlocks but the "block_cte" query runs for several minutes and is occasionally causing latch waits on tempdb and blocking other sessions in production.  Killing the session resolves the blocking issue, as expected.

Any suggestions?


  • Alex BAlex B Posts: 1,118 Diamond 4
    Hi @JPennAsembia,

    Can you share what resources it's blocking on and/or any information on the queries/processes that are being blocked?  Are you getting an alert in SQL Monitor for it by chance and if so can you share the information from the Details and Processes tabs of the alert?

    If you need I can get in touch via a support ticket to collect the information.

    I'll need to get the information to the team to see if there's anything we can do regarding it.

    Kind regards,
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • Hi Alex-

    I haven't seen any more blocking while this query runs (since I reported this).  However there are occasional executions that take over 4 minutes to complete.  Running sp_whoisactive shows the following for one such run:

    CPU: 131,204
    tempdb_allocations: 306,128
    Reads: 16,914,597
    Writes: 301,309
    Used memory: 9,775,126

    Is this expected?
  • Hi @JPennAsembia,

    Since you aren't seeing continuous blocking or deadlocking and it is only occasionally taking longer to run I believe this is working as intended. There isn't a particular expected set of statistics as it depends on what is happening on your instance. The only unusual thing with this sampler that we've seen recently is a large memory grant, which is in the backlog to look into further.

    If any of that changes, do let me know and if you can provide any more information on the blocking or locking that is occurring.

    Kind regards,
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.