Sql-server – How to a connection for one DB block a connection to another DB in SQL Server

deadlockperformancesql serversql-server-2008troubleshooting

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!

Best Answer

So there's a couple of ways that this can happen.

  1. The queries in the blocking database reference objects within the blocked database.
  2. The queries are waiting for tempdb to allocate pages and one query is blocking another query.
  3. There is memory pressure and queries are waiting for memory to be allocated so they have to wait their turn in the memory allocator.

If you download and put sp_whoisactive on the server it'll give you a lot more information about why things are blocking if this happens again.

Given your note about the memory pressure you probably were suffering from either #2 or #3 on my list above. Odds are it was memory not being able to be allocated fast enough, but it's possible that because you didn't have enough memory stuff was spilling to tempdb faster than tempdb to do "stuff".

Related Question