Home Dashboard Directory Help
Search

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


Status: 

Active


136
0
Sign in
to vote
Type: Suggestion
ID: 714968
Opened: 12/20/2011 11:25:27 PM
Access Restriction: Public
0
Workaround(s)
view

Description

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.
Details
Sign in to post a comment.
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.

Paul
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
Sign in to post a workaround.