January 20, 2016 at 4:13 pm
Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL 2005 and indicates that some of the issues aren't fixed in SQL 2008.
Is there any indication that here are still problems with MAXDOP > 0 and are any DBAs out there not using 0 to reduce risks?
Leo
No problem is ever so complicated that with a little bit of effort it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 20, 2016 at 5:03 pm
I don't default to a non-zero value, but I don't really default to any particular value, unless the server has >8 cores, in which case I'll default to 8 (partially because customers love official documentation and it's something for which there's MS documentation I can point to, and partially because it's been my experience that queries do tend to scale badly with MAXDOPs past 8).
Even in that case, though, I prefer to test and figure out the best value for the particular workload in question. As Paul Randal points out at http://www.sqlskills.com/blogs/paul/maxdop-configuration-survey-results/, the boring truth is that SQL Server commonplace:
The key point when making any change for performance tuning is to test the change before putting it into production and work out which setting works best for your workload on your system – rather than blindly following 'this is the best way' rules for settings that people publish.
In other words, what should your MAXDOP be set to? It depends! 🙂
(emphasis mine)
Having said all that, I might be missing it, but I don't see in the Aaron Bertrand article you reference where he suggests generally setting MAXDOP=1. It's listed by MS as a workaround for each of those bugs, but I don't see it as a general recommendation anywhere.
Having said THAT, yes, there are still bugs around parallelism, even in much newer versions. See the discussion and links at http://www.sqlservercentral.com/Forums/Topic1750457-3077-1.aspx#bm1750490, for a recent example.
Cheers!
January 20, 2016 at 5:22 pm
Having said all that, I might be missing it, but I don't see in the Aaron Bertrand article you reference where he suggests generally setting MAXDOP=1. It's listed by MS as a workaround for each of those bugs, but I don't see it as a general recommendation anywhere.
Sorry just my way of interpreting this.
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 21, 2016 at 2:19 am
For the most part, when it comes to parallelism, your MAXDOP is not the issue. Instead, the issue is almost always around the Cost Threshold for Parallelism. The default value of 5 is way too low for both OLTP and DW workloads. The only exception is when you're trying to get parallel execution in support of a columnstore index and then, the cost threshold of 5 is too high. When worrying about costs and issues with parallel query execution, instead of sweating MAXDOP, first worry about the Cost Threshold.
"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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply