Tag: Cost Threshold of Parallelism

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!

Knowledge worth sharing...

Linkedin

Facebook

Google

Twitter