Setting SQL Server MaxDOP - Looking for Advice
-
I was fortunate enough to attend Epicor Insights this year, and they had some sessions dedicated to performance tuning for SQL which I found quite helpful. One of the things they said was that most folks using a SQL database for Epicor do not have their max degree of parallelism set to something other than 0 (the default), which means people running large processes in the Epicor might be utilizing all processors on the SQL server and essentially bringing other areas to a screeching halt. I've seen that happen here and there. To improve performance they recommend setting the MaxDOP to something nonzero.
We use SQL 2008 (not R2) 64-bit on Server 2008 R2 Datacenter Edition running as a VM with 4 vCPU and 32 GB RAM. If I read the articles below correctly, the large CXPACKET wait time we have from gathering some statistics means setting the MaxDOP to something like 1 or 2 could show a significant performance increase.
http://shaunjstuart.com/archive/2012/06/setting-maxdop-on-multi-core-processor-servers/
http://shaunjstuart.com/archive/2012/07/changing-sql-servers-maxdop-setting/So what do you recommend I set MaxDOP to be? I was thinking start with 1 and see how things go. For those who have tweaked MaxDOP for your environment, has anything suffered because of you setting it to a nonzero value?
-
-
-
Basically you want to do NUMA isolation for best performance. Limit the number to the number of cores that you have available from a single CPU. Chances are, that is larger than your vCPU count so staying at zero works fine.
-
@scottalanmiller
Yes - that server has dual 8-core processors. Thanks for the help. -
You bet.
