How to Tune Maximum Degree of Parallelism (MAXDOP)

The Maximum Degree of Parallelism (MAXDOP) setting will determine the degree to which SQL server will attempt to parallel process transactions which are received from the scheduler. As a general rule of thumb, OLTP environments with high transaction volume and repetitive queries should normally have a MAXDOP value of 1, as any benefit gained from parallel process would quickly be lost in overheads introduced in managing this. OLAP environments however (such as analysis services / data warehousing etc) where transactions are generally much fewer, however considerably more complex – these environments would benefit from a MAXDOP setting of 0 (unlimited) or near the number of CPUs in the system.

A good indicator on whether this value has been correctly configured is the CXPACKET performance counter. Where more than 5% of the total wait conditions are due to this counter, it’s most likely that the MAXDOP value will need to be adjusted.

Below are 2 code snippet to retrieve the wait types as a percentage. The first is from Paul Randal;

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
SELECT
[W1].[wait_type] AS [WaitType],
CAST ([W1].[WaitS] AS DECIMAL(14, 2)) AS [Wait_S],
CAST ([W1].[ResourceS] AS DECIMAL(14, 2)) AS [Resource_S],
CAST ([W1].[SignalS] AS DECIMAL(14, 2)) AS [Signal_S],
[W1].[WaitCount] AS [WaitCount],
CAST ([W1].[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait_S],
CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgRes_S],
CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

The 2nd snippet is a reduced version of this which only returns wait_time_ms and percentage;

SELECT 
wait_type, wait_time_ms, 
convert(decimal(7,4), 100.0 * wait_time_ms / SUM(wait_time_ms) OVER()) AS [Percent]
from 
sys.dm_os_wait_stats
where 
wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP',
'CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN','BROKER_EVENTHANDLER','SLEEP_DBSTARTUP', 'TRACEWRITE'
)
and wait_type NOT like 'PREEMPTIVE%'
order by 
[Percent] Desc
go

The MAXDOP value can be updated during run time by the following command, noting that the value below should be adjusted to suit your requirements;

sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO

You may also want to clear the wait statistics after this to ensure your next checks on the system are clear / fresh statistics since this change has come into effect;

DBCC SQLPERF(“sys.dm_os_wait_stats”,CLEAR);