Share via

Regarding the stability of the execution plan generation

博雄 胡 685 Reputation points
2025-06-06T06:58:03.05+00:00

My English is not good, so I use a translator to communicate. Please excuse me if it's not accurate

I'm optimizing an sql,I created a few indexes, and the cost exceeded the parallel threshold, and it reported that the optimization was prematurely terminated due to a timeout.this plan was serial.

I used the force parallel hint, and it generated a parallel plan with a lower cost, but the execution was very slow.

I'm worried that if the table structure and index structure remain unchanged, it will adopt this parallel plan instead of the previous serial one during the next recompilation because the cost of this parallel plan is lower than that of the serial one.

  1. Is my concern possible?
  2. How can I avoid it (except for plan guides)?
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
198 questions
0 comments
Accepted answer
  1. Hleb Sukrystsik 230 Reputation points
    2025-06-06T08:08:20.97+00:00

    Hey. Regarding your questions:

    1. Is my concern possible? Yes, it is. SQL Server uses a cost-based optimizer, which means it picks the plan with the lowest estimated cost based on several factors:

    CPU cost: processing requirements

    I/O cost: disk and memory usage

    Row cardinality: number of rows estimated per step

    Memory usage: intermediate memory requirements

    So, even if a parallel plan performs worse in practice, SQL Server may choose it during recompilation if its estimated cost is lower than a serial alternative.

    2. How can I avoid it (besides plan guides)? If you have time, you could try to refactor the query or adjust indexes to encourage a more efficient serial plan. Reducing estimated row counts or avoiding scans can help.

    But for a quick and reliable solution, I’d recommend using OPTION (MAXDOP 1). This forces serial execution and avoids plan regressions during recompilation. Docs here.

    Another option, though broader in scope, is to increase the “cost threshold for parallelism” at the server level. The default is 5, but raising it makes parallel plans less likely overall. Be aware this affects all queries on the instance. Docs here.

    Hope this helps.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest

Your answer

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