June 13, 2011 at 12:56 pm
Plus, there is no one, perfect answer. So you set the MAXDOP to 1 and "fix" the CXPACKET problem, but now you've got queries that are taking forever because they used to benefit from multiple processors. It just goes round & round. The one thing I can say for sure, most systems benefit from having a higher cost threshold for parallelism. After that, you get into a dance where you have decide if killing it for the server is adequate or if you can use hints to control one or two outlier queries. Again, no answer covers all cases or all situations or all permutations. No answer is applicable forever. You can fix the problem with setting the cost threshold and then in a year or two the data sets have grown sufficient that the problem either resurfaces or you find that you need to lower the threshold in order to get parallelism back.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 26, 2011 at 1:40 pm
Grant Fritchey (6/10/2011)
It's not automatic that the query will generate a parallel plan. The storage engine makes that determination, not the optimizer.
The storage engine? Are you sure about that?
--
Adam Machanic
whoisactive
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply