Query plans should provide more transparency into "row goals" - by geoff patterson

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


4
0
Sign in
to vote
ID 1008425 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 10/22/2014 10:02:53 AM
Access Restriction Public

Description

Currently, SQL Server adjusts the "Estimated Number of Rows" and "Estimated Subtree Cost" when applying row goals, but does not explicitly declare that a row goal has been applied to an operator.  It also leaves other properties (e.g. "Estimated I/O Cost") in an inconsistent state.

While it is obvious that very simple queries like the one below would use a row goal, there can be cases where a query is considerably more complex (e.g. using a COUNT DISTINCT) and SQL Server is able to optimize the query in a way that uses row goals.  This may be a good optimization in some cases, but may yield very poor performance in others.  

By providing more transparency into the fact that row goals are being applied and changing the optimizer's cost-based decisions, SQL Server can better enable developers to understand and improve their queries.  See the attached test script for a detailed illustration of the inconsistencies and confusion the current behavior creates.
Sign in to post a comment.