Firstly, you have to check out Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server .
The KB basically says, if we have 8 or more logical processors in a NUMA node, generally we will get the best performance by setting maxdop to 8 or lower.
Secondly, use the script below to get to instantly get the suggested MAXDOP for your SQL instance.
DECLARE @CoreCount int; DECLARE @NumaNodes int; SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i); SET @NumaNodes = ( SELECT MAX(c.memory_node_id) + 1 FROM sys.dm_os_memory_clerks c WHERE memory_node_id < 64 ); IF @CoreCount > 4 /* If less than 5 cores, don't bother. */ BEGIN DECLARE @MaxDOP int; /* 3/4 of Total Cores in Machine */ SET @MaxDOP = @CoreCount * 0.75; /* if @MaxDOP is greater than the per NUMA node Core Count, set @MaxDOP = per NUMA node core count */ IF @MaxDOP > (@CoreCount / @NumaNodes) SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75; /* Reduce @MaxDOP to an even number */ SET @MaxDOP = @MaxDOP - (@MaxDOP % 2); /* Cap MAXDOP at 8, according to Microsoft */ IF @MaxDOP > 8 SET @MaxDOP = 8; PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max)); END ELSE BEGIN PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.'; PRINT 'This is the default setting, you likely do not need to do'; PRINT 'anything.'; END
Cost Threshold Parallelism
Cost Threshold of Parallelism is like a bar that we set to control which queries qualifies to go parallel and which queries are cheap, that it is going to get 1 core no matter what.
The default value is 5 which is very low . The starting value for Cost Threshold of Parallelism is 50.
To change the values of these 2 parameters, execute the scripts below.
-- Change MAXDOP value EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'max degree of parallelism', 8; GO RECONFIGURE WITH OVERRIDE; GO -- Change Cost Threshold for Parallelism value EXEC sys.sp_configure N'cost threshold for parallelism', N'50' GO RECONFIGURE WITH OVERRIDE GO -- Verify the changes made SELECT name ,value ,value_in_use ,description ,is_dynamic ,is_advanced FROM sys.configurations WITH (NOLOCK) WHERE name IN ('max degree of parallelism','cost threshold for parallelism',)
Cheers!