SQLYARD Tools
MAXDOP Advisor
Input your server hardware specs and live wait stat data to get a runtime-informed MAXDOP recommendation — not just the install-time formula, but what your workload is actually telling you.
Disclaimer: This tool provides recommendations based on the inputs you supply and established SQL Server guidance from Microsoft and the DBA community. It is not a substitute for professional analysis of your specific environment. Always test sp_configure changes in a non-production environment first. Review any changes with a senior DBA or architect before applying to production systems. Results depend entirely on the accuracy of the data you provide.
Server Hardware
Total physical cores across all sockets. Run this query:
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 on the server:
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 value (default is 5, often too low):
SELECT value_in_use
FROM sys.configurations
WHERE name = 'cost threshold for parallelism';
Primary workload pattern on this instance:
Helps contextualize wait stat accumulation:
Live Wait Stats — Parallelism Signals
Run this query and enter the values below. If CXPACKET or CXCONSUMER do not appear in your results enter 0.
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET','CXCONSUMER','SOS_SCHEDULER_YIELD','THREADPOOL')
ORDER BY wait_time_ms DESC;
Primary parallelism coordination wait
Number of waiting tasks for CXPACKET
Consumer thread wait (SQL 2016+, benign if low)
CPU pressure signal alongside parallelism
Used to normalize 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;