We have an interesting scenario. For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's living on that server. When looking at SQL Server for answers -- we found that we had many SUSPENDED connections and lots of BLOCKING, via the BLKBY column from sp_who2. To fix the issue, we would first save the sp_who2 output for later analysis, then kill all the blocked/suspended PID's. This apparently freed things up and transactions were able to continue flowing normally.
After analysis, we found 2 things:
- We were experiencing DEADLOCKS. Connection 1 was blocked by Connection 2, but Connection 2 was also blocked by Connection 1. SQL Server seemed unable to choose a victim so presumably queries started queuing up, waiting.
- The PID's for the deadlocked queries were from DIFFERENT DB's!
Not sure if this matters, but -- the issues were solved after looking at performance counters and seeing that we essentially were suffering from severe MEMORY PRESSURE (PLE, Lazy Writes, etc..) so we doubled the RAM and issues went away.
We are at a loss in understanding how queries from seemingly UNRELATED DB's can block each other. One theory is that both unrelated queries were waiting for the same PAGE in memory (cache)?? Any insight is welcome!