Wow... We have MAXDOP calculator for SQL Server... It makes my job easier...
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 https://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.
For example:-
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 https://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 https://blogs.technet.com/b/mapblog/archive/2012/02/03/why-doesn-t-the-map-toolkit-report-the-number-of-cores.aspx
Calculator: https://blogs.msdn.com/b/sqlsakthi/p/maxdop-calculator-sqlserver.aspx
Keep your feedbacks coming through comments section in this blog post...
Sakthivel Chidambaram, Microsoft
Comments
Anonymous
May 27, 2012
The comment has been removedAnonymous
August 07, 2012
The comment has been removedAnonymous
August 20, 2012
Thanks for great post. I have 24 cores on my server, each socket has 3 cores. So should my maxdop value be multiples of 3?Anonymous
December 23, 2012
Great tool, but are we supposed to Add or multiply the results? I got a '2' from the first query and 2 rows of '6' from the second query, so what answer does that query give? Is it 2+6+6 or 266 or just take first of the second query, so 2*6?Anonymous
July 14, 2014
Please update the bitley link that points to the PowerShell documentation to this link: technet.microsoft.com/.../dn425048.aspx. The link you have posted has been archived and is out of date. Thanks.Anonymous
September 15, 2014
Link in second paragraph should be (I believe) support.microsoft.com/.../2806535. But THANKS for article! JamieAnonymous
October 12, 2014
Shall we consider hyper threading while calculating the no of processors as its showing 8(powershell gave me 4) in device manager....Anonymous
October 30, 2014
Hi, Where is the calculator gone? Only bookmarks i end up with are general Powershell sites. [REPLY] MAXDOP Calculator is still available at http://blogs.msdn.com/b/sqlsakthi/p/maxdop-calculator-sqlserver.aspx but it not updated since 2 yrs. so it doesn't cover any changes/enhancements to SQL/OS that happened in last 2 yrs.Anonymous
February 11, 2015
Hello Sakthi, Is this calculator applicable for VM's too?Anonymous
June 23, 2015
-added this for the lazy DBAs. Hopefully it is accurate enough. I did little testing with it. I think you better still test your settings as the article(s) suggest. /** MAXDOP helper blogs.msdn.com/.../wow-we-have-maxdop-calculator-for-sql-server-it-makes-my-job-easier.aspx support.microsoft.com/.../2806535 **/ set nocount on declare @numa_node int, @logical_processors int set @numa_node= (select COUNT(DISTINCT memory_node_id)FROM sys.dm_os_memory_clerks WHERE memory_node_id!=64); declare @cores table (Cores nvarchar(100)) insert into @cores exec master.dbo.xp_cmdshell 'powershell -Command "Get-WmiObject -namespace "rootCIMV2" -class Win32_Processor -Property NumberOfCores | select NumberOfCores"' set @logical_processors= (select top 1 ltrim(cores) as [logical processors] from @cores where ltrim(cores) like '[0-9]%') select @numa_node as [NUMA Nodes], @logical_processors as [Logical Processors] if @numa_node =1 and @logical_processors <=8 begin select cast (@numa_node as nvarchar(10)) + ' NUMA node, Server with single NUMA node', cast (@logical_processors as nvarchar(10)) + ' Logical Processors (Less than 8 logical processors)', 'Keep MAXDOP at or below # of logical processors ' + cast (@logical_processors as nvarchar(10)) + ' for MAXDOP ?' end if @numa_node =1 and @logical_processors >8 begin select cast (@numa_node as nvarchar(10)) + ' NUMA node, (Server with single NUMA node)', cast (@logical_processors as nvarchar(10)) + ' Logical Processors (Greater than 8 logical processors)', 'Keep MAXDOP at 8' end if @numa_node >1 and @logical_processors <=8 begin select cast (@numa_node as nvarchar(10)) + ' NUMA node, Server with multiple NUMA nodes', cast (@logical_processors as nvarchar(10)) + ' Logical Processors (Less than 8 logical processors)', 'Keep MAXDOP at or below # of logical processors per NUMA node' + cast (@logical_processors as nvarchar(10)) + ' for MAXDOP ?' end if @numa_node >1 and @logical_processors >8 begin select cast (@numa_node as nvarchar(10)) + ' NUMA node, Server with multiple NUMA nodes', cast (@logical_processors as nvarchar(10)) + ' Logical Processors (Greater than 8 logical processors per NUMA node)', 'Keep MAXDOP at 8' endAnonymous
January 19, 2016
Just leave the setting on ZERO (0) and then SQLServer will calculate it itself!