Provide a hint to force generation of a parallel plan - by Paul White NZ

Status : 


Sign in
to vote
ID 714968 Comments
Status Active Workarounds
Type Suggestion Repros 2
Opened 12/20/2011 11:25:27 PM
Access Restriction Public


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.
Sign in to post a comment.
Posted by geoff patterson on 5/17/2017 at 10:48 AM
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 :)
Posted by Luiz Mercante on 3/28/2017 at 1:14 PM
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.
Posted by Microsoft on 3/2/2017 at 5:50 PM
This will be addressed in an upcoming SQL Server 2016 SP1 CU. Will provide details once available.
Posted by R Herring on 12/22/2016 at 6:32 AM
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.
Posted by John Hardin on 12/20/2016 at 5:09 PM
Sadly, while OPTION(querytraceon 8649) 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!
Posted by Grant Fritchey on 5/26/2015 at 9:12 AM
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.
Posted by Ostati on 12/16/2014 at 2:18 PM
Are there any plans at MS to go forward with this beautiful suggestion?
Posted by Georgi Kyuchukov on 12/1/2014 at 1:26 AM
I have major problem with this, too. Any updates?
Posted by Mitch Wheat on 6/8/2014 at 10:04 PM
Any update? It's been a couple of years....
Posted by GuillaumeSL on 2/20/2014 at 1:02 AM
Any update on this? a normal hint would be much better than some undocumented or short term workarounds.
Posted by Paul White NZ on 12/21/2011 at 3:39 PM
Hi Eric,

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.

Posted by Microsoft on 12/21/2011 at 10:50 AM
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(querytraceon 8649).

Best regards,
Eric Hanson
Program Manger
SQL Server Query Processing