Block-Buster: SQL 2005 and Blocking/Deadlocking

If you'd like some help in going to sleep at night, try thinking about how concurrency is controlled behind the scenes in SQL Server. No guarantees however: you may wake up in the middle of the night having nightmares about blocking and deadlocking. Seriously though, the real nightmare would be the opposite... uncontrolled concurrency = sheer chaos

Many ways exist to prevent or eliminate blocking: isolation levels, hints, sp_indexoption, transaction boundaries, order of processing etc etc. Aside from the classics, if you are fighting blocking and deadlocking, SQL Server 2005 has a few things to offer:

1. Snapshot Isolation Level and Database Snapshots: If the blocking is a certain types of reader/writer conflict these are great ways to go.

2. Asynchronous Statistics Calculation: If the blocking is due to stats getting recomputed, this will help.

3. Statement Level Recompile: If the blocking was due to large stored procedures recompiling, this will certainly help.

4. Service Broker: If you are suffering from long transactions. SB can help you break things up into smaller chunks.

5. Online Index Operations: If you were being blocked by maintenance tasks (index rebuilds, defrags etc) online index operations can help increase concurrency.

6. Table and Index Partitioning: If you were being blocked by large data imports or data purging activities, partitioning can help eliminate blocking on large data movements by reducing the need for moving data.

All of these should have good explanation in recent 2005 books online but if you cannot find something in there, let me know.

Wishing you days without new-kids-on-the block =p. Sorry couldn't resist.