Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
1  
Tempdb contention anyone? –  Max Vernon Feb 28 at 19:10

1 Answer 1

up vote 1 down vote accepted

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".

share|improve this answer
    
I think #3 was the issue. Filled up the RAM, PLE skyrocketed, Lazy Writes decreased, etc... and blocks have been fine since. –  tresstylez Apr 3 at 3:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.