Investigating Serial vs Parallel Executions in SQL Server with Query Store

---

One of the most common causes of query runtime variability in SQL Server is a query that flips between serial and parallel execution. One run uses a single thread and completes in two seconds. The next run uses eight threads, generates CXPACKET waits, and takes thirty seconds. The query is technically “the same query” but the optimizer chose differently based on statistics, parameter values, or server load at compile time.

Query Store captures is_parallel_plan on every stored plan, making it the right tool to confirm whether a query is flipping and to measure exactly how much each execution mode costs. This guide covers the complete investigation workflow from confirming the flip to testing fixes.

1 Serial vs Parallel: The Core Problem Beginner

Serial execution uses a single worker thread. The query runs predictably with low coordination overhead. Parallel execution splits the query across multiple threads, which can dramatically reduce elapsed time for large scans and joins but adds thread synchronization cost that shows up as CXPACKET and CXCONSUMER waits.

When a query compiles to a serial plan one time and a parallel plan the next, the results are unpredictable runtimes, user complaints that a query was fast yesterday but slow today, and difficulty tuning because the plan changes between investigations.

The root causes are almost always one of three things: the cost threshold for parallelism is set low enough that small changes in statistics estimates push a query over or under the threshold, parameter sniffing causes the optimizer to produce a plan optimized for one parameter value but executed against another, or server load at compile time affects the optimizer’s cost estimates.

2 Set the Time Window Beginner

All queries in this guide use a shared time window variable. Set it once at the top of the session and all subsequent queries respect it.

-- Set the analysis window: change @HoursBack to match the incident window
-- 4 hours covers most active incidents
-- 24 hours gives a broader picture of recurring variability
DECLARE @HoursBack INT  = 4;
DECLARE @Since DATETIME2 = DATEADD(HOUR, -@HoursBack, SYSUTCDATETIME());

3 Summary: Serial vs Parallel Execution Counts Beginner

Start here to confirm whether the server has a meaningful parallelism problem in the current window. This query aggregates across all queries and shows how total execution time is split between serial and parallel plans.

;WITH rs AS (
    SELECT rs.runtime_stats_id, rs.plan_id,
           rs.count_executions, rs.avg_duration,
           rs.last_execution_time
    FROM sys.query_store_runtime_stats rs
    WHERE rs.last_execution_time >= @Since
),
agg AS (
    SELECT
        CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END AS ExecType,
        SUM(r.count_executions)                    AS Executions,
        SUM(r.count_executions * r.avg_duration)   AS TotalDurationUs
    FROM rs                          r
    JOIN sys.query_store_plan        p ON p.plan_id = r.plan_id
    GROUP BY CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END
)
SELECT
    ExecType,
    Executions,
    CAST(TotalDurationUs / 1000000.0 AS DECIMAL(18,1)) AS TotalDurationSec
FROM agg
ORDER BY ExecType;

Example output:

ExecType	Executions	TotalDurationSec
Parallel	120	900.5
Serial	850	420.0

In this example 120 parallel executions consumed more than twice the total elapsed time of 850 serial executions. That imbalance is the signal to investigate which queries are driving the parallel load.

4 Queries That Flip Between Both Intermediate

This query finds every query_id that has both serial and parallel plan executions in the window. These are the specific queries with unstable parallelism decisions.

;WITH rs AS (
    SELECT r.plan_id, r.count_executions,
           r.avg_duration, r.last_execution_time
    FROM sys.query_store_runtime_stats r
    WHERE r.last_execution_time >= @Since
),
d AS (
    SELECT
        q.query_id,
        SUM(CASE WHEN p.is_parallel_plan = 1 THEN r.count_executions ELSE 0 END) AS ParallelExecs,
        SUM(CASE WHEN p.is_parallel_plan = 0 THEN r.count_executions ELSE 0 END) AS SerialExecs
    FROM rs                          r
    JOIN sys.query_store_plan        p ON p.plan_id  = r.plan_id
    JOIN sys.query_store_query       q ON q.query_id = p.query_id
    GROUP BY q.query_id
)
SELECT TOP 50
    d.query_id,
    d.SerialExecs,
    d.ParallelExecs,
    qt.query_sql_text
FROM d
JOIN sys.query_store_query_text qt
    ON qt.query_text_id = (
        SELECT TOP 1 query_text_id
        FROM sys.query_store_query
        WHERE query_id = d.query_id
    )
WHERE d.SerialExecs > 0 AND d.ParallelExecs > 0
ORDER BY (d.SerialExecs + d.ParallelExecs) DESC;

Example output:

query_id	SerialExecs	ParallelExecs	query_sql_text
2671001	100	50	UPDATE dbo.Certificates …
2672002	290	15	SELECT * FROM dbo.Orders WHERE …

Any query_id appearing in this result has an unstable parallelism decision. Take the query_id values forward into Section 5 to examine the individual plans.

5 Plan Detail for a Specific Query Intermediate

Set @QueryId to a query_id from Section 4 to see each plan, its serial or parallel classification, and its average runtime. The enhanced version also extracts the numeric DOP from the plan XML.

Basic version (no XML parsing required)

DECLARE @QueryId BIGINT = 2671001;  -- set to query_id from Section 4

SELECT TOP 100
    q.query_id,
    p.plan_id,
    CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END AS ExecType,
    rs.count_executions,
    CAST(rs.avg_duration AS BIGINT)  AS AvgDurationUs,
    rs.last_execution_time
FROM sys.query_store_plan            p
JOIN sys.query_store_query           q  ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats   rs ON rs.plan_id = p.plan_id
WHERE q.query_id = @QueryId
  AND rs.last_execution_time >= @Since
ORDER BY ExecType DESC, rs.last_execution_time DESC;

Enhanced version: extract numeric DOP from plan XML

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP 100
    q.query_id,
    p.plan_id,
    CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END AS ExecType,
    TRY_CONVERT(XML, p.query_plan).value(
        '(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/@DegreeOfParallelism)[1]',
        'INT'
    )                                                       AS DOP,
    rs.count_executions,
    CAST(rs.avg_duration AS BIGINT)                         AS AvgDurationUs,
    rs.last_execution_time
FROM sys.query_store_plan            p
JOIN sys.query_store_query           q  ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats   rs ON rs.plan_id  = p.plan_id
WHERE q.query_id = @QueryId
  AND rs.last_execution_time >= @Since
ORDER BY ExecType DESC, rs.last_execution_time DESC;

-- TRY_CONVERT avoids errors if a plan cannot be cast to XML for any reason
-- XMLNAMESPACES is required for ShowPlan XQuery to resolve correctly

Example output:

query_id	plan_id	ExecType	DOP	count_executions	AvgDurationUs
2671001	193	Parallel	8	50	500000
2671001	193	Serial	1	100	80000

This confirms the parallel plan at DOP 8 is six times slower than the serial plan. The parallel execution is adding more thread coordination overhead than it is saving through parallelism.

6 Wait Statistics by Execution Type Advanced

The wait statistics query joins Query Store wait stats to the runtime stats on both plan_id and runtime_stats_interval_id. This is the correct join key. Joining on plan_id alone produces incorrect aggregations.

Basic version (no XML)

;WITH rs AS (
    SELECT plan_id, runtime_stats_interval_id, last_execution_time
    FROM sys.query_store_runtime_stats
    WHERE last_execution_time >= @Since
)
SELECT
    p.query_id,
    p.plan_id,
    CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END AS ExecType,
    ws.wait_category_desc,
    SUM(ws.total_query_wait_time_ms)                        AS TotalWaitMs
FROM rs
JOIN sys.query_store_wait_stats      ws
    ON ws.plan_id                    = rs.plan_id
   AND ws.runtime_stats_interval_id  = rs.runtime_stats_interval_id
JOIN sys.query_store_plan            p  ON p.plan_id = rs.plan_id
GROUP BY
    p.query_id,
    p.plan_id,
    CASE WHEN p.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END,
    ws.wait_category_desc
ORDER BY p.query_id, ExecType, TotalWaitMs DESC;

Enhanced version: include numeric DOP from plan XML

;WITH rs AS (
    SELECT plan_id, runtime_stats_interval_id, last_execution_time
    FROM sys.query_store_runtime_stats
    WHERE last_execution_time >= @Since
),
p_dop AS (
    SELECT
        p.plan_id,
        p.query_id,
        p.is_parallel_plan,
        x.qp.value(
            '(/ShowPlanXML/BatchSequence/Batch/Statements/*/QueryPlan/@DegreeOfParallelism)[1]',
            'INT'
        ) AS DOP
    FROM sys.query_store_plan p
    OUTER APPLY (
        SELECT TRY_CONVERT(XML, p.query_plan) AS qp
    ) x
)
SELECT
    pd.query_id,
    pd.plan_id,
    CASE WHEN pd.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END AS ExecType,
    pd.DOP,
    ws.wait_category_desc,
    SUM(ws.total_query_wait_time_ms) AS TotalWaitMs
FROM rs
JOIN sys.query_store_wait_stats      ws
    ON ws.plan_id                   = rs.plan_id
   AND ws.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN p_dop pd ON pd.plan_id = rs.plan_id
GROUP BY
    pd.query_id,
    pd.plan_id,
    CASE WHEN pd.is_parallel_plan = 1 THEN 'Parallel' ELSE 'Serial' END,
    pd.DOP,
    ws.wait_category_desc
ORDER BY pd.query_id, ExecType, TotalWaitMs DESC;

Example output:

query_id	plan_id	ExecType	DOP	wait_category_desc	TotalWaitMs
2671001	193	Parallel	8	Parallelism	2283
2671001	193	Parallel	8	Latch	110
2671001	193	Serial	1	N/A	0

The Parallelism wait category dominating the parallel plan confirms that thread synchronization overhead is the cost. The serial plan has no significant waits. For this query, parallelism is hurting rather than helping.

7 Top Parallel Plans Right Now Beginner

This query surfaces the parallel plans consuming the most total elapsed time in the current window. Use it to prioritize which queries to investigate first when there are many candidates from Section 4.

SELECT TOP 20
    q.query_id,
    p.plan_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS TotalExecutions,
    CAST(
        SUM(rs.count_executions * rs.avg_duration) / 1000000.0
        AS DECIMAL(18,1)
    )                                                       AS TotalElapsedSec
FROM sys.query_store_plan                p
JOIN sys.query_store_runtime_stats       rs ON rs.plan_id  = p.plan_id
JOIN sys.query_store_query               q  ON q.query_id  = p.query_id
JOIN sys.query_store_query_text          qt ON qt.query_text_id = q.query_text_id
WHERE rs.last_execution_time >= @Since
  AND p.is_parallel_plan = 1
GROUP BY
    q.query_id,
    p.plan_id,
    qt.query_sql_text
ORDER BY TotalElapsedSec DESC;

References

• Microsoft Docs: sys.query_store_plan (is_parallel_plan column)
• Microsoft Docs: sys.query_store_wait_stats
• Microsoft Docs: Configure max degree of parallelism
• SQLYARD: A Beginner-to-Advanced Guide to Query Store
• SQLYARD: Query Store Complete Screen-by-Screen Guide
• SQLYARD: When SQL Server Is Slow: Triage Playbook