How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170

Question: Can I force SQL Server to use a parallel plan for a query, and should I?

Answer: During a database performance health check, a customer asked how to make a serial query use several processors. The useful interview answer is the question I asked back: what evidence says a parallel plan would improve this query? Parallelism can reduce elapsed time for some work, but it can also spend more total CPU and add exchange overhead.

Whether a query goes parallel depends on statistics, indexes, row counts, the cost threshold, server settings and available workers. The same query text can be serial on one server and parallel on another.

For a supported comparison, MAXDOP 1 requests a serial plan, while MAXDOP 4 permits up to four workers for parallel portions. The latter is a ceiling, not a command to make a parallel plan. Run this only in a test environment with the same data and settings that produced the concern; examine actual plans and compare CPU time, elapsed time and logical reads over repeated executions.

USE WideWorldImporters;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT OrderID, CustomerID
FROM Sales.Orders
WHERE CustomerID > 100
ORDER BY OrderID
OPTION (MAXDOP 1);

SELECT OrderID, CustomerID
FROM Sales.Orders
WHERE CustomerID > 100
ORDER BY OrderID
OPTION (MAXDOP 4);
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

On SQL Server 2025 this Orders query stays serial with both hints. That’s exactly why MAXDOP 4 must not be described as forcing parallelism. You’ll also find undocumented trace flags and hints that push the optimizer toward a parallel plan. They are not an answer to hand to an application team. If the real query is slow, I inspect why its chosen plan is expensive before trying to make that plan use more workers.

A Lab With Enough Work to Go Parallel

A private two-million-row aggregate gives a clearer comparison. It requires SQL Server 2022 or later, compatibility level 160 or higher for GENERATE_SERIES, and enough tempdb space.

USE tempdb;
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#ParallelAggregateLab') IS NOT NULL
    THROW 50001, 'The private demonstration table already exists.', 1;
CREATE TABLE #ParallelAggregateLab
    (ItemID int NOT NULL, GroupID int NOT NULL, Amount bigint NOT NULL);
INSERT #ParallelAggregateLab (ItemID, GroupID, Amount)
SELECT value, value % 1000, value % 10000
FROM GENERATE_SERIES(1, 2000000);
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT GroupID, SUM(Amount) AS TotalAmount, COUNT_BIG(*) AS ItemCount
FROM #ParallelAggregateLab GROUP BY GroupID ORDER BY GroupID
OPTION (MAXDOP 1);
SELECT GroupID, SUM(Amount) AS TotalAmount, COUNT_BIG(*) AS ItemCount
FROM #ParallelAggregateLab GROUP BY GroupID ORDER BY GroupID
OPTION (MAXDOP 4);
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
DROP TABLE #ParallelAggregateLab;

Three alternating runs for each hint returned the same 1,000 groups. Serial CPU/elapsed times in milliseconds were 328/386, 296/304 and 297/313; parallel runs were 250/106, 375/124 and 376/119. Both read 6,192 pages. In this lab the parallel plans finished sooner, while total CPU varied. Those numbers show why the chosen plan and repeated results matter more than the hint’s name.

Want to try this yourself? Here is how to install WideWorldImporters.

MAXDOP is not a switch that turns parallelism on, it is a ceiling, and the evidence decides whether more workers help.

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