July 17, 2014 at 8:07 am
We are running SQL server 2008 R2 standard edition on 4 CPU cores and our Scheduler report shows even utilization over all cores. It has been in production over a year and has been performing well all that time with no issues.
An external vender has asked us several times to set MAXDOP = 1
They even made this statement when our CIO said no.
"We do run sql server 2008 R2 enterprise edition in our data centers and our DBA team has found that this had significant performance improvement. These are 40 core systems with a 1 TB RAM on them."
I would like any comments from anyone about what I have posted here.
July 17, 2014 at 8:42 am
That's great for the vendor. But that is an apples to oranges comparison coming from them.
Is the workload high OLTP? Or do you have you noticed parallelism being of benefit?
The only time I set Maxdop to what the vendor recommends is when it deals with Sharepoint or Dynamics AX.
Otherwise, I base that setting off the workload we have noticed and where we get the best performance.
In theory, if the workload is pure OLTP maxdop 1 should be good.
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
July 17, 2014 at 8:49 am
SQLRNNR (7/17/2014)
That's great for the vendor. But that is an apples to oranges comparison coming from them.Is the workload high OLTP? Or do you have you noticed parallelism being of benefit?
The only time I set Maxdop to what the vendor recommends is when it deals with Sharepoint or Dynamics AX.
Otherwise, I base that setting off the workload we have noticed and where we get the best performance.
In theory, if the workload is pure OLTP maxdop 1 should be good.
SQLRNR - Well when I mentioned that the maxdop = 1 setting is good for pure OLTP issues where the CXPACKET waits are high. We agreed that they have nothing but Buffer I/O waits. Their app is mostly OLTP, but has a single 42GB file that they do not want to grow. Their main tables that are doing the main OLTP have millions of rows.
July 17, 2014 at 8:51 am
A good response for this vendor would be to Smile and Wave.
No need changing a setting when it likely won't improve anything. Not under the described circumstances.
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
July 17, 2014 at 8:52 am
PHYData DBA (7/17/2014)
SQLRNR - Well when I mentioned that the maxdop = 1 setting is good for pure OLTP issues where the CXPACKET waits are high.
Not even then.
CXPacket waits just indicate that queries are running in parallel. 'High' values, for whatever the definition of high is, doesn't mean maxdop 1. It may mean increase cost threshold a little and tune expensive queries. At most set maxdop to cores/numa node. But not 1, not unless you're running sharepoint or dynanics.
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
July 17, 2014 at 9:00 am
We smiled and waved, they literaly bit the waving hand.
Then after showing them some information from Paul Randal, and from some Issues with a WellPoint Datawarehouse.
They asked us who is Paul Randal, who is Well Point.
This vender supplies a EMR application to millions and their performance expert has never heard of Well Point.
July 17, 2014 at 9:01 am
Simple Answer :
If the system is running well without any performance issues, then there is no need to go around changing settings. However, you will need to continually monitor performance and if there are problems then you can look into testing whether changing MAXDOP settings is beneficial.
Longer Answer :
You need to look at a few key details before determining whether to implement MAXDOP:
Is the business an OLTP(Online transaction processing) or OLAP (Online analytical processing)? Usually, with high volume and low read/write CPU related transactions, you can set up MAXDOP. One would only do this because you do not want your CPU utilizing all its resources on one single transaction leaving the other incoming transactions to form a long queue.
However, with low volume and high read/write CPU related transactions you might want to keep MAXDOP at default settings. This is because you might have smaller amounts of transactions, but they require a lot more CPU usage.
So, you mentioned you only have 4 CPUs. Realistically in the future you might want to set up MAXDOP to 1, but only if you are seeing performance issues related to CXPackets and/or CPU related issues.
SOURCE: http://www.sqlskills.com/blogs/brent/sql-server-virtualization-get-your-hands-off-that-maxdop/
and a few of articles on Brent Ozar's blog (too lazy to find, but shoot me a PM and I can go look for them)
(For other readers: my reply was a little slow, but everything I have said is already mentioned above 🙂 )
July 17, 2014 at 9:06 am
PHYData DBA (7/17/2014)
We smiled and waved, they literaly bit the waving hand.Then after showing them some information from Paul Randal, and from some Issues with a WellPoint Datawarehouse.
They asked us who is Paul Randal, who is Well Point.
This vender supplies a EMR application to millions and their performance expert has never heard of Well Point.
The performance expert should have at least heard of Paul Randal. If that person is a performance expert. I get that some DBAs don't know who he is, but an "expert" really should.
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
July 17, 2014 at 10:18 am
GilaMonster (7/17/2014)
PHYData DBA (7/17/2014)
SQLRNR - Well when I mentioned that the maxdop = 1 setting is good for pure OLTP issues where the CXPACKET waits are high.Not even then.
CXPacket waits just indicate that queries are running in parallel. 'High' values, for whatever the definition of high is, doesn't mean maxdop 1. It may mean increase cost threshold a little and tune expensive queries. At most set maxdop to cores/numa node. But not 1, not unless you're running sharepoint or dynanics.
GilaMonster - I totally agree. To add insult to injury they say that the fact this is running on a Hypervisor VM makes no difference. Also, thanks so much for chiming in. I know who you are and have respected you're opinion on things SQL posted here and elsewhere for almost 8 Years now.
July 17, 2014 at 10:24 am
arfeenmalik (7/17/2014)
Simple Answer :If the system is running well without any performance issues, then there is no need to go around changing settings. However, you will need to continually monitor performance and if there are problems then you can look into testing whether changing MAXDOP settings is beneficial.
Longer Answer :
You need to look at a few key details before determining whether to implement MAXDOP:
Is the business an OLTP(Online transaction processing) or OLAP (Online analytical processing)? Usually, with high volume and low read/write CPU related transactions, you can set up MAXDOP. One would only do this because you do not want your CPU utilizing all its resources on one single transaction leaving the other incoming transactions to form a long queue.
However, with low volume and high read/write CPU related transactions you might want to keep MAXDOP at default settings. This is because you might have smaller amounts of transactions, but they require a lot more CPU usage.
So, you mentioned you only have 4 CPUs. Realistically in the future you might want to set up MAXDOP to 1, but only if you are seeing performance issues related to CXPackets and/or CPU related issues.
SOURCE: http://www.sqlskills.com/blogs/brent/sql-server-virtualization-get-your-hands-off-that-maxdop/
and a few of articles on Brent Ozar's blog (too lazy to find, but shoot me a PM and I can go look for them)
(For other readers: my reply was a little slow, but everything I have said is already mentioned above 🙂 )
LOL - That is literally the Paul Randal article we sent them a link for when they asked who he was.
FYI - We had to tell them how to enable ForceParameterization at the database level since we did not want to enable it at the server level.
July 17, 2014 at 12:16 pm
Everyone thank you for your replies.
These are the exact same things we tried to explain prior to being sent the Quote in our original post.
Needless to say we are not impressed with their opinions.
Thanks again for validating what we had already discovered to be true.
Sometimes it is good to get a second opinion.
We appreciate yours.
July 17, 2014 at 8:16 pm
From what I've seen, Grant Fritchey's recommendations for changing the default threshold of parallelism are much more effective than changing the whole server to use MAXDOP 1. It keeps queries where parallelism costs more than the benefit from using parallelism while still allowing queries that do benefit from it to use it.
Of course, the vendor probably hasn't heard of Grant Fritchey or Gail Shaw, either. :pinch:
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2014 at 7:35 am
Jeff Moden (7/17/2014)
From what I've seen, Grant Fritchey's recommendations for changing the default threshold of parallelism are much more effective than changing the whole server to use MAXDOP 1. It keeps queries where parallelism costs more than the benefit from using parallelism while still allowing queries that do benefit from it to use it.Of course, the vendor probably hasn't heard of Grant Fritchey or Gail Shaw, either. :pinch:
They might have heard of Santa though.
But yes, changing cost threshold seems more effective imho than a carte blanche change to maxdop to 1.
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
July 18, 2014 at 8:12 am
SQLRNNR (7/18/2014)
Jeff Moden (7/17/2014)
From what I've seen, Grant Fritchey's recommendations for changing the default threshold of parallelism are much more effective than changing the whole server to use MAXDOP 1. It keeps queries where parallelism costs more than the benefit from using parallelism while still allowing queries that do benefit from it to use it.Of course, the vendor probably hasn't heard of Grant Fritchey or Gail Shaw, either. :pinch:
They might have heard of Santa though.
:-D:-P
--Jeff Moden
Change is inevitable... Change for the better is not.
July 18, 2014 at 9:07 am
Jeff Moden (7/18/2014)
SQLRNNR (7/18/2014)
Jeff Moden (7/17/2014)
From what I've seen, Grant Fritchey's recommendations for changing the default threshold of parallelism are much more effective than changing the whole server to use MAXDOP 1. It keeps queries where parallelism costs more than the benefit from using parallelism while still allowing queries that do benefit from it to use it.Of course, the vendor probably hasn't heard of Grant Fritchey or Gail Shaw, either. :pinch:
They might have heard of Santa though.
:-D:-P
😀
You guys are the best!
I will say one thing. They have heard of me now. They have also heard what my CIO's angry voice sounds like.
What truly matters is I have heard of all of you. I make a new account after each opportunity that matches my new work email. I have been posting to and reading from posts here since 2006.
All of you have helped me over the years. I wanted to say I appreciate it.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply