MAXDOP Settings for SQL Server 2014

I know this question has been asked number of times and also has answers to it but, I still need a bit more guidance on this subject.

Below is the details of my CPU from SSMS:

Below is CPU tab from task manager of the DB Server:

I have kept the setting of MAXDOP at 2 by following below formula:

declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int
declare @MaxDOP int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id  1 AND @HTEnabled = 0
    SET @MaxDOP= @logicalCPUPerNuma 
ELSE IF  @NoofNUMA > 1 AND @HTEnabled = 1
    SET @MaxDOP=round( @NoofNUMA  / @physicalCPU *1.0,0)
ELSE IF @HTEnabled = 0
    SET @MaxDOP=@logicalCPUs
ELSE IF @HTEnabled = 1
    SET @MaxDOP=@physicalCPU

IF @MaxDOP > 10
    SET @MaxDOP=10
IF @MaxDOP = 0
    SET @MaxDOP=1

PRINT 'logicalCPUs : '         + CONVERT(VARCHAR, @logicalCPUs)
PRINT 'hyperthreadingRatio : ' + CONVERT(VARCHAR, @hyperthreadingRatio) 
PRINT 'physicalCPU : '         + CONVERT(VARCHAR, @physicalCPU) 
PRINT 'HTEnabled : '           + CONVERT(VARCHAR, @HTEnabled)
PRINT 'logicalCPUPerNuma : '   + CONVERT(VARCHAR, @logicalCPUPerNuma) 
PRINT 'NoOfNUMA : '            + CONVERT(VARCHAR, @NoOfNUMA)
PRINT '---------------------------'
Print 'MAXDOP setting should be : ' + CONVERT(VARCHAR, @MaxDOP)

I am still seeing high wait times related to CXPACKET. I am using below query to get that:

```
WITH [Waits] AS
(SELEC

Bogus

Here's why that wait stats report stinks: It doesn't tell you how long the server has been up.

I can see it in your screenshot of CPU time: 55 days!

Alright, so let's do some math.

Math

There are 86,400 seconds in day.

SELECT (86400 * 55) seconds_in_55_days

The answer there? 4,752,000

You have a total of 452,488 seconds of CXPACKET.

SELECT 4752000 / 452488 AS oh_yeah_that_axis

Which gives you... 10 (it's closer to 9.5 if you do actual math, here).

So while CXPACKET might be 62% of your server's waits, it's only happening about 10% of the time.

Leave It Alone

You've made the right adjustments to settings, it's time to do actual query and index tuning if you want to change the numbers in a meaningful way.

Other considerations

CXPACKET may arise from skewed parallelism:

• More on CXPACKET Waits: Skewed Parallelism

On newer versions, it may surface as CXCONSUMER:

• CXCONSUMER Is Harmless? Not So Fast, Tiger.

Absent a third party monitoring tool, it may be worth capturing wait stats on your own:

• Capturing wait statistics for a period of time

• How to Capture Baselines with sp_BlitzFirst

StackExchange Database Administrators Q#232943, answer score: 13