Long-running monitor query creating tempdb locks
JPennAsembia
Posts: 6 Bronze 1
I am running SQL Monitor 11.0.12.4383 and I am encountering an issue similar to what is described at:
https://forum.red-gate.com/discussion/87368/deadlocks-from-sql-monitor-queries-after-upgrading-to-10-2-9-1104
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?
https://forum.red-gate.com/discussion/87368/deadlocks-from-sql-monitor-queries-after-upgrading-to-10-2-9-1104
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?
Tagged:
Answers
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,
Alex
Have you visited our Help Center?
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?
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,
Alex
Have you visited our Help Center?