SQL Server: Determine Max DoP

https://dba.stackexchange.com/questions/36522/maxdop-setting-algorithm-for-sql-server?noredirect=1&lq=1

Older versions of SQL Server:

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

Newer versions of SQL Server: 

DECLARE @socket_count int;

DECLARE @cores_per_socket int;

DECLARE @numa_node_count int;

DECLARE @memory_model nvarchar(120);

DECLARE @hyperthread_ratio int;

SELECT @socket_count = dosi.socket_count

       , @cores_per_socket = dosi.cores_per_socket

       , @numa_node_count = dosi.numa_node_count

       , @memory_model = dosi.sql_memory_model_desc

       , @hyperthread_ratio = dosi.hyperthread_ratio

FROM sys.dm_os_sys_info dosi;

SELECT [Socket Count] = @socket_count

       , [Cores Per Socket] = @cores_per_socket

       , [Number of NUMA nodes] = @numa_node_count

       , [Hyperthreading Enabled] = CASE WHEN @hyperthread_ratio > @cores_per_socket THEN 1 ELSE 0 END

       , [Lock Pages in Memory granted?] = CASE WHEN @memory_model = N'CONVENTIONAL' THEN 0 ELSE 1 END;

DECLARE @MAXDOP int = @cores_per_socket;

SET @MAXDOP = @MAXDOP * 0.75;

IF @MAXDOP >= 8 SET @MAXDOP = 8;

SELECT [Recommended MAXDOP setting] = @MAXDOP

       , [Command] = 'EXEC sys.sp_configure N''max degree of parallelism'', ' + CONVERT(nvarchar(10), @MAXDOP) + ';RECONFIGURE;';

Useful query:

select * FROM sys.dm_os_sys_info dosi;