Parallelism In SQL Queries Causing Deadlocks

July 28th, 2008 by Xerxes Leave a reply »

This is really just a stub post, and should force me to come back and add more detail to the topic.

In a nutshell, be careful if you have parallelism turned ON for your multi-core SQL server as SQL’s internal worker threads can seemingly jam themselves into a deadlock scenario when executing a parallelised (sp?) query.

to disable parallelism, use the OPTION (MAXDOP 1) query hint.

SELECT col1, col2, col3
FROM table1 t1 INNER JOIN table 2 t2 on t1.id = t2.id
WHERE SomeCondition = SomeValue
OPTION (MAXDOP 1)

This will force SQL Server to run the query with a max parallelism of 1 (ie: single thread). Good-bye locks!

Some references:
Query Hint (T-SQL)
Appropriate Uses of Parallelism in SQL Server
Detecting and Ending Deadlocks
Deadlock Troubleshooting (Part 1)

Be Sociable, Share!

Related posts:

  1. SQL Index Fragmentation The great SQL Performance saga continued today, taking a turn...
  2. NRL Tipping Comp 2008 Comp Name: Rex’s NRL Tipping Comp 2008 Comp #: 121315...
  3. The day the UNION saved my bacon Sounds weird, right? Not quite :)   Imagine if you...
  4. Deadlocked transactions and Junctions Wow lots has happened in the last 4 hours. Where...