We are going to look at a blocking pattern that I am calling the Sneaky Blocker.
As a consultant, I wasn’t always at the keyboard when blocking struck
You know, sometimes your alert comes out and says, “Hey, there’s blocking on the SQL Server,” but you just aren’t there.
Now if you’re a full time DBA, you’re stuck in a lot of meetings, this is a great excuse when that alert comes in to be like, “Gotta go! Problem happening live.”
But if it’s a long trip back to your desk, you’ve got the same problem I have.
You wanna have a way to get details about the queries that are running
Because that super simple blocking alert we set up is super simple. It just says hey, blocking’s happening right now. It doesn’t capture who’s blocking who or how many queries are being impacted by it.
Now I’m not gonna lie. There are a lot of really cool, mature monitoring tools out there for SQL Server that will do things like let you go back in time and say:
“Hey, at three PM last Wednesday, what was running in my SQL Server? Was there blocking?”
Even, “What are the execution plans for everything that was happening?”
But we can’t buy monitoring tools for every SQL Server in every environment
So it’s often super useful to have a built in tool in SQL Server that will create a bread crumb trail when blocking happens and help us see in the past what blocking was happening and what was involved.
Let’s take a look in a demo in SQL Server.