Parallelism Calibration

Overview

Cost threshold for parallelism and max degree of parallelism are the two settings every DBA is told to change, with advice that has been “try 50” since about 2010.

They affect every query on the instance at once, which makes them the highest leverage and the highest risk knobs available, and they are currently turned on folklore. The one number that would let somebody pick a value is where their own workload’s costs actually sit, and no tool anywhere shows it.

The Parallelism Calibration report: the cost histogram with a draggable threshold
The histogram is the argument. The solid line is the threshold now, the dashed line is the one being proposed, and the colours say which side of both each cost band is on.

Almost every workload is a wall of trivial queries under cost 5 and a long thin tail above it, and the default threshold of 5 sits in the worst possible place: in the middle of the wall.

Where to find it

In the tree, right-click an instance and choose Instance Level Reports → Parallelism Calibration. It is a recommended report and appears in the Server Overview list.

Requirements

Requirement Why
SQL Server 2012 or newer Everything the page reads exists back to 2012.
VIEW SERVER STATE For the configuration, scheduler, wait and plan cache reads.
SQL Server 2016 for achieved DOP total_dop, last_dop and max_dop arrived there.
Access to each database for scoped MAXDOP Databases this login cannot reach are skipped.

There is no version gate on the page. The two things that vary by version each degrade to a stated absence rather than turning the page away.

Dragging the line

Drag the dashed line, or click anywhere in the plot. Three numbers move with it:

  • how many plans change side,
  • what share of daily executions that is,
  • and how much processor time those executions currently spend.

The headline follows while the line moves; the what-if table below recounts when it is dropped. Recounting a few thousand plans per mouse move is affordable, rebuilding the grid on every one of them is not.

That is what converts an argument into an experiment, and it produces a change record a change advisory board can actually read.

Reading the histogram

The horizontal axis is bucketed rather than linear in cost. Estimated cost spans four or five orders of magnitude on any real workload; a linear axis puts every bar in the first pixel and leaves the tail invisible, which is the half of the picture the threshold lives in. The bands are fixed, so two instances can be compared against each other and the scale does not move when the scan budget changes.

The vertical axis is executions per day, not plan count. A thousand plans that run once a night are not what a threshold change will be felt by; one plan that runs four million times is. Counting plans is what makes a calibration chart agree with itself and disagree with the server.

The colours are assigned from the two lines, not from the bucket:

Colour Meaning
Blue Serial now and serial at the proposed threshold
Amber Changes side at the proposed threshold
Purple Parallel now and parallel at the proposed threshold

A band is judged on its high edge, so it is only called serial when everything in it is.

Estimated cost is unitless

It is comparable to itself and to the threshold and to nothing else. A cost of 47 is not forty-seven of anything – not seconds, not pages, not rows. The axis label says so, because the one comparison being made here is exactly the comparison the unit supports.

The four views

The histogram stays above all four, because it is the calibration and the rest is the evidence for it.

Cost distribution

The what-if table: three candidate thresholds against what each would move, with the dragged one among them and marked.

The risk column is a sentence rather than a score, because the risk of a threshold change is entirely about which queries lose parallelism, and that is a fact about this workload. It names the largest query that would go serial.

Achieved DOP

What the queries actually got, against what they were allowed.

A query allowed 8 that consistently achieves 1 has a serial zone in it, typically a scalar function in the SELECT list, and Scalar UDF Inlining is the next click. Where the optimizer recorded a NonParallelPlanReason the row says which one.

Waits

CXPACKET against CXCONSUMER, which is the split that matters.

From SQL Server 2017 the benign half of CXPACKET – a consumer thread waiting for a producer, which is parallelism working correctly – is separated into CXCONSUMER. The two must never be added. Adding them is the classic misreading that ends with somebody setting MAXDOP to 1 on an instance that was fine.

LATCH_EX is listed and labelled as not all parallelism: some of it is the ACCESS_METHODS_DATASET_PARENT latch, which is parallel scan coordination, and sys.dm_os_latch_stats has the breakdown.

Hardware

Both settings as configured against in use, the guidance calculation shown as arithmetic rather than as a verdict, the NUMA nodes with their scheduler counts, and any database scoped MAXDOP.

The database scoped MAXDOP

This is the setting that most often explains why the instance level MAXDOP appears to be ignored, and it is invisible from the instance.

sys.database_scoped_configurations is database scoped and only ever answers for the database the connection is in, so the page loops the databases and reads each one inside its own error handler. A database that goes offline mid loop costs its own row rather than the whole report.

Where any database overrides MAXDOP, the banner says so and the Hardware view lists them.

The guidance calculation, shown as arithmetic

Machine Guidance
One NUMA node, 8 or fewer schedulers The scheduler count
One NUMA node, more than 8 8
Several NUMA nodes, 16 or fewer per node The count per node
Several NUMA nodes, more than 16 per node Half of that, capped at 16

The Hardware view prints the working, not just the answer, so the reader can disagree with it. Soft-NUMA on modern builds changes what “cores per NUMA node” means, and the page says which node layout it is reading.

How the recommendation is reached

The proposed line starts at the cost at the 95th percentile of the execution weighted distribution, rounded to something a person would type, and clamped between 25 and 100.

The percentile is the argument: below it is the wall of trivial queries that gain nothing from parallelism and pay for the attempt, above it is the tail where a parallel plan earns its coordination cost.

The clamp is not arbitrary. Below 25 the threshold is still inside the wall on almost every workload; above 100 it starts denying parallelism to genuinely large queries. A workload whose percentile lands outside that range is one where the reader should be moving the line themselves, which is what the line is for.

The recommendation is a script, never a button

Changing MAXDOP clears the plan cache for the affected scope. On a production instance that means a compilation storm followed by several minutes of every query being slower than it was. A one-click control that did that silently is the single worst thing this page could have.

The Copy script button produces both sp_configure statements, commented out, with:

  • what the proposed threshold would move, in plans, executions per day and processor time,
  • the hardware arithmetic behind the MAXDOP number,
  • the instruction to change one setting at a time, because moving both together makes the result uninterpretable,
  • every database that overrides MAXDOP and will not be affected,
  • and the query to check value against value_in_use afterwards.

Where the data comes from

Source What it gives
sys.configurations Both settings, configured and in use.
sys.database_scoped_configurations Per database MAXDOP overrides.
sys.dm_os_schedulers, sys.dm_os_nodes Scheduler count and NUMA layout.
sys.dm_os_sys_info Hyperthread ratio.
sys.dm_os_wait_stats CXPACKET, CXCONSUMER, CXSYNC_PORT, LATCH_EX and the rest.
sys.dm_exec_query_stats Executions, worker time, plan age, and achieved DOP.
sys.dm_exec_text_query_plan The StatementSubTreeCost and DegreeOfParallelism attributes.

Why this sweep is cheap

It reads one attribute out of each plan rather than shredding it. Only the root StatementSubTreeCost and the QueryPlan degree of parallelism are asked for, so the XQuery is two value() calls against a document the engine already has in memory rather than a walk of every operator. That is what makes a thousand plans affordable where a full shred would not be.

TRY_CONVERT to xml rather than CONVERT: a plan nested deeper than the 128 levels the xml type allows comes back NULL instead of ending the batch. Those are counted and reported as unreadable rather than quietly leaving the histogram short.

Plans are ranked by execution count rather than by cost, because the histogram is weighted by executions. A ranking by cost would sample the tail and miss the wall.

Messages you may see

The cost threshold for parallelism is 5. The default it has had since 1995, chosen for hardware nobody now owns.

MAXDOP is 0 on an instance with N schedulers. A single query can take every one of them.

RECONFIGURE has not been run since the value was changed. value and value_in_use disagree. The setting on the page is not the setting in force.

N databases override MAXDOP with a database scoped configuration. The instance setting does not apply to them. The Hardware view lists them.

N of the plans read carried no readable cost. A module created WITH ENCRYPTION, a plan nested past the 128 levels the xml type allows, or one that left the cache mid read. They are not in the histogram.

Report Why you would go there
Waits Where a large CXPACKET number is first noticed.
SQL CPU Schedulers Whether one query is holding every scheduler.
Scalar UDF Inlining The usual reason a query allowed 8 achieves 1.
Configuration Values Both settings as configured against in use, with the rest.
Memory Grants and Spills Grants are per query and are sized at compile time with DOP in mind.

Frequently asked questions

Why is the histogram empty? The plan cache has nothing in it, or nothing in it whose document would convert. A recent restart or DBCC FREEPROCCACHE produces this.

Why does the proposed threshold start where it does? At the 95th percentile of the execution weighted cost distribution, rounded and clamped between 25 and 100. Drag it wherever the evidence takes you.

The what-if table shows zero plans affected. Every plan in the cache is on the same side of both thresholds. That is a real answer: on this workload the change would do nothing.

Should I change both settings at once? No. Moving both together makes the result uninterpretable, and the cost threshold is the safer of the two to move first.

Is a high CXPACKET number a problem? Not on its own, and on SQL Server 2017 and newer not even mostly. CXCONSUMER is the benign half and is reported apart from it. What is left in CXPACKET after the split is genuine skew, and that is the number worth acting on.

Why is my instance’s cost distribution so different from the examples? Because it is your workload, which is the entire reason this page exists rather than a rule of thumb.

Most of my plans are unreadable. Why? WITH ENCRYPTION hides a module’s execution plan as well as its text, which is not obvious. On an instance built out of encrypted procedures the histogram covers only the code that is not encrypted, and the banner says how many plans that leaves out.