Last week, I was fortunate enough to be on SQL Skills’ Immersion Event on Performance Tuning and Optimization Part 1 with Paul S Randal and Kimberly L Tripp. I’d highly recommend the course, which was excellent albeit a little tiring with five full in depth days diving into SQL Server’s internals.
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.