Look at some of the graphs produced by the intra query parallelism deadlocks, below.
Bart Duncan explains this “phenomenon” (bug, rather) in this article.
The two solutions to this problem are:
- Adding or tuning an index in order to reduce the cost of the query and hence, eliminate parallelism
- Using “OPTION (MAXDOP 1)” query hint to force-stop the query from parallelising
In some instances, depending on the optimization level for the query (specifically, TIMEOUT), you may be able to influence the execution plan by changing the order of the joins.
Would be interested to know if any of you have encountered this scenario and what you did to resolve the issue.
Blake Bishop says
I ran into this issue in a big way (with similarly beautiful deadlock graphs….) and ended up setting the server’s MAXDOP=1. After trying to optimize indexes and force query hints at the cached query plan level (we were not able to modify the inbound queries coming from the web services hitting the database), we determined that, for the workload we were trying to support, we did not need parallelism enabled on the server. This immediately solved our problems.
Me says
Almost as bad is what you see in a deadlock graph for a Java app that uses connection pools and the app forgets to commit explicit transactions. The same connections are shared across multiple databases and the deadlocks involve dozens of sessions and objects in dozens of databases.
Willowisp says
The “Cost Threshold for Parallelism” server option is worth considering too.
Tuning this option could prove particularly useful where the software vendor restricts changes to indexes or queries.
References:
https://technet.microsoft.com/en-us/library/ms190949(v=sql.110).aspx
http://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
Steve says
only seen it once – fixed with a maxdop(1) query hint.
Might have had a support case though (it was 9 years ago)
Ayman El-Ghazali says
Beautiful! Hopefully this doesn’t encourage poor programming practices 😛
mark says
Pretty