Viewing block chains on SQL Server using DMVs and a recursive cte

Locks and subsequently blocks are an integral part of a database in order to ensure that data integrity is met. However, if a process is blocked for any significant period of time, then this has a detrimental effect on your application’s performance.

I find that it’s rare that there’s a single process being blocked; usually if a block is occurring then it will be a bunch of processes, and these in turn may end up causing subsequent blocks: before you know it, we have a whole swathe of blocked processes, and application that’s hanging and users complaining left, right and centre.

Handily, SQL Server has procedures and DMVs that show us which process is blocking any given process. Unfortunately, in a big block chain, this means you have to piece all this together to find the root cause of the block chain and then make a decision on how to deal with the problem.
Continue reading