1

We found a scenario where a large delete in one table causes hanging across seemingly unrelated transactions and tables in the same the database (but not other databases on the same server). The good news is, using a combination of spwho2 and other commands, we found the query that causes trouble. This was all trial and error - and shutting things down until the symptoms subsided - but still we do not know the precise cause that leads to cascading blocks and timeouts - such as the exact lock or resource contention.

I started digging into this and got as far as enabling the "blocked process threshold" (set at 10 secs) and using a nice little util to parse the trace file: https://michaeljswart.com/2011/04/a-new-way-to-examine-blocked-process-reports/

We recreated the problem, and sure enough, we see plenty of "lead blockers" and then transactions that are waiting - but none of them are pointing to the large delete that we know to be the true cause. In other words, the lead blockers in the the blocked process report are likely victims themselves - so I am looking to connect the dots with the right diagnostics.

Any advice? As I am not a full time DBA, I am weighing a few things: Hiring a DBA to consult and evaluate, try a tool like SQL sentry, and stop here to talk to a community of pros where I might find some new quick leads.

Happy to share more info.

Thanks.

Improve this question
3
  • 1
    Are you seeing any transactions waiting to write to the log? My guess is your large delete is causing contention on the transaction log. That wouldn’t really make it a lead blocker, though the effect is essentially the same.
    – Brendan McCaffrey
    Commented Oct 13, 2022 at 16:09
  • What does sp_WhoIsActive have to say about blocking when you run your DELETE statement? Any foreign keys defined with that table?
    – J.D.
    Commented Oct 13, 2022 at 17:40
  • From the dup "@Charlieface - hey Charlie - we have synchronous mirroring on - and last night I was able to see a bunch of threads waiting on DBMIRROR_DBM_EVENT I'm guessing this creates some kind of single file line - all parallel transactions in the DB need to wait? It would appear that way. This is plenty to go on... spWhoIsActive FTW"
    – David Browne - Microsoft
    Commented Oct 16, 2022 at 15:19

0

Reset to default

Your Answer

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

Browse other questions tagged or ask your own question.