Troubleshooting sql issue when multiple spids are involved

Question

Hi All, We have an application that spawns multiple spid's, runs multiple multiple batches , multiple txns. While troubleshooting a BLOCKING, initially thought to trace one spid and collect the sql  statements for that session. But eventually, dev team and we saw multiple spid's are spawned as part of that process and even multiple databases are involved(in this case two databases). how to troubleshoot such blocking problem to tell the cause of the blocking, especially when multiple spids & db's are involved? I find it very difficult to tell a story story. how do you approach such blocking problem when they have long running processes and multiple spids are involved? What questions we should ask ourselves and app team to debug such perf issues? Looking for general approach. Thank you. Regards, Sam

Answer 1

I typically use my own beta_lockinfo for those situations. It can show you the lock chains and who is blocking whom. You will need some understanding of locking etc in SQL Server to make sense of the data.

And, not to neglect: you also need to understand your own application.

Answer 2

Hi @Samantha r To gather blocking information, there are two complimentary methods.

The first is to query dynamic management objects (DMOs) and store the results for comparison over time. Some objects referenced in this article are dynamic management views (DMVs) and some are dynamic management functions (DMFs).

The second is to use Extended Events(XEvents) or SQL Profiler Traces to capture what is executing. Since SQL Trace and SQL Server Profiler are deprecated, this troubleshooting guide will focus on XEvents.

Please refer to this doc for more details: Understand and resolve SQL Server blocking problems.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.