what to do when we see multiple blocking chains?

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  February 28, 2015 at 1:52 pm

  #305038

  Hi All,

  How to troubleshoot blocking if u r seeing multiple blocking chains? Do I need to kill all the head blockers?

  What is the best approach to deal with multiple blocking chains?

  Thanks,

  Sam

• lmarkum

  SSCertifiable

  Points: 7985

  More actions

  February 28, 2015 at 8:11 pm

  #1780914

  vsamantha35 (2/28/2015)

  ---

  Hi All,

  How to troubleshoot blocking if u r seeing multiple blocking chains? Do I need to kill all the head blockers?

  What is the best approach to deal with multiple blocking chains?

  Thanks,

  Sam

  I would encourage you to consider capturing the queries, wait types, and query plans those SPIDs are running before you start killing sessions. This way you will know what kinds of transactions and applications you're affecting. For the long term, you can use that data to tune queries and or indexes in order to shorten up transactions and reduce your overall blocking.

• vsamantha35

  SSChampion

  Points: 11529

  More actions

  March 1, 2015 at 7:43 am

  #1780956

  Thank you. Will do that. We are seeing this very often.

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  March 1, 2015 at 5:11 pm

  #1781004

  vsamantha35 (2/28/2015)

  ---

  Hi All,

  How to troubleshoot blocking if u r seeing multiple blocking chains? Do I need to kill all the head blockers?

  What is the best approach to deal with multiple blocking chains?

  Thanks,

  Sam

  Some blocking isn't just normal, it's required. The question here is "How long does each blocking chain last"?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)