SQL Server Community Tools: How I Use sp_BlitzLock

Posted on by Erik Darling

Groovy Baby

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:

  • Lookups
  • Foreign keys
  • Triggers
  • 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.

Dutifully.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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. You can also get a quick, low cost health check with no phone time required.

Related Posts