How to fix blockings in sql server?

Question

How to fix blockings in sql server?

Answer 1

Write better queries. Have better indexes so that queries run faster.

Answer 2

Hi @NN

Regardless of which blocking situation we are in, the methodology for troubleshooting locking is the same. Need to find the head blocker and identify what that query is doing and why it's blocking. Once the problematic query is identified (that is, what is holding locks for the prolonged period), the next step is to analyze and determine why the blocking happening. After we understand why, we can then make changes by redesigning the query and the transaction.

Steps in troubleshooting:

1. Identify the main blocking session (head blocker)
2. Find the query and transaction that is causing the blocking (what is holding locks for a prolonged period)
3. Analyze/understand why the prolonged blocking occurs
4. Resolve blocking issue by redesigning query and transaction

Here is detailed Microsoft document on Understand and resolve SQL Server blocking problems

Resolve and Troubleshoot SQL Blocking chains with root sessions

Troubleshooting blocking- https://www.mssqltips.com/sqlservertutorial/253/troubleshooting-blocking/

Please find similar thread and blocking queries- https://learn.microsoft.com/en-us/answers/questions/1180235/blocking-gives-me-wrong-impression-in-this-scenari

Thank you!

Answer 3

Hi @NN

You can also refer to this document.

https://www.red-gate.com/hub/product-learning/sql-monitor/troubleshooting-blocking-sql-server-using-sql-monitor

Best regards,

Percy Tang