July 1, 2016 at 5:28 am
Hi All,
I have a VM with 32GB RAM and 12 processors (SQL 2012 SP3 Ent 64bit)
What would be the correct setting for MAXDOP, see attached image
Thanks
It's better to fail while trying, rather than fail without trying!!!
July 1, 2016 at 5:35 am
Depends on the workload on the server, whether there are any other instances, etc, etc. I would start with six, test, and adjust as necessary.
John
July 1, 2016 at 5:42 am
Can you explain the significance of MAXDOP setting.
July 1, 2016 at 5:56 am
John Mitchell-245523 (7/1/2016)
Depends on the workload on the server, whether there are any other instances, etc, etc. I would start with six, test, and adjust as necessary.John
In part, the MaxDoP setting depends on the underlying hardware and virtualisation platform. Is your VM aware of the platform it's running on? Can SQL Server see the NUMA configuration? If so, then I would start by setting MAXDOP to the number of CPUs in each NUMA node.
And you might also want to adjust the Cost Threshold for Parallelism setting. 5 is a bit low. (Assuming you haven't changed this already...)
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 1, 2016 at 6:46 am
Maybe these articles will explain this better:
https://www.mssqltips.com/sqlservertip/2650/what-maxdop-setting-should-be-used-for-sql-server/
https://support.microsoft.com/en-us/kb/2806535
This is an additional to check five settings that often go overlooked
https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
Durga, in the Microsoft article you can find the answer.
July 1, 2016 at 12:00 pm
As someone else said, if the VM is NUMA aware, make the MAXDOP no more than the PHYSICAL cores in each NUMA node. For other configs (in any case actually) to give best advice I would need more information about host/guest config, workload, etc.
Cost threshold for parallelism default of 5 is universally too low. 20 for OLTP and 50 for OLAP without other information (which as a performance tuning consultant I will always have).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 1, 2016 at 6:13 pm
It won't work for every SQL Server instance out there but I tend to use MAXDOP as a bit of a poor-man's resource governor. In my eyes, no one process should get to use more than 1/4 of the total CPU usage and so usually start off by setting MAXDOP for 1/4th the number of CPUs.
Of course, that's also being a bit lazy. Our daytime "load" is a whole lot different than our night time load and, on a system with only 12 CPUs, that would drive me to set MAXDOP to only 3, possibly 4 because we do have some heavy lifting processes that necessarily run (SLAs, etc) during the day. Fortunately, I don't have to worry about that too much because we have 32 processors and anything that needs more than 8 CPUs (1/4th of 32) deserves to run slow and needs to have its code repaired for performance.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2016 at 7:41 am
Hi All,
Thanks for your response. To answer a few
The VM is hosted with other VM's running on the same tin of which I dont have the number of other vm's hosted on the same tin.(Load)
The VM in question it's a BI VM (OLAP) single instance, only highly active from 5pm till 8am ETL process
It's better to fail while trying, rather than fail without trying!!!
July 2, 2016 at 8:30 am
smthembu (7/2/2016)
Hi All,Thanks for your response. To answer a few
The VM is hosted with other VM's running on the same tin of which I dont have the number of other vm's hosted on the same tin.(Load)
The VM in question it's a BI VM (OLAP) single instance, only highly active from 5pm till 8am ETL process
If the total for physical box is 32G of RAM and 12 CPUs and that is shared amongst all the VMs you have on the box, then you're going to need to really look at the types and expected "schedule" of the different types of loads you have and when they occur. It may be better to setup "Resource Governor" and leave MAXDOP set to 0 for all instances. If the nightly ETL runs on the one VM have no overlap with your daytime load, you might not even have to do that.
I will, however, suggest that if you're running multiple VMs on just 32GB and 12 CPUs in total, your best bet may be to recognize that the "metal" is seriously underpowered and make plans to increase the resources. Contrary to what many people think, VM isn't a magical solution that makes more resources available. The box has physical limits. VM only shares "idle times" between instances.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2016 at 10:41 am
It is very easy to adjust MAXDOP to suit varying load times since it is a live/immediate setting and requires no reboot. I am tired and can't recall it's affect on plan cache though.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 4, 2016 at 6:59 am
TheSQLGuru (7/2/2016)
It is very easy to adjust MAXDOP to suit varying load times since it is a live/immediate setting and requires no reboot. I am tired and can't recall it's affect on plan cache though.
I think it does, unless they changed this behaviour 2012 onwards?!
July 19, 2016 at 6:52 am
I've been monitoring the load on the VM and noticed the CXPACKET wait was too high, so for now I have changed the maxdop to 1 and cleared the waits stats shall see tomorrow how bad/good the change is
It's better to fail while trying, rather than fail without trying!!!
July 19, 2016 at 7:36 am
smthembu (7/19/2016)
I've been monitoring the load on the VM and noticed the CXPACKET wait was too high, so for now I have changed the maxdop to 1 and cleared the waits stats shall see tomorrow how bad/good the change is
Bad, very bad.
CXPacket is not a problem, it's an indication that queries are running in parallel. Alone it tells you very little and is certainly not a reason to cripple the workload by restricting everything to running in serial.
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 19, 2016 at 12:04 pm
@Gail, what else to look for?
It's better to fail while trying, rather than fail without trying!!!
July 19, 2016 at 7:46 pm
smthembu (7/19/2016)
@Gail, what else to look for?
Can't speak for Gail but my suggestion would be... a small amount of money to at least quadruple memory or spend some time fixing unnecessarily resource intensive code. 32GB is pretty small for a 12 processor box running on 64 bit technology.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply