Obscure optimizer's behaviour - by M2M telematics

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<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 743136 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 5/22/2012 7:48:41 AM
Access Restriction Public



I've found very intresting omptimizer's behaviour.
Could you explain what exactly happens?

The test was produced on SQL Server 2012. See "Steps to Reproduce".

On your hardware the numbers can be different, but the result of test is permanent and necessary numbers can be found to reproduce test as well.

I don't know is this bug or not, but I want to know why this happens and how I can control it.
Sign in to post a comment.
Posted by Microsoft on 6/26/2012 at 5:55 PM
At the top of the plan there is a Parallelism/Gather Streams operator. The estimated executon cost of that operator is affected by the width of the rows being processed which in turn is affected by the number in the type cast. The larger the number, the wider the rows, the higher the cost. At a particular size, the cost of the parallelism operator reaches so high that the serial plan has a lower execution cost and the plan choice flips. Optimize the query with different values such as 100,200,300,400 to see how the cost of the parallelism operator changes.

SQL Development
Posted by Paul White NZ on 5/23/2012 at 12:09 AM
For query 1, the serial plan estimated cost is 10.6047; getting a parallel plan with TF 8649, the estimated cost is 12.0579. So, the optimizer chooses the serial plan alternative - the lower cost of the parallel scan is not enough to pay for the Gather Streams operator.

If your cost threshold for parallelism is set to 11 or higher, the plan will be a TRIVIAL one, and parallelism will not even be considered.
Posted by Paul White NZ on 5/22/2012 at 8:22 AM
The tipping point for parallelism for me on that query (also on SQL Server 2012) is 2257. The estimated cost for the parallel nvarchar(2257) plan is 11.2158; for the serial nvarchar(2258) plan it is 11.2162. It is often possible to find the 'edge' where the costing component produces very close costs for serial and parallel options by doing this sort of thing.

The cost savings from performing a parallel scan are offset by the increased estimated cost of the conversion in the Compute Scalar. The estimated CPU cost of that conversion is related to the size of the data type, so it makes sense that varying that parameter affects the balance between serial and parallel options.