If you have a copy of the StackOverflow2013 database, this query should produce a parallel deadlock.
SELECT c.Id, c.UserId, c.CreationDate, c.PostId, c.Score INTO #ohno FROM dbo.Comments AS c WITH (TABLOCKX);
If you want an easy way to track down deadlocks, I can’t recommend sp_BlitzLock enough.
It doesn’t render the deadlock graph for you, but it does get you the deadlock XML, which you can save as an XDL file.
For viewing them, Sentry One’s Plan Explorer tool is way better than SSMS. It doesn’t just explore plans — it also explores deadlocks.
The way it’ll look is something like this:
You’ll see the exchange event, and you’ll also see the same query deadlocking itself.
This is an admittedly odd situation, but one I’ve had to troubleshoot a bunch of times.
You might see query error messages something like this:
Msg 1205, Level 13, State 18, Line 3 Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Options For Fixing It
If you start running into these, it can be for a number of reasons, but the root cause is a parallel query. That doesn’t mean you should change MAXDOP to 1, though you should check your parallelism settings to make sure they’re not at the defaults.
You may want to try setting the query you’re having a problem with to DOP 1. Sure, performance might suffer, but at least it won’t error out.
If that’s not possible, you might need to look at other things in the query. For example, you might be missing a helpful index that would make the query fast without needing to go parallel.
Another issue you might spot in query plans is around order preserving operators. I wrote a whole bunch about that with an example here. You might see it around operators like Sorts, Merges, and Stream Aggregates when they’re surrounding parallel exchange operators. In those cases, you might need to hint HASH joins or aggregations.
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 performance problems quickly.
- Software Vendor Mistakes With SQL Server: Not Getting Parallel Inserts Into #Temp Tables
- Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server
- Table Partitioning And Parallelism In SQL Server Standard Edition
- How Useful Is Column Store Indexing In SQL Server Standard Edition?