You keep getting warnings and emails about deadlocks, but let's be honest: you're not really sure how they happen or what to do about it. In this one-hour session from the SQLBits conference, I will show one, use sp_BlitzLock to analyze it, then fix it.
https://www.youtube.com/watch?v=3EwDn9hqgkg
In SQL Server Management Studio, plain ol' deadlock graphs look like this:
BOOOOO-RING. That's why I prefer opening deadlock graphs in SentryOne Plan Explorer, which presents a much better visual in the form of a circle...a circle, let's just stop there:
When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic's excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it's way more powerful. The documentation is extensive -…
This week, we're all about tuning indexes. So far, we've covered Brent's 5 and 5 Rule and The D.E.A.T.H. Method. Today, let's talk about reducing blocking and deadlocking. Normally, when we think about the causes of blocking or deadlocks, we use badly written queries that modify different tables in the wrong order, resulting in a Mexican…
Watch in awe and wonder as I create a deadlock, then use sp_BlitzLock after the fact to show you which queries and tables were involved:
https://youtu.be/IR7rgByP8BI
Yes, especially when they're part of a larger transaction.
In this example, I'm going to use a database in Read Committed Snapshot Isolation (RCSI), my favorite isolation level for building new applications from the ground up. Optimistic concurrency (aka MVCC) helps avoid a lot of blocking issues - but not all of them.
People seem to care about deadlocks
That's why I wrote sp_BlitzLock. Why not sp_BlitzDeadlock?
Well, I had this song stuck in my head. But enough about that.
Deadlocks are hard - especially when there are a lot of them. When there are dozens or more, looking at each graph is a pain.
sp_BlitzLock gives you a slice-and-dice analysis of which tables, indexes, and queries are involved in most of your deadlocks so you can quickly get to the root cause.
