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
Great Job and Tool!! Don’t listen to the so called “community people”; they will make a fool out of you. Keep doing what you are doing, it is great to see such tools from the real community; don’t listen to these stupid people and their derogatory crappy comments
Reply from Sakthi:
Thanks Ashok. I takes feedbacks constructively, Keep them coming!
I'm DBA on SQL Server, but i don't have access to remote the server database and I can't execute commands PowerShell.
Can I consider the response from this command the information contained in the "Server Properties >> Processors"?
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?
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 2*6*6 or just take first of the second query, so 2*6?
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.
Link in second paragraph should be (I believe) support.microsoft.com/.../2806535.
But THANKS for article! Jamie
Shall we consider hyper threading while calculating the no of processors as its showing 8(powershell gave me 4) in device manager....
Where is the calculator gone? Only bookmarks i end up with are general Powershell sites.
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.
Is this calculator applicable for VM's too?
-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.
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 "root\CIMV2" -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
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 ?'
if @numa_node =1 and @logical_processors >8
+ ' NUMA node, (Server with single NUMA node)',
+ ' Logical Processors (Greater than 8 logical processors)',
'Keep MAXDOP at 8'
if @numa_node >1 and @logical_processors <=8
+ ' NUMA node, Server with multiple NUMA nodes',
'Keep MAXDOP at or below # of logical processors per NUMA node' +
if @numa_node >1 and @logical_processors >8
+ ' Logical Processors (Greater than 8 logical processors per NUMA node)',