About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.
SQL Server can split a single query across several processors so it finishes sooner. That is called a parallel plan. It helps large reports and batch jobs, but it costs extra processor time and coordination, and on a small virtual machine it can let one heavy report crowd out the short, frequent queries that staff are waiting on.
Two server settings decide how much parallelism SQL Server uses: max degree of parallelism (MAXDOP) and cost threshold for parallelism. This article explains each one, Microsoft's current guidance, and how to tell whether yours need attention.
Why it matters
A hosted SQL Server VM usually has a modest number of virtual CPUs, chosen for cost. On such a VM, how parallelism is set can change how the application feels under load: whether a month-end report slows down invoice entry, or whether a busy period shows high processor use with little work done.
Both settings are, in Microsoft's words, advanced options that should be changed only by an experienced database professional. The aim of this article is to help owners and administrators ask the right questions, not to replace testing.
How it works
MAXDOP: the degree of parallel execution
MAXDOP limits the degree of parallelism. Microsoft's detailed qualification is that the limit applies per task, not as a hard worker or CPU budget for an entire request or query. Concurrent parallel branches can use more workers than the MAXDOP value. A value of 1 suppresses parallel plans. A value of 0 allows SQL Server to use available processors up to 64; Microsoft notes this is not the recommended value for most cases. See the thread and task architecture guide for worker scheduling.
Since SQL Server 2019, setup offers a recommended MAXDOP value based on the number of processors available during the installation process. As with memory, that recommendation reflects the VM at installation time. If virtual CPUs were added or removed later, check the value again.
Microsoft's guidance for SQL Server 2016 and later depends on how many NUMA nodes SQL Server sees. NUMA nodes are groups of processors; SQL Server 2016 and later also create software NUMA nodes automatically on larger servers. For a server with a single NUMA node:
| Logical processors | Microsoft guidance |
|---|---|
| Eight or fewer | Keep MAXDOP at or under the number of logical processors |
| More than eight | Keep MAXDOP at 8 |
For servers with several NUMA nodes, the guidance is per node: at or under the number of logical processors per node when there are 16 or fewer per node, and half the processors per node, up to 16, when there are more. A hosted VM's NUMA layout depends on its size and the provider's platform, so confirm how many nodes SQL Server reports rather than assuming.
MAXDOP can also be set more narrowly. Microsoft lists overrides At the database level, using the MAXDOP database scoped configuration, for individual queries using hints, and for workload groups through Resource Governor where the version and edition support it. Resource Governor is available in Enterprise for SQL Server 2022 and 2025; Standard gains it in 2025 and does not have it in 2022. A database-level setting can suit one application database on a shared instance. The server setting takes effect without a restart.
Cost threshold: which queries may go parallel
The cost threshold decides which queries are considered for parallelism at all. SQL Server evaluates parallel alternatives only when the best serial plan's estimated cost exceeds the cost threshold for parallelism value.
The cost is not seconds. Microsoft explains it is a relative measure used only for plan selection; it doesn't measure actual runtime.
The default is 5, and Microsoft is direct about it: The default value of 5 is a starting point, not a recommendation. It adds that raising it can help to keep smaller OLTP queries executing with serial plans. OLTP here means the short, frequent transactions of a business application.
The setting is ignored when SQL Server has only one logical processor available, or when MAXDOP is 1.
Signs a cost threshold is too low or too high
Microsoft lists indicators on the cost threshold page. Too low: many light queries go parallel; many plans in Query Store are parallel; and CXPACKET and CXCONSUMER waits dominate, possibly with THREADPOOL and SOS_SCHEDULER_YIELD. Too high: Not enough of the workload's CPU-heavy queries go parallel, and CPU utilization is higher than optimal as a result.
These are signals to investigate, not proof. Parallelism waits can also reflect other problems, such as missing indexes or out-of-date statistics, that no setting will fix.
Newer versions can adjust parallelism per query
For production deployments, the SQL Server 2022 and 2025 edition matrices list degree of parallelism (DOP) feedback for Enterprise, not Standard or Express. SQL Server 2022 introduced the feature, which reduces parallelism for repeating queries that use it inefficiently. It needs database compatibility level 160 or higher and Query Store in read-write mode. Microsoft notes that DOP feedback is not enabled by default in SQL Server 2022 (16.x). Its adjustments never go above the MAXDOP setting, so MAXDOP still matters. Check the documentation for your version before relying on it.
What to choose and why
Start from the guidance, not the default
For a hosted VM with eight or fewer virtual CPUs and one NUMA node, the guidance allows MAXDOP up to the number of processors. A lower value can reduce contention, but it does not reserve processors for other queries. Representative concurrent-workload measurements must establish whether it improves responsiveness. Above eight processors on one node, the guidance is 8.
Raise the cost threshold carefully
Because 5 is not a recommendation, a higher value is a reasonable thing to test on a business application. Microsoft's method is incremental: Use small increments and observe a full business cycle before further changes. For a business application, a full cycle may include a month-end.
Ask the application vendor
A vendor may specify MAXDOP or cost threshold values for its product. Follow a vendor's documented requirement unless you have tested otherwise and agreed it with them.
Change one setting at a time
Change MAXDOP or the cost threshold, not both at once, and record what changed and when. Otherwise it is not possible to tell which change helped or harmed.
How to test
- Use an isolated copy first. Restore a copy of the database to a non-production VM with the same SQL Server version, number of virtual CPUs and memory. Use synthetic or masked data where the data is sensitive.
- Replay a representative workload. Include normal data entry and the heavy reports or batch jobs, run at the same time as they would be in production.
- Measure before and after. Compare query durations, processor use and wait statistics for the same workload, not anecdotes.
- Then change production in small steps, at a quiet time, and watch a full business cycle.
Risks and limits
- No single value suits every workload. Microsoft's table is guidance; your workload decides.
- Settings do not fix poor queries. Missing indexes, stale statistics and inefficient code can look like a parallelism problem.
- A VM resize changes the picture. Revisit both settings when the number of virtual CPUs changes.
- Instance-wide changes affect every database on the instance. A database-scoped MAXDOP setting can target an application database. Cost threshold for parallelism is an instance setting; it has no corresponding database-scoped setting.
- No change commands are given here. Microsoft's pages show the Transact-SQL and Management Studio steps; follow them on a test copy first.
Sources
- Microsoft Learn: SQL Server 2022 editions and features.
- Microsoft Learn: SQL Server 2025 editions and features.
- Microsoft Learn: Thread and task architecture.
Opened 14 November 2026.
- Microsoft Learn: Server configuration: max degree of parallelism.
- Microsoft Learn: Server configuration: cost threshold for parallelism.
- Microsoft Learn: Degree of parallelism (DOP) feedback.
Related service: Cloud hosting.
Related future topics
- SQL Server memory on a hosted VM.
- Reading SQL Server wait statistics for business owners.
- Index and statistics maintenance on hosted SQL Server.
Learning notes
- MAXDOP limits parallelism per task, not total workers or CPUs across a whole query; 0 is not recommended for most cases.
- With one NUMA node and eight or fewer processors, keep MAXDOP at or under the processor count; above eight, at 8.
- The cost threshold default of 5 is a starting point, not a recommendation.
- Change one setting at a time, in small steps, over a full business cycle.
- Test on an isolated copy with a representative workload before changing production.
This article was originally published on .