June 5, 2014 at 12:31 am
Hi all
We are implementing Dynamics AX and during the development/testing phases we were advised to set MAXDOP to 1. Now I get that in transactional systems this can be advantageous; I also get that alternatives such as raising the cost threshold for parallelism are often not looked into (I have read all the recommendations!!!).
Now in this case, when we were advised this there were no CXPACKET waits within SQL so it begs the question; if we are seeing no CXPACKET waits at all, what are we actually going to gain by setting MAXDOP to 1. What I am up against is...its best practice! Which, I've had before 🙂
As always, many thanks.
D
'Only he who wanders finds new paths'
June 5, 2014 at 1:00 am
Microsoft recommends it: http://technet.microsoft.com/en-au/library/dd309734.aspx
But also recommends that you change it to 8 during batch jobs or maintenance & upgrades.
To me, that sounds like MAXDOP 0 could be used and the cost threshold could be increased instead.
June 5, 2014 at 4:46 am
You're seeing no CXPACKET waits at all? That's a little abnormal in itself. Some of those waits are sure to bubble up in most systems. They're not any kind of a problem on their own, just an indication that there is parallel activity going on. Regardless, some best practices are... well... a path of least resistance. It's easier to advise all customers to set maxdop to 1 instead of telling them to adjust their cost threshold for parallelism to a particular value, then monitor performance and make determinations if some additional changes to maxdop to a lower value allowing for some cpus for the server and some for sql server as well as possibly exploring setting affinity on those cpus, more monitoring, rinse and repeat. So, a "best practice" is established that easy, but isn't really necessarily the best thing to do for your server.
"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
June 5, 2014 at 5:07 am
Thanks for the replies guys.
No CXPACKET waits whatsoever, tiny little IO waits but that is about all. Its not the first time I have come across 'best' practices in this kind of situation 🙂
Thanks again
D
'Only he who wanders finds new paths'
June 5, 2014 at 5:33 am
I have an app where the vendor recommends (nay insists) setting maxdop to 1 and cost threshold to 15, go figure.........
Is Dynamics AX a microsoft product, they have a habit of recommending maxdop = 1 (sharepoint for instance)
---------------------------------------------------------------------
June 5, 2014 at 6:57 am
george sibbald (6/5/2014)
I have an app where the vendor recommends (nay insists) setting maxdop to 1 and cost threshold to 15, go figure.........Is Dynamics AX a microsoft product, they have a habit of recommending maxdop = 1 (sharepoint for instance)
Lovely. Turn off parallelism, but adjust the cost threshold because.... uhm.... someone on the internet said it was a good thing. If I didn't work for a vendor I'd go off on a rant about vendors.
"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
June 5, 2014 at 8:55 am
george sibbald (6/5/2014)
I have an app where the vendor recommends (nay insists) setting maxdop to 1 and cost threshold to 15, go figure.........Is Dynamics AX a microsoft product, they have a habit of recommending maxdop = 1 (sharepoint for instance)
1) MAXDOP 1 is actually a very good recommendation FOR WELL TUNED OLTP APPLICATIONS WHERE NO REPORTING IS BEING DONE AND ONLY THAT APP LIVES ON THE SQL SERVER. Note that is a VERY specific set of circumstances for which in 15 years of consulting I don't recall seeing many (any??) apps/servers that meet the bar.
2) Your specific scenario (MAXDOP 1, CTFP increased to 15) actually addresses an exception to 1) above. That is where reports or larger-costing statements ARE being run and they explicitly hint OPTION (MAXDOP 'somethingOtherThanOne'). Now those 'big hitters' can parallelize but only when they are actually bigger than the horribly-inadequate CTFP default of 5.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2014 at 9:09 am
TheSQLGuru (6/5/2014)
george sibbald (6/5/2014)
I have an app where the vendor recommends (nay insists) setting maxdop to 1 and cost threshold to 15, go figure.........Is Dynamics AX a microsoft product, they have a habit of recommending maxdop = 1 (sharepoint for instance)
1) MAXDOP 1 is actually a very good recommendation FOR WELL TUNED OLTP APPLICATIONS WHERE NO REPORTING IS BEING DONE AND ONLY THAT APP LIVES ON THE SQL SERVER. Note that is a VERY specific set of circumstances for which in 15 years of consulting I don't recall seeing many (any??) apps/servers that meet the bar.
2) Your specific scenario (MAXDOP 1, CTFP increased to 15) actually addresses an exception to 1) above. That is where reports or larger-costing statements ARE being run and they explicitly hint OPTION (MAXDOP 'somethingOtherThanOne'). Now those 'big hitters' can parallelize but only when they are actually bigger than the horribly-inadequate CTFP default of 5.
good point but the question as to why is met with silence............
There are other recommendatons which reek of throwing the results of white papers at it.
Oh and they want sysadmin for the app account 🙂
---------------------------------------------------------------------
June 5, 2014 at 9:58 am
david.alcock (6/5/2014)
Thanks for the replies guys.No CXPACKET waits whatsoever, tiny little IO waits but that is about all. Its not the first time I have come across 'best' practices in this kind of situation 🙂
Thanks again
D
With Dynamix AX, it really is advantageous to have maxdop set to 1. Once the system starts getting busier it will have an impact. I have seen it numerous times on AX.
That said, I still like to test different settings. Even with maxdop set to 1, I still adjust the Cost Threshold to 75 or so.
As for the CXPacket waits, I am really surprised by that. I imagine this could be a result of low activity at the moment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2014 at 10:32 am
Yes for AX then MAXDOP = 1 is the recommended setting however with the lack of CXPACKET waits and because we aren't really in the production/transactional stages of the implementation then I did wonder if its potentially hindering performance.
As with most things in SQL I find the answer is more often than not 'it depends' and tweaking and measuring is called for rather than just applying a recommended setting.
Thanks as always everyone 🙂
D
'Only he who wanders finds new paths'
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply