SQL Server blocking

Question

The attached query is causing blocking problems (root blocker) with other SQL statements. Can you please review the attached query and suggest If I need add/or remove indexes to prevent blocking. Please advise.

https://easyupload.io/zwcpgi

Answer 1

Hi @PraveenKumar,

Thank you for reaching out and welcome to Microsoft Q&A.

Here is detailed document on Resolve and Troubleshoot SQL Blocking chains with root sessions. In addition to what Erland says, you can also try to build a covering index to resolve blocking issue.

The link you provided shows me nothing, but I find a known issue about blocking and he posted the detailed query. Hope this thread can help you!

Feel free to share your issue here if you have any confusions.

Best regards,

Lucy Chen

---

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.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

The query could possibly run faster with an index on item_mandatory_condition_id, but that depends on how selective that column is.

To avoid that SELECT queries causes blocking, you could consider setting the database in READ_COMMITTED_SNAPSHOT. In this mode, SELECT queries read from a version store to get was committed when the query started. But this also means that they could be reading stale data. This can be a problem in triggers that validates data.