Setting MAXDOP and Cost Threshold Sensibly

A server can look busy while small queries launch parallel workers they never needed. Setting MAXDOP and cost threshold sensibly starts with the workload, not a copied pair of numbers.

Know the Two Decisions in MAXDOP and Cost Threshold

Cost threshold for parallelism influences when the optimizer considers a parallel plan. MAXDOP limits how many processors a parallel plan can use for a statement or task. One setting does not replace the other. A high MAXDOP with a low threshold can invite too many small parallel requests. A high threshold with a very restrictive MAXDOP can constrain legitimate analytical work.

I explain them together because changing one number in isolation makes results hard to interpret. The default cost threshold of 5 is a starting value, not a modern workload recommendation. It is an optimizer cost estimate, not five seconds. Ask which queries are actually going parallel and whether that behavior helps them. The answer belongs in plans and workload measurements.

Read Configured and Effective Values

sys.configurations reports the stored value and value currently in use. Capture both before a change. Also record the server build, processor topology, and application workload. An inherited value can be intentional. A difference between stored and active values can indicate a pending action or incomplete change.

The query is small enough to keep in a health-check script. I save it beside the planned change so the team can restore the previous state if necessary. The values alone do not show whether queries benefit from parallel plans. They define the starting point for measurement.

SELECT name, value, value_in_use, is_dynamic
FROM sys.configurations
WHERE name IN
(
    N'max degree of parallelism',
    N'cost threshold for parallelism'
)
ORDER BY name;

Check Topology Before MAXDOP

Processor sockets, cores, and NUMA layout affect a sensible MAXDOP starting point. Modern SQL Server guidance considers logical processors per NUMA node and other topology details. Virtual machines can present a topology different from the physical host. Confirm what the instance sees rather than trusting a purchase sheet.

I ask the infrastructure team whether vCPU allocation changed after the server was built. A MAXDOP value chosen for an earlier layout can age badly. Also check database-scoped settings and query hints that override the instance setting. A server-level change will not explain a query with its own MAXDOP hint. The most specific effective setting is the one the plan follows.

Look at Real Parallel Work

Query Store and execution plans show which queries run frequently, how long they take, and whether they use parallel operators. Wait statistics can show parallel worker coordination, but one wait type is not a verdict. Compare CPU, duration, throughput, and user impact over a full business cycle. A busy report can benefit from parallelism while a small transaction gains nothing.

I start with the queries that matter to users. If changing threshold reduces worker activity but makes critical reports slower, the trade-off needs discussion. If a query is slow because of scans or poor estimates, parallelism can only make the bad plan faster at using CPU. Fix the query or statistics before asking a server setting to rescue it.

Treat Five as a Starting Point

The default threshold of 5 is low for many modern workloads, but the right replacement is not one universal number. Raise it in measured steps and observe plans and performance. The optimizer’s cost model is an estimate used during compilation. It is not a wall-clock forecast, and the final plan can still be parallel in cases that appear below the displayed threshold.

I do not promise that moving the threshold will make a server faster. It changes which plans the optimizer considers. Capture a before period, change one variable, and compare representative queries. A setting that looks elegant in a configuration report must still prove useful under load.

Make a Reviewed Change to MAXDOP and Cost Threshold

sp_configure changes instance settings. The example shows syntax with placeholder values that your workload review must choose. Run it in a planned window with a rollback value recorded. RECONFIGURE applies dynamic options. Test on a suitable environment first when the application is sensitive to plan changes.

I avoid changing MAXDOP and cost threshold at the same moment unless the test is designed to evaluate them as a pair. Otherwise an improvement or regression has no clear cause. Document the chosen values and expected effect. Check effective values immediately after the command.

EXEC sys.sp_configure N'max degree of parallelism', 4;
RECONFIGURE;
EXEC sys.sp_configure N'cost threshold for parallelism', 40;
RECONFIGURE;

Watch Plans After Changing MAXDOP and Cost Threshold

A configuration change can affect new compilations while cached plans remain until they are replaced or recompiled. Do not flush the whole plan cache just to force a quick visual result. That can create a separate performance event. Observe the workload naturally and compare query plans for important statements as they compile again.

I watch CPU, worker use, elapsed time, and application throughput. If a specific query regresses, examine its plan and options before reverting the entire server change. A query hint can be a narrow exception, but it creates maintenance work. Keep the server default understandable and exceptions few.

Account for Special Workloads

Reporting, maintenance, ETL, and OLTP can share an instance. One setting pair serves all statements unless database-scoped choices, hints, or workload controls introduce exceptions. Separate the workloads in your measurements. A nightly index task should not decide the daytime application setting by itself.

I ask whether the business prefers faster individual reports or steadier interactive response during overlap. The answer shapes the test. Scheduling heavy work outside peak hours can be simpler than forcing every statement into the same parallelism pattern. Server settings are one tool among query tuning and workload planning.

Review After Hardware Changes

New processors, a VM resize, or an application release can change the right balance. Review MAXDOP and cost threshold after those events. Keep the original measurement method so a later DBA can compare like with like. A configuration standard should include a review trigger, not only two numbers.

Which user-visible operation improved after the change? If you cannot name one or show stable throughput, keep investigating. A good setting is one that fits the current workload and topology. The DBA should be able to explain why it was chosen without quoting a magic value from an old slide.

Related reading on this blog: SQL SERVER Performance: When MAXDOP = 1 Slowed Down the Entire Business and 3 Ways to Configure MAXDOP: SQL in Sixty Seconds #166.

A parallelism setting is not a speed dial, it is a workload control that needs measurement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.