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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 714968 Comments
Status Closed Workarounds
Type Suggestion Repros 1
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 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 Eric [MSFT] 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