Part of the DBA-Tools Project.
Max Degree of Parallelism is one of the first things worth checking on any server, and one of the most commonly wrong. The default of 0 lets a single query use every scheduler on the box, which sounds efficient until 20 concurrent OLTP queries all decide to go parallel at once and end up fighting each other for CPU. Cost Threshold for Parallelism, MAXDOP’s quieter partner, is left at its ancient default of 5 in most environments, a number tuned for hardware from two decades ago.
This script reports both settings alongside the actual CPU topology, so you can tell at a glance whether the configuration matches the hardware it’s running on.
Why MAXDOP Configuration Matters
Both settings control when and how aggressively SQL Server splits a query across multiple CPU cores:
- MAXDOP set too high (or left at 0 on a many-core box) lets individual queries consume more CPU than they should, starving concurrent workloads
- MAXDOP set too low forces genuinely large queries to run single-threaded when they’d benefit from parallelism
- Cost Threshold for Parallelism left at the default of 5 means even cheap, everyday queries go parallel, adding scheduling overhead for no real benefit
- Misconfigured MAXDOP is one of the most common findings in any SQL Server performance review, and one of the cheapest to fix
When to Run This Script
- Routine SQL Server health checks
- After migrating to new hardware, a VM resize, or a core-count change
- When diagnosing CXPACKET or CXCONSUMER waits
- Reviewing a server you’ve just inherited
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-MaxdopConfiguration
Category : configuration-and-environment
Purpose : Show MAXDOP and cost threshold settings alongside current CPU topology.
Author : Peter Whyte (https://sqldba.blog/script-check-maxdop-configuration/)
Requires : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SELECT
(SELECT value_in_use FROM sys.configurations WHERE name = 'max degree of parallelism') AS maxdop,
(SELECT value_in_use FROM sys.configurations WHERE name = 'cost threshold for parallelism') AS cost_threshold_for_parallelism,
osi.cpu_count AS logical_cpu_count,
osi.hyperthread_ratio,
osi.cpu_count / osi.hyperthread_ratio AS physical_cpu_count,
osi.scheduler_count AS online_schedulers,
osi.numa_node_count
FROM sys.dm_os_sys_info AS osi;
The script reads the current MAXDOP and cost threshold values from sys.configurations, alongside logical/physical CPU counts, hyperthread ratio, scheduler count, and NUMA node count from sys.dm_os_sys_info, in a single row.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Check MAXDOP and cost threshold against actual CPU topology:
.\run.ps1 Get-MaxdopConfiguration
# To run against a remote sql server:
.\run.ps1 Get-MaxdopConfiguration -ServerInstance SQLSERVER01
This script lives in the repo at:
sql/monitoring/instance/Get-MaxdopConfiguration.sqlpowershell/wrappers/monitoring/instance/Get-MaxdopConfiguration.ps1
Example Output
This instance has 8 logical CPUs, a hyperthread ratio of 8 (meaning 1 physical core, typical for a small VM or lab box), and MAXDOP is already set to 8, matching the logical CPU count.
Understanding the Results
- maxdop — Microsoft’s general guidance: for servers with 8 or fewer logical processors, set MAXDOP to the logical CPU count or less. For more than 8, set it to 8 as a starting point per NUMA node, and tune from there. A value of 0 means unlimited, almost never correct on a production OLTP server.
- cost_threshold_for_parallelism — the default of 5 is decades old. Most modern guidance suggests starting at 25-50 and adjusting based on your actual query cost distribution.
- physical_cpu_count / hyperthread_ratio — use these to sanity-check maxdop against the real hardware, not just the logical core count hyperthreading reports.
- numa_node_count — on multi-NUMA hardware, MAXDOP should generally not exceed the number of cores per NUMA node, to avoid cross-node memory access penalties.
How to Fix MAXDOP Configuration
-- Set MAXDOP (example: 8 logical CPUs or fewer per NUMA node)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 8;
RECONFIGURE;
-- Raise cost threshold for parallelism from the outdated default of 5
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;
Both changes take effect immediately, no restart required. Start conservatively, monitor CXPACKET/CXCONSUMER waits and query duration, then adjust.
Best Practices
- Re-run this check after any hardware change: VM resize, core reassignment, new server.
- Don’t copy a MAXDOP value from a blog post or another server without checking your own core count and NUMA layout first.
- Review cost threshold alongside MAXDOP. Fixing one without the other often doesn’t move the needle.
Related Scripts
You may also find these scripts useful:
- Instance Configuration Score
- Instance Configuration Snapshot
- Memory Configuration and Usage
- OS Configuration Checks
- Resource Governor Config
- SQL Server CPU Topology and Scheduler Details
- Trace Flags
Frequently Asked Questions
What’s a good default MAXDOP value?
For 8 or fewer logical CPUs, set it to the logical CPU count. For more than 8, start at 8 per NUMA node. There’s no single universal number; it depends on core count and NUMA topology.
Does raising Cost Threshold for Parallelism disable parallelism?
No. It just raises the bar for when SQL Server considers a query “expensive enough” to justify going parallel. Cheap queries stay single-threaded; genuinely expensive ones still parallelize.
Summary
MAXDOP and cost threshold are two of the cheapest, lowest-risk configuration changes available, and two of the most commonly left at defaults that don’t match the hardware they’re running on.
Run this script as part of routine health checks, and always after a hardware or core-count change, since a MAXDOP value that was correct on the old server is rarely correct on the new one.
Leave a Reply