Optimizer row goal information in query execution plan added in SQL Server 2014, 2016 and 2017

Applies to: SQL Server 2016 StandardSQL Server 2016 DeveloperSQL Server 2016 Enterprise More

Summary


Add the new EstimateRowsWithoutRowGoal query execution plan attribute to SQL Server 2017 Cumulative Update 3 (CU3) and Microsoft SQL Server 2016 Service Pack 2 (SP2).

When the Query Optimizer estimates the cost of a query execution plan, it usually assumes that all qualifying rows from all sources have to be processed. However, some queries cause the Query Optimizer to search for a plan that will return a smaller number of rows faster. This can occur if the query uses a TOP clause, FAST number_rows query hint, an IN or EXISTS clause, or a SET ROWCOUNT { number | @number_var } statement. In this case, the optimizer is using a row goal when it estimates the query plan. If the row goal plan is applied, the estimated number of rows in the query execution plan is reduced. This is because the plan assumes that a smaller number of rows will have to be processed in order to reach the row goal.

Note The estimate that's made by using row goal is approximate. If the actual data distribution is non-uniform, it may be less than the actual number of rows that have to be processed. Therefore, it could create an inefficient query plan. As a workaround for this issue, you can use the  USE HINT (‘DISABLE_OPTIMIZER_ROWGOAL’) query option or Trace Flag 4138.

For more information, see Update introduces USE HINT query hint argument in SQL Server 2016. However, it may be challenging to identify in complex queries whether a row goal is applied and how much it affects the plan choice.

By using the EstimateRowsWithoutRowGoal attribute, you can compare the results to those of the EstimateRows attribute to see how much row goal affects the Query Optimizer estimates.

Note If row goal is not applied to a particular query plan operator, this attribute is not included.

More Information


This update is included in the following cumulative update for SQL Server:

       Cumulative Update 3 for SQL Server 2017

Service pack information for SQL Server

This update is introduced in the following service packs for SQL Server:

       Service Pack 2 for SQL Server 2016

       Service Pack 3 for SQL Server 2014

References


Learn about the terminology that Microsoft uses to describe software updates.