Paul White

Setting and Identifying Row Goals in Execution Plans

February 12, 2018 by in SQL Optimizer, SQL Performance, SQL Plan | 1 Comment
Database smarter with deeper insights for Microsoft SQL
SentryOne eBooks

In these books, you will find useful, hand-picked articles that will help give insight into some of your most vexing performance problems. These articles were written by several of the SQL Server industry’s leading experts, including Paul White, Paul Randal, Jonathan Kehayias, Erin Stellato, Glenn Berry, Aaron Bertrand, and Joe Sack.

Free Download →

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts →

Introduction

The SQL Server product documentation is a bit light on the topic of row goals. The main official references are in:

When people ask for more information than is contained there, I normally refer them one or more of the following:

To summarize briefly: The row goal feature allows the optimizer to generate an execution plan (or part(s) of an execution plan) with the aim of returning a certain number of rows quickly. This is in contrast to the normal behaviour (without a row goal), which aims to find a plan optimized for the complete potential result set.

A row goal strategy generally means favouring non-blocking navigational operations (for example, nested loops joins, index seeks, and lookups) over blocking, set-based operations like sorting and hashing. This can be useful whenever the client can benefit from a quick start-up and steady stream of rows (with perhaps a longer overall execution time – see Rob Farley's post above). There are also the more obvious and traditional uses e.g. in presenting results a page at a time.

Naturally, there is an element of risk involved with a row goal plan. If everything plays out broadly as the optimizer expects (given the information available, and the modelling assumptions made), the execution plan will start streaming the requested number of rows more quickly and efficiently than would have been the case without the row goal.

Unfortunately, when the row goal strategy goes wrong, it can be a performance disaster (see Bart Duncan's post). This can happen, for example, when the optimizer has incomplete information, encounters an unfavourable data distribution, or makes an unsafe assumption. In any case, the cause of the poor performance is almost always that many more rows need to be processed at execution time than the optimizer expected.

It can be very useful to identify execution plan areas affected by a row goal, because it helps us understand why the optimizer made the choices it did. This is particularly important when the row goal logic produces an adverse outcome. Without understanding the role played by the row goal, it might look as if the optimizer simply underestimated the number of rows, leading people to look in the wrong places (e.g. statistics) for a root cause.

Setting Row Goals

It is a lot easier to look for row goal effects if one knows what sorts of things might cause a row goal to be set in the first place. The official documentation often talks about row goals being associated with the keywords TOP, FAST, IN, and EXISTS. This can leave the reader with an incomplete or misleading understanding, so it is worth taking a moment to clarify some aspects.

I want to emphasize right up front that using specific T-SQL keywords in a query does not guarantee that a row goal will be set. The official documentation mentions certain keywords to help people identify common scenarios where row goals may be introduced, without getting into too many technicalities.

A second general point to bear in mind is that a row goal is only set when the goal would be less than the regular estimate. After all, there is not much point generating a plan fragment optimized for 100 rows if the whole thing is only expected to produce 50 rows anyway. To be extra clear, this point always applies to all the ways a row goal can be set. If you are expecting a row goal, but do not see one, this is a likely cause.

Finally, for the preamble, note that row goals are a cost-based optimization thing; a row goal affects optimizer choices, so if there are no choices to be made (i.e. a trivial plan) there is no row goal effect.

Let's now look at the things that can set a row goal:

FAST and TOP

Using the FAST query hint is a reliable way to set a row goal at the root of the execution plan (subject to the general exceptions noted above). A SET ROWCOUNT n statement also sets a similar top-level row goal (when n is not zero of course) for the statements it applies to.

Writing a TOP clause in a query also very often results in a row goal. As long as the finished execution plan features a physical Top operator, it is likely that at least a portion of the plan below the Top operator was affected by a row goal (again, the general terms and conditions apply).

Note that Top operators introduced by the query optimizer (without a query-specified TOP clause) can also set a row goal. This is important, because there are all sorts of ways that this can happen, for example when filtering on a simple row number, as shown in the following AdventureWorks query:

SELECT
    THN.RowNum,
    THN.TransactionID 
FROM 
(
    SELECT 
        TH.TransactionID, 
        RowNum = 
            ROW_NUMBER() OVER (
                ORDER BY TH.TransactionID ASC)
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = 400
) AS THN
WHERE
    THN.RowNum >= 10
    AND THN.RowNum < 20
ORDER BY
    THN.RowNum ASC;

The execution plan for that query includes a Top operator added by the optimizer (to limit the number of rows processed to 20):

Row Number Top

Top-related row goals need not appear at the root of the execution plan. This is one reason some part(s) of a plan may be subject to a row goal, while other parts are not.

IN and EXISTS

The documentation mentions IN and EXISTS because these are two common ways to express a semi join or anti join in T-SQL. Using either keyword in a way that does not produce a semi or anti join will not set a row goal.

T-SQL does not provide us with a way to write a semi or anti join directly (though interestingly U-SQL does), so we have to use indirect syntax like this instead (Aaron Bertrand reviewed the main T-SQL options in an earlier article).

For example:

-- Using IN
SELECT P.ProductID
FROM Production.Product AS P
WHERE P.ProductID IN
(
    SELECT TH.ProductID
    FROM Production.TransactionHistory AS TH
);

-- Using EXISTS
SELECT P.ProductID
FROM Production.Product AS P
WHERE EXISTS
(
    SELECT * 
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = P.ProductID
);

-- Using INTERSECT (also removes duplicates but P.ProductID is a key)
SELECT P.ProductID
FROM Production.Product AS P
INTERSECT
SELECT TH.ProductID
FROM Production.TransactionHistory AS TH;

All three forms produce exactly the same execution plan, featuring a semi join:

Semi join execution plan

Again, it is the semi/anti join that is important from a row goal perspective, not the T-SQL keywords. The following query also produces an identical semi join execution plan using only DISTINCT and regular INNER JOIN:

SELECT DISTINCT
    P.ProductID
FROM Production.Product AS P
INNER JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID;

Similar examples can easily be constructed for anti join (though care is needed when using NOT IN).

As a second (much less common) example, the IN and EXISTS keywords can also be used with IF to generate a semi join:

IF EXISTS (SELECT * FROM Production.TransactionHistory WHERE ProductID = 331)
    PRINT 'Row found';

IF 331 IN (SELECT ProductID FROM Production.TransactionHistory)
    PRINT 'Row found';

IF 331 = ANY (SELECT ProductID FROM Production.TransactionHistory)
    PRINT 'Row found';

All of the above produce the same execution plan featuring a nested loops semi join:

Semi join with probe from IF

Whichever way the semi/anti join is expressed, it is less likely than TOP or FAST to result in a row goal. I will dedicate a separate post to how this works in detail, and why these types of join can activate the row goal logic.

Identifying Row Goals

In my experience, it is very common for people to miss the impact of row goals in execution plans.

No wonder, since before SQL Server 2017 CU3 (update: now ported to 2016 SP2 and 2014 SP3), there was no documented way to see information about row goals in execution plans! That update includes:

This enhancement adds the EstimateRowsWithoutRowGoal attribute to each plan operator affected by a row goal.

The new attribute is visible in all the usual places (e.g. DMVs, plan cache) but not yet in SQL Server Management Studio graphical plans (up to and including SSMS version 17.4). The SQL Server does send the new attribute, but SSMS graphical plans strip bits out that do not match its local version of the xml plan schema.

Version 17.5 of SSMS is expected to ship with an updated xml schema and UI elements, making the new attribute visible. SentryOne Plan Explorer shows the new attribute in the raw xml view already, since it doesn't have any reason to strip the information out, but an update will be required to incorporate the new row goal information into other places (such as the Plan Diagram). I would expect it to make its way onto tooltips first.

The above SSMS limitations apply to plans obtained by requesting an estimated or actual graphical execution plan in the UI. Looking at the xml underlying the graphical plan will also not show the new attribute.

However, requesting raw xml showplan output separately will not strip the new attribute. This can be achieved with e.g. SET SHOWPLAN_XML ON for an estimated plan, or SET STATISTICS XML ON for an actual plan. In either case, the SSMS option to show graphical actual plans must be turned off to prevent SSMS intercepting the xml and stripping it. Clicking the xml result will open in xml view, not as a graphic, since showplan schema validation fails.

Inferring the presence of a row goal

It is possible that the new row goal plan information will be added to previous SQL Server versions in a future update, but there is no official news on that at the time of writing. In the meantime, we are left with trying to infer the presence of a row goal from other information available in execution plans. There are many ways to attempt this, but none is particularly complete or convenient.

For example a row goal may be active:

  • Where the Estimated Operator Cost attribute is less than the sum of the Estimated I/O Cost and the Estimated CPU Cost attributes, multiplied by the Estimated Number of Executions as necessary
  • Where the Estimated Number of Rows property of an index or table scan (without a residual predicate) is less than the Table Cardinality property. The cardinality estimate for an unrestricted scan can normally be expected to match the cardinality of the underlying object; where there is a difference, a row goal may be responsible.
  • Users of SQL Server 2016 SP1 onward may also infer the presence of a row goal by comparing the Estimated Number of Rows property for a data access operator to the Estimated Number of Rows to be Read property. This also only applies to data access operators without a residual predicate (but usefully includes Index Seeks).

People also sometimes assume a row goal will be present under any Top operator in a plan. This is an incomplete approach, and not always accurate, but it does have the advantage of being simple (and better than nothing).

We can also compare the plan of interest with one obtained when the row goal feature is disabled (using trace flag 4138 or the relatively new DISABLE_OPTIMIZER_ROWGOAL query hint). This is a reasonable idea, but the second plan is often so different from the first that no meaningful comparison can be made. It can at least prompt further investigation, I suppose.

The undocumented trace flag option

Where a detailed plan analysis is required, my go-to tool for looking at row goals has long been a combination of undocumented trace flags, which show at the optimizer's output operator tree.

Let's look at an example (that also shows how a row goal is only set when the goal is less than the regular estimate).

The following toy AdventureWorks query has an estimate of 29 rows (without a row goal):

SELECT
    TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
    TH.Quantity = 100;

The execution plan is:

29 row estimate without row goal

Notice that the filter on Quantity has been pushed into the scan as a residual predicate. Modern versions of SQL Server have the attributes Estimated Number of Rows = 29 and Estimated Rows to be Read = 113,443 on the Clustered Index Scan to indicate that 113,443 rows will be processed to finally produce 29 that pass the filter.

Modifying the query to ask for 28 rows using TOP sets a row goal:

SELECT TOP (28)
    TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
    TH.Quantity = 100
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612);

The trace flags combine to produce output like the following (in the SSMS messages tab):

*** Output Tree: ***
PhyOp_Top NoTies 
[ Card=28 Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.742065 ]
    PhyOp_Filter 
    [ Card=29 Cost(RowGoal 28,ReW 0,ReB 0,Dist 0,Total 0)= 0.742063 ]
    PhyOp_Range TBL: Production.TransactionHistory(alias TBL: TH)
    [ Card=113443 Cost(RowGoal 109531,ReW 0,ReB 0,Dist 0,Total 0)= 0.689488 ]
    ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [TH].Quantity
        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=100)
    ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=28)
    ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

That output shows:

  • The TOP (28) itself has no row goal (since it is expected to produce all 28 of its possible rows).
  • The Filter (on Quantity = 100) has a row goal of 28 set. Without the row goal the estimate is 29 rows as before.
  • The index range scan has a row goal of 109,531. This is the number of rows the optimizer expects to have to pass to the Filter to meet the Filter's goal of 28 rows. Without the row goal, the index range scan estimate is 113,443 rows (the total cardinality of the table).

Using adjusted row goal numbers (and derived estimated costs) is what biases optimizer choices toward a non-blocking navigational strategy. The revised calculations do not change the plan shape in this toy example, simply because there is no cheaper strategy available to deliver 28 rows (instead of 29). In more complex (= realistic) queries, the effect of the row goal can be very significant.

The new plan information available in SQL Server 2017 CU3 does not provide quite the same level of detail as the trace flags (2017 CU3 showplan output abbreviated for clarity):

Note the new row goal attribute. Unfortunately, pushing the filter into the scan as a residual has resulted in a loss of information about the scan row goal (109,531). This leaves us with slightly misleading information in the EstimatedRowsRead attribute. Perhaps a future update will address this issue (maybe by adding a EstimatedRowsReadWithoutRowGoal attribute!) who knows.

In the meantime, we can use another undocumented trace flag (9130) to prevent pushing the filter into the scan as a residual predicate, purely for plan diagnostic purposes. The showplan information is then:

This now contains the same level of information as the undocumented trace flag combination. The estimated graphical plan (with TF 9130) is:

Row goal plan with TF 9130 enabled

Running the same query with TOP (29) shows there is no row goal when the goal is equal to or greater than the regular estimate:

SELECT TOP (29)
    TH.TransactionID
FROM Production.TransactionHistory AS TH
WHERE
    TH.Quantity = 100
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607, QUERYTRACEON 8612, QUERYTRACEON 9130);

The messages tab output is:

*** Output Tree: ***
PhyOp_Top NoTies 
[ Card=29 Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.768451 ]
    PhyOp_Filter 
    [ Card=29 Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.768448 ]
        PhyOp_Range TBL: Production.TransactionHistory(alias TBL: TH)
        [ Card=113443 Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.713995 ]
        ScaOp_Comp x_cmpEq
           ScaOp_Identifier QCOL: [TH].Quantity
           ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=100)
     ScaOp_Const TI(bigint,Null,ML=8) XVAR(bigint,Not Owned,Value=29)
     ScaOp_Const TI(bigint,ML=8) XVAR(bigint,Not Owned,Value=0)

All the RowGoal attributes are now set to zero, indicating no row goal. The SQL Server 2017 CU3 showplan xml contains:

The absence of any EstimateRowsWithoutRowGoal attributes here indicates that no row goals were set.

Summary and Final Thoughts

There are many ways for an execution plan to be affected by one or more row goals in different areas of the plan. Sometimes the row goal effect will be beneficial, delivering the requested number of rows with low latency and high efficiency. On other occasions, the row goal influence can lead to performance problems.

It is not always possible to tell whether a row goal affected the optimizer's plan selection simply by looking at the T-SQL query text. The least well-understood of the possible row goal causes is probably the presence of a semi or anti join, which I will talk about in more detail separately.

Prior to SQL Server 2017 CU3, there was no really reliable way to detect a row goal, at least not without resorting to undocumented trace flags (and knowing how to match up and interpret the output). The new showplan attribute should be very useful for people running a recent enough version, and it will be even more useful once client tools are updated to display it. Hopefully, this enhancement will be made available for older releases as well.