Provide a hint to force generation of a parallel plan
The SQL Server query optimizer often chooses a serial plan (with a significant estimated cost) where a parallel plan would execute much faster.
Often, the desired parallel plan costs only very slightly higher than the serial version.
The root cause can usually be traced back to optimizer model limitations, other simplifying assumptions made by the costing component, unavoidable cardinality estimation errors in moderately complex plans,
or the use of operations that do not allow us to give the optimizer good information about the cardinality and distribution of output values (e.g. CLR functions).
To be clear, the motivation for this suggestion is for cases where all reasonable steps have been taken to ensure the information available to the optimizer is accurate
(e.g. computed columns, multi-column statistics, filtered indexes), and yet costing still estimates a high-cost serial plan option to be cheaper than the parallel alternative.
Examples are often decision-support or data-warehouse queries where SQL Server is running on fast storage, or where the working set of the database is generally expected to be in buffer pool.
These types of scenarios often result in currently unavoidable out-of-model conditions.
Currently, these cases require us to rewrite the query using arcane tricks to artificially raise estimated costs and/or cardinality estimates.
An alternative, though equally unacceptable current workaround is to use undocumented DBCC commands to artificially raise the costing multiplier for CPU costs,
capture the parallel plan produced, and specify that in a plan guide.
It is appreciated that future versions of SQL Server may seek to ameliorate these issues through model improvements,
but it seems there will always be cases where the optimizer makes the wrong choice, even if the reasons are understandable.
Upvotes: 220<=-=Dec 21 2011 10:50AM=-=>
Thanks for the suggestion. We will consider this for a future release. There is an undocumented trace flag 8649 to set the cost overhead of parallelism to 0. This may provide some relief, but is not a full solution. You can use it in a query hint in the form OPTION.
SQL Server Query Processing
Thanks for the very quick response. TF 8649 is indeed very helpful – perhaps we’re not so far away from seeing a PARALLEL_PLAN or MINDOP hint as I thought! Very cool, thanks again.
Paul<=-=Feb 20 2014 1:02AM=-=>
Any update on this? a normal hint would be much better than some undocumented or short term workarounds.<=-=Jun 8 2014 10:04PM=-=>
Any update? It’s been a couple of years….<=-=Dec 1 2014 1:26AM=-=>
I have major problem with this, too. Any updates?<=-=Dec 16 2014 2:18PM=-=>
Are there any plans at MS to go forward with this beautiful suggestion?<=-=May 26 2015 9:12AM=-=>
There is an extremely strong use case for this with Columnstore indexes. I can show queries with an estimated cost of .7 that get a major performance boost by going from row mode to batch mode processing within the Columnstore index.<=-=Dec 20 2016 5:09PM=-=>
Sadly, while OPTION works, it requires elevated privileges, or using it in a SP owned by SA… My testing shows it really helps, but I can’t incorporate it into our application due to the permissions issue.
Please, PLEASE add a supported PARALLEL_PLAN query hint to a SQL2016 service pack!<=-=Dec 22 2016 6:32AM=-=>
I have mixed feelings about this. I work with developers that look at query hints as the first answer not the last resort. NoLock, Index hints, Join hints, whatever magic they happen to read about that “solved” some particular problem.
This would be a very sharp tool to put in the hands of even experienced developers.
However, I voted in favor.
This will be addressed in an upcoming SQL Server 2016 SP1 CU. Will provide details once available.<=-=Mar 28 2017 1:14PM=-=>
I have a 4 milion rows clustered index scan in serial, even using 8649 and Adam Machanic function trick. Tried also DBCC SETCPUWEIGHT. Using 13.0.4001.0 Enterprise Edition. Works only using insert into, 10x faster in parallel. Thanks.<=-=May 17 2017 10:48AM=-=>
It looks like an undocumented ENABLE_PARALLEL_PLAN_PREFERENCE option is now available via USE HINT in SQL Server 2016 SP1 CU2 and 2017 CTP2. Now we just need it to be documented :)