Do Queries Always Respect Cost Threshold of Parallelism? – Interview Question of the Week #216

Question: Can a parallel plan have a displayed cost below the cost threshold for parallelism? Yes. The threshold is used when considering parallel alternatives, based on an estimate earlier in optimization. It is not a lower bound on the cost displayed for the final parallel plan.

This is one of the surprises I discuss in performance health checks. My original test used AdventureWorks2014, a threshold of 10, and a machine with eight logical processors. The final plan cost was around four, yet the plan was parallel.

The same query is:

USE AdventureWorks2014;
GO
SELECT sd1.UnitPriceDiscount
FROM Sales.SalesOrderDetail AS sd1
ORDER BY sd1.ProductID;

Enable the actual execution plan before running it. The original result was a historical observation on that server, not a guarantee that this query goes parallel on every AdventureWorks installation. The current companion uses AdventureWorks2025 and reads the active configuration without changing it.

SQL Server considers parallel alternatives when the relevant serial-plan estimate exceeds the threshold. A parallel alternative can then be cheaper, and the decision may use an estimate from an earlier optimization stage. Comparing only the final displayed parallel cost to 10 therefore tests the wrong assumption.

The reverse isn’t guaranteed either: a high estimated cost does not force a parallel plan. Available parallel alternatives, settings and query features still matter. Plan cost is an optimizer estimate, not elapsed seconds.

My old setup description also mixed MAXDOP 0 and 8. MAXDOP is a separate limit on degree of parallelism, not the cost threshold, and neither setting promises that every query will use every available processor. I have left server settings unchanged in the companion.

So the original “No, not always” answer is useful, but “SQL Server ignored the rule” is misleading. The rule is about considering alternatives, not requiring the winning plan’s cost to remain above the threshold.

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