SQLYSE

Everything SQL

  • About Me
  • Blog
  • Contact

CONNECT

  • E-mail
  • Linkedin
  • RSS
  • Twitter

Work of art – the intra query parallelism deadlock graphs

11th September 2015

Look at some of the graphs produced by the intra query parallelism deadlocks, below.

post132_graph1
 
post132_graph6
 
post132_graph10
 
post132_graph9
 
post132_graph8
 
post132_graph7
 
post132_graph5
 
post132_graph4
 
post132_graph3
 
post132_graph2
 
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.

Comments

  1. Blake Bishop says

    14th September 2015 at 5:07 pm

    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.

    Reply
  2. Me says

    14th September 2015 at 5:08 pm

    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.

    Reply
  3. Willowisp says

    14th September 2015 at 5:50 pm

    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/

    Reply
  4. Steve says

    14th September 2015 at 6:13 pm

    only seen it once – fixed with a maxdop(1) query hint.
    Might have had a support case though (it was 9 years ago)

    Reply
  5. Ayman El-Ghazali says

    15th September 2015 at 12:51 pm

    Beautiful! Hopefully this doesn’t encourage poor programming practices 😛

    Reply
  6. mark says

    15th September 2015 at 4:03 pm

    Pretty

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *