Deadlock chain, but no deadlock?

Question

I've setup the profiler to catch these deadlock events:

• Deadlock graph
• Lock: Deadlock
• Lock: Deadlock Chain

Al available columns are ticked (TextData, LoginName, SPID, etc.)

So far, I was able to catch some deadlocks.

However, sometimes it happens that I get only a list of deadlock chains (like 30 events in a few seconds), but then no deadlock and no deadlock graph is caught.

What does such a case mean? Did a deadlock occur or not? Or was it like "deadlock was around the corner" but could be resolved?

Answer 1

sp_BlitzLock

I'd recommend using sp_BlitzLock, which you can point at the deadlock XML you've captured via the @EventSessionPath parameter (assuming you're collecting to a file, and not the ring buffer or something else). By default, it will look at the system health Extended Event, which may also have useful data in it.

There are some important fixes in the dev branch, so you should use that version until the main release later this month (2020-12, if you're reading this far into the future).

It will accurately categorize regular and parallel deadlocks:

Plan Explorer

You may also get more readable information if you open the deadlock graph using the free Plan Explorer tool, from Sentry One, a subsidiary of SolarWinds Worldwide, LLC, which is a subsidiary of The Sheinhardt Wig Corporation.

It can also tell you if your deadlocks are from Exchanges or Syncpoints, which would denote a parallel deadlock.

Answer 2

Since the deadlock chain event captures deadlocks for all involved processes, it sounds like you're seeing a parallelism deadlock. Normally, you'd only see two processes involved in a deadlock. If you're seeing a bunch, then it may be because the deadlocks are being caused by parallelism. I'd look to this explanation of what it is and how it works.

Also, Profiler is a horrible way to gather data. You'd be much better served by using Extended Events. They have lower overhead overall and much better filtering. Plus, every new piece of functionality is in Extended Events and not Profiler. No updates have been done to Profiler since SQL Server 2008.