I wrote sp_BlitzLock while drinking (okay, drunk) on a plane from Chicago to Seattle with Brent. We were headed to PASS in 2017 to teach a precon that I had to pay $500 to get into.
This might be my favorite procedure to talk about, even though the contents are almost my least favorite to analyze.
It calls out two kinds of deadlocks:
- Regular deadlocks from queries fighting
- Parallel deadlocks from parallelism being insane
For regular deadlocks, much of the time I end up talking about how many of the queries involved are selects, and how enabling RCSI will fix the problem across the board without changing queries or indexes.
God bless RCSI.
For parallel deadlocks, this is when I get to tell folks that their parallelism settings are all amok (or at the defaults, yuck) and that’s why 300 queries fail a day.
Make It Weird
By detfault, sp_BlitzLock will look at the system health extended event session. Most everyone in the SQL Server running world has that available, logging deadlocks.
Why doesn’t it also log blocking? Great question, if you want to end up in an orange jumpsuit with your head in a sack.
But it can also parse and analyze any extended event session that’s capturing deadlocks.
EXEC sp_BlitzLock; --goes to system health EXEC sp_BlitzLock --goes to another session @EventSessionPath = 'P:\PutSomethingHere*.xel';
There are many things I’d like the extended event XML to do better:
- Sometimes the queries it shows being involved aren’t the ones that deadlocked
- Sometimes it will show you the database id and object id of a procedure instead of resolving them to give you a readable name
- Sometimes it will cut off query text so it’s even harder to figure out what went wrong
A big thing missing is the query plan, too. Most of the time this will illustrate quite nicely what the deadlock cause was:
- Foreign keys
- Missing indexes
But this is still the best way I’ve been able to come up with to deal with something as dull as deadlocks.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly.