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.

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


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.
Posted by Paul White NZ on 1/6/2018 at 2:05 PM
2017 CU3 https://support.microsoft.com/en-us/help/4051361/optimizer-row-goal-information-in-query-execution-plan-added-in-sql-se
Posted by geoff patterson on 11/21/2017 at 6:07 AM
Thank you for the update, and thank you for continuing to improve showplan!
Posted by Microsoft on 11/20/2017 at 7:43 PM
Thank you for the suggestion. In an upcoming 2017 CU, and in 2016 SP, we will expose more information about rowgoal impact.