SQLYARD Tools
MAXDOP Advisor
Runtime-informed MAXDOP and Cost Threshold for Parallelism recommendations based on your server hardware and live wait statistics — not just the install-time formula.
Disclaimer: This tool provides recommendations based on the inputs you supply and established SQL Server guidance. It is not a substitute for professional analysis of your environment. Always test sp_configure changes in non-production first. Review with a senior DBA before applying to production. Results depend on the accuracy of the data you provide.
Single Snapshot Mode: Values from sys.dm_os_wait_stats accumulate since the last SQL Server restart. A single snapshot may include historical waits from months ago. Use Delta Analysis for a current picture. Note: sys.dm_os_wait_stats returns values in milliseconds — if your query divided by 1000 for readability, multiply back before entering.
Foundation Pre-Check — Complete Before Tuning Parallelism
MAXDOP and Cost Threshold for Parallelism are instance-level tuning knobs — they are the last thing you touch. The right order is: fix bad queries and missing indexes first, eliminate heaps where appropriate, clean up redundant indexes, then tune parallelism around what is left. Check each item before proceeding.
Missing indexes reviewed
Checked sys.dm_db_missing_index_details for high-impact missing indexes. Optimizer is not begging for indexes that would eliminate large scans going parallel.
Top queries by CPU and reads reviewed
Checked sys.dm_exec_query_stats. Not just 2-3 bad queries driving all the parallelism — if they are, fix those plans first before touching global MAXDOP.
Redundant and overlapping indexes addressed
Redundant indexes quietly destroy write performance, bloat the transaction log, and increase CDC overhead. Overlapping indexes mean more log records per write.
Index fragmentation managed
Heavy fragmentation drives more pages read, larger scans, more PAGEIOLATCH pressure. Fragmented indexes combined with missing indexes are often the real source of parallelism pressure.
Unchecked items will appear as warnings in your recommendation. You can still proceed — the tool will flag what to address first.
Server Hardware
Total physical cores across all sockets:
SELECT cpu_count / hyperthread_ratio AS physical_cores
FROM sys.dm_os_sys_info;
Total logical processors including hyperthreading:
SELECT cpu_count FROM sys.dm_os_sys_info;
Number of NUMA nodes:
SELECT COUNT(DISTINCT memory_node_id)
FROM sys.dm_os_memory_nodes
WHERE memory_node_id != 64;
Version affects default MAXDOP behavior:
SELECT @@VERSION;
Current Configuration
Your current configured value:
SELECT value_in_use FROM sys.configurations
WHERE name = 'max degree of parallelism';
Current CTP (default 5 is almost always too low):
SELECT value_in_use FROM sys.configurations
WHERE name = 'cost threshold for parallelism';
Live Wait Stats — Parallelism Signals
Run this query and enter the values below. Values are in milliseconds. If your query returns seconds multiply by 1000. Enter 0 for any wait type not in your results. SOS_WORK_DISPATCHER is automatically excluded as background noise.
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET','CXCONSUMER','SOS_SCHEDULER_YIELD','PAGEIOLATCH_SH','THREADPOOL')
ORDER BY wait_time_ms DESC;
Column 2 in results
Column 3 in results
Enter 0 if not in results (SQL 2016+)
Key ratio signal — benign if close to CXPACKET
I/O pressure — high value alongside CXPACKET signals missing indexes forcing scans that go parallel
CPU pressure signal
Normalizes wait stats per hour:
SELECT DATEDIFF(HOUR, sqlserver_start_time, GETDATE())
FROM sys.dm_os_sys_info;
Typical concurrent user sessions:
SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
Understanding This Tool — Important Context
sys.dm_os_wait_stats accumulates since last restart
Wait statistics are cumulative from the moment SQL Server starts. A single snapshot on a server running for 18 months contains 18 months of data. A batch job that hammered CXPACKET once a year ago is still in those numbers. Always use Delta Analysis for production tuning decisions.
SOS_WORK_DISPATCHER is excluded automatically
This wait type is a background worker thread dispatcher wait — almost always benign internal system activity. It can dominate raw wait stats (sometimes 90%+) and has nothing to do with parallelism tuning. The tool filters it out before any calculations.
CXCONSUMER close to CXPACKET usually means benign synchronization
CXCONSUMER represents consumer threads waiting for work to be distributed — normal parallel query coordination. When CXCONSUMER approaches CXPACKET in magnitude, most of the parallelism wait is this benign sync, not true contention. The ratio matters more than the raw number.
High PAGEIOLATCH_SH alongside CXPACKET often means missing indexes
When PAGEIOLATCH_SH is significant alongside CXPACKET, queries are likely doing large scans (due to missing indexes) that then go parallel. Fixing the indexes eliminates both the I/O pressure and the parallelism pressure — without touching MAXDOP at all.
Apply Cost Threshold for Parallelism first — always
The default CTP of 5 was set in the 1990s. On modern servers 35-50 is the baseline. Most production environments find CTP alone eliminates the majority of unnecessary parallelism without touching MAXDOP. Change CTP first, monitor 2-4 weeks, then re-evaluate.
Read the full methodology
For a complete technical walkthrough including Microsoft recommendation tables, wait stat interpretation, the three-scope MAXDOP hierarchy, and DOP Feedback in SQL 2022:
SQL Server MAXDOP: Why the Installation Default Is Just the Starting Point →