Intra-Query Parallelism | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Intra-Query Parallelism

I had a developer come to me with the following error message query – select vw_feed_search.* from vw_feed_search Where vw_feed_search.fedd_status_id = 3 AND vw_feed_search.date_in between ‘2/16/2006’ and ‘6/24/2006’ ORDER BY vw_feed_search.date_in asc Error Intra-query parallelism caused your server command (process ID #%d) to deadlock.
Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). I found article 837983 which describes the problem and give you a couple of different methods for work around. It mentions that you can modify the ‘max degree of parallelism’ to 1 via SP_Configure. I went a head and did that and the query now works, but I’m worried about the adverse affects if I do this in produciton. Any help would be greatly appreciated. Thanks Susan
as general rule for SQL 2000, treat parallelism as broken / not good except where known to work with gain you are probably better off leaving max degree .. to 1 during the day if you do maintenance ops like reindexing at night, turn it on just for that