So after Max. Server Memory calculator, to make your life much more easier, here is the Max Degree of Parallelism or MAXDOP calculator.
We are using SQL DMV’s to find out the number of NUMA nodes and Powershell query to find out the number of processor cores and then use the recommendations given in http://support.microsoft.com/kb/2023536 to give a MAXDOP value to be set in SQL Server.
As stated in this KB:
“…The maximum value of 8 provided in the above guideline is applicable for typical SQL Server activity and the overhead for the exchange operators used in parallel query plans. You could vary this maximum value depending upon your specific application patterns and concurrent activity on the SQL Server.
If you have very small number of concurrently executing queries relative to the number of processors, then you can set maxdop to a higher value like 16.
If you have very large number of concurrently executing queries relative to the number of processors, then you can set maxdop to a lower value such as 4.
Any value you propose to use should be thoroughly tested against the specific application activity or pattern of queries…”
You can then follow http://msdn.microsoft.com/en-us/library/ms189094.aspx and set Max. Degree of Parallelism in SQL Server.
Please note that we detect the number of cores available in the Server from OS perspective, so if you have limited the number of processors to SQL Server, this calculator will not be able to adjust the values accordingly so may not be useful to you.
This calculator assumes that there are equal number of processors per NUMA node.
This calculator works for SQL Server 2005 & above running on Windows Server 2008 and above. To make this to work on Windows 2003, please install the hotfix mentioned in http://blogs.technet.com/b/mapblog/archive/2012/02/03/why-doesn-t-the-map-toolkit-report-the-number-of-cores.aspx
Keep your feedbacks coming through comments section in this blog post…
Sakthivel Chidambaram, Microsoft