August 13, 2012 at 1:46 am
Good Day,
according to Microsoft Best Practices Analyzer we should set the Max Degree of Parallellism to an optimal value. I read that it is better to leave it at the default value for SQL Server 2008R2 . What should we use as a guideline ? Our server don't have more than 8 processors on a vm . Any ideas ?
August 13, 2012 at 2:07 am
Welcome to the 'everyone has an opinion and none of them agree' topic. 🙂
Firstly don't set it to 1, that's an old 'best practice' that's nothing of the sort.
Is the DB mostly OLTP or mostly OLAP/DW?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 2:08 am
Gila, It is OLTP
August 13, 2012 at 2:15 am
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 2:33 am
GilaMonster (8/13/2012)
Welcome to the 'everyone has an opinion and none of them agree' topic. 🙂
Not the only one, honestly. 🙂
Just to add my $0.02, if you're on Enterprise Edition, resource governor can help you set this option in a more granular way, rather than choosing a max DOP for the whole server.
-- Gianluca Sartori
August 13, 2012 at 2:55 am
Gila,
thank you for the e-mail . Unfortunately we have no budget to procure the book . Could you please mail me the comments regarding this setting ?
August 13, 2012 at 3:04 am
lianvh (8/13/2012)
Gila,thank you for the e-mail . Unfortunately we have no budget to procure the book .
I assume you didn't bother to look at the link, because the e-book is available free.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 13, 2012 at 4:09 am
Thanks, Gila,
I managed to download the frre book.
August 13, 2012 at 8:20 am
In addition to setting the Max Degree of Parallelism, make sure you change the Threshold for Parallelism setting too. The default value of 5 is way too low. I'm not sure what Jonathan says (and I probably wouldn't disagree with him) but I'd start at a value of 25 and then work from there.
"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
August 13, 2012 at 4:39 pm
Actually, setting the max degree of parallelism to 1 is the optimal setting in some cases. You need to determine the best setting for your sql workload. Ideally you have a test environment where you can change this setting and capture perfmon data for some time (or use a better monitoring package but you said that you do not have the budget so I am assuming that option is out). Look for a high number of cxpacket waits in perfmon. Your optimal max degree of parallelism setting is the one that results in the minimum number of cxpacket waits but does not reduce your query execution times to a crawl. It takes some time to get to the magic number and at some point you will need to re-do the whole process again as your workload will change over time. When you do have some budget options you should invest in a db monitoring package as this will take thw rok out of this tedious task. I discuss cxpacket waits and how they relate to the max degree of parallelism on my site.
The below msdn article discusses this approach
[font="Courier New"]Michael Connolly
[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply