Peculiar Blocking Issue

Question

On our production server, SQL 2005 SP2 on Win 2003 EE SP2, we have had a peculiar blocking issue occur twice now, and the only way we could resolve the situation was to restart SQL Server.

What happens is that a chain of blocked processes occurs, but killing the head of the chain does not resolve the issue, instead the next process in the chain takes over the head of the chain. We thought that it might have been due to an orphaned DTC transaction but we checked sys.dm_tran_locks for a request_session_id = -2 and found nothing.

Any ideas as to what might be causing this, or how to track it down? Please note that this is production and restarting SQL Server is problematic, so too is running trace flags on the server itself. If we have to run a trace it would have to be done remotely and the data stored remotely.

Any help or advice is greatly appreciated.

---

Michael MacGregor, Senior SQL Server DBA, Carlson Marketing

Answer 1

Hi Michael,

   When you find the chain blocking next time please look at the resource in contension.  Without that you cannot evaluate what is the real issue.  If restarting is fixing the issue you might have a DISK I/O or Memory I/O issue.  Possibly look at your KPI for Disk and Memory to see if you notice any pressure.  You can execute following query to find out which resource is causing the block.

SELECT * FROM sys.dm_exec_requests

Look at the wait_resource column; for example you might see PAGE:6:1:727 there.  If nothing it is in waiting resource lok at the WAIT_TYPE.

Thats the first step.... Cheers

---

Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/

Answer 2

Are these connections doing similar work? You maybe should check the input buffer of these header connection to see what they are doing.

Answer 3

You would need to let us know what kind of blocking are you observing: lock blocking or disk io waits etc. Can you run the following script to capture blocking information: http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx Once that is done you can import the captured data into a SQL Nexus database and check for the blocking chains: Reference: sqlnexus.codeplex.com For us to help you further you would need to tell us what operation is the head blocker performing and if it is waiting on something. And what are the blocked spids waiting on. HTH

---

This posting is provided "AS IS" with no warranties, and confers no rights.

---

My Blog: http://troubleshootingsql.com
Twitter: @banerjeeamit
SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq

Answer 4

We have opened a ticket with Microsoft and are monitoring the server with PSSDIAG. If and when we figure out what the issue is I will post an update, but so far the issue has not occurred again.

---

Michael MacGregor, Senior SQL Server DBA, Carlson Marketing