January 6, 2014 at 8:20 am
Right getting a mixed bag on this subject
Where should the MAXDOP be altered.
a) at server Instance level
B) Query Level.
Is it best practise to change the default value as a matter of course.
How to determine the optimal value if it is to be set at the server instance level.
Scenario 1 dual core VM machine....
What is the optimal MAXDOP?
Hope this will trigger a debate.:-):-P
January 6, 2014 at 8:51 am
This is a good read on MAXDOP and the associated CXPACKET wait type:
http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/
For what it's worth, we have a vendor application database set to a MAXDOP of 2 per their recommended settings. According to Microsoft documentation it should be set to 8. I talked to someone from the vendor and he more or less said they ignore most Microsoft recommendations. We did see a spike in CXPACKET waits on that server for a while and I floated out the idea of changing MAXDOP. We were told we would be "out of compliance" if we changed it and they wouldn't support their own application, therefore, it is still set at 2. The server is running well right now, so I'm not going to fight it.
January 6, 2014 at 9:00 am
Is there a reason you want to change MAXDOP or are you just looking for a best practice? Unless you are seeing CXPACKET waits (that are causing problems) I would leave it alone. If you are experiencing CXPACKET waits issues is it limited to a few queries or across the board? Generally I think the "cost threshold for parallelism" is too low out of the gate, but this will affect your entire instance, so unless you have a reason to change it I would leave it alone. If you are limited to a few queries having the issue you can experiment with changing the MAPDOP for those queries and see if that helps.
January 6, 2014 at 9:00 am
No, it's not a good idea to change it at the server level. Most of the time you get parallelism on queries that are not complex enough to justify it. Before I would ever adjust MAXDOP, especially on the server, I would adjust the Cost Threshold For Parallelism. The default value of 5 is a joke. Set it radically higher, say 50, then see how your queries behave after that. Disabling or limiting parallelism through MAXDOP is usually addressing issues that are better addressed by modifying 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 6, 2014 at 10:36 am
DWalker Thanks just read the article right before I saw your reply. Breaks it down very weel.
Keith - No reason other than best practise and knowledge growth, I do have one server with high CXPACKET waits.
Grant, I would fell safer just changing the TRESHOLD to 50 rather than the server wide MAXDOP setting.
At which point would you consider changing the MAXDOP from the default value.
Unfortunaetly it is mainly 3rd party apps, so I am unable to change the code without violating very poor support.
Thanks all
January 6, 2014 at 10:31 pm
I would change it immediately after installing SQL Server. The default value is just ridiculously low.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 7, 2014 at 7:58 am
How do I find the query cost in the cache plan.
January 7, 2014 at 9:56 am
Talib123 (1/7/2014)
How do I find the query cost in the cache plan.
If you hover over an element you will see Estimated Subtree Cost - that's what you're after I believe.
'Only he who wanders finds new paths'
January 7, 2014 at 10:58 am
What David said is accurate. The entire cost for the plan is stored in the first operator, SELECT/UPDATE/DELETE/INSERT.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 11, 2014 at 2:25 am
Thanks
January 11, 2014 at 10:51 am
Grant Fritchey (1/6/2014)
No, it's not a good idea to change it at the server level. Most of the time you get parallelism on queries that are not complex enough to justify it. Before I would ever adjust MAXDOP, especially on the server, I would adjust the Cost Threshold For Parallelism. The default value of 5 is a joke. Set it radically higher, say 50, then see how your queries behave after that. Disabling or limiting parallelism through MAXDOP is usually addressing issues that are better addressed by modifying the cost threshold.
According to msdn,
Cost Threshold For Parallelism:
The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration - http://technet.microsoft.com/en-us/library/ms188603(v=sql.105).aspx
Why do you think the value of 5 is a joke?
Regards,
IgorMi
Igor Micev,
My blog: www.igormicev.com
July 17, 2014 at 1:07 pm
Because a value of 5 tends to cause the parallelization of many/most queries which will interfere with the efficiency of your OLTP environment by not having enough CPU cores readily available for service requests.
Just because Microsoft has a default value for a server setting doesn't mean it's the appropriate or best value. Consider the 10% auto-growth on database files. It's universally considered to be a terrible setting.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply