Share via

maxdop of a query will fallback to serial or not.

Rajesh Kumar Yadav 20 Reputation points
2026-06-06T05:53:14.3433333+00:00

if a query has maxdop 2 : in this case

can microsoft decided in this situation to not to use parallelism and use all cores/or execute a query serially, as if parllelism is switched off

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator
    2026-06-06T08:22:27.0833333+00:00

    If a query has OPTION(MAXDOP 2), the query will never use more than two cores. There are several reasons why the query still could execute serially:

    1. The optimizer estimate that serial plan will have lower cost. Parallelism comes with overhead.
    2. The query cost is below the configuration setting cost threshold for parallelism.
    3. The query includes operations that blocks parallelism. (In this case, the operation may only block part of the plan from being parallel.)
    4. Even if the optimizer has a arrived at a parallel plan, SQL Server may make a run-time decision that there is not enough resources for a parallel plan, and still run a serial plan.
    5. The estimates for how to partition the data between the threads are completely wrong, so all data end up in one thread. This is the worst of them all. The execution is serial, but you still have all the overhead of paralellism.

    Was this answer helpful?

  2. AI answer

    2026-06-06T05:53:27.7433333+00:00

    MAXDOP is an upper bound, not a guarantee that parallelism will be used.

    If a query is submitted with MAXDOP 2 (via server setting, database scoped configuration, Resource Governor, or query hint), the optimizer is allowed to use up to 2 CPUs for parallel parts of the plan. However, the optimizer can still choose a serial plan, and at runtime the engine can also end up running with fewer workers than the MAXDOP value.

    Key points from the engine behavior:

    • MAXDOP controls the maximum degree of parallelism, not that parallelism must occur.
    • The optimizer will not use a parallel plan if, for example:
      • The serial plan is trivial or does not exceed the cost threshold for parallelism.
      • The serial plan has a lower total estimated subtree cost than any parallel plan considered.
      • The query contains operators that force serial execution.
    • Even when a parallel plan is chosen, the actual DOP at runtime can be lower than the configured MAXDOP if there are not enough worker threads available.

    Therefore, with MAXDOP 2 the engine can:

    • Execute the query serially (DOP 1) if that is cheaper or required by operators.
    • Execute the query in parallel with up to 2 CPUs.
    • It will not “use all cores” beyond the MAXDOP limit for a given parallel task, but more processors than the MAXDOP might be used concurrently across multiple tasks within the same request.

    So, yes: a query with MAXDOP 2 can legitimately fall back to serial execution, behaving as if parallelism were effectively off for that query, if the optimizer or runtime conditions dictate it.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.