Visualizing Deadlocks In SQL Sentry & Plan Explorer

Aaron Bertrand shows off new functionality in SQL Sentry and SentryOne Plan Explorer around deadlock visualization:

There’s a lot going on there, but much of it is noise. There is a whole bunch of contention on the table SqlPerf.Session — session 342 is trying to perform an update, but it is stuck waiting on shared locks taken by two services. Now, let’s check the Optimize Layout box above, and look at the circular graph again. Simplified, right?

This checkbox is easily the most powerful option to discard noise and help you focus on the crux of the deadlock issue. In the original graph, you can see that many of the elements presented are simply innocent bystanders — waiters that are captured as part of the deadlock activity, but in no way contributing to it. We can detect this in a lot of cases and so, when you check the box, we hide them from view, allowing you to focus much more directly on the key players involved in the deadlock. There is no question that eliminating the noise can really speed up troubleshooting; with those extra nodes removed, I can clearly see that I have some kind of order-of-operations issue on the SqlPerf.Session table, between the transfer service and the processor service.

Very cool.

Related Posts

Range Locks On Multi-Table Indexed Views

Erik Darling looks at the kinds of locks taken when updating an indexed view: So what causes Range Locks? Just ask Sunil. He knows everything (this assumes the serializable isolation level): Equality Predicate If the key value exists, then the range lock is only taken if the index is non-unique. In the non-unique index case, the […]

Read More

Blocking A Truncate Statement

Arun Sirpal shows that the TRUNCATE command needs to take locks like any other data modification command: The truncate option is fast and efficient but did you know that it takes a certain lock where you could actually be blocked? What am I talking about? When you issue a truncate it takes a Sch-M lock and […]

Read More

Categories

August 2018
MTWTFSS
« Jul Sep »
 12345
6789101112
13141516171819
20212223242526
2728293031