EstimateRowsWithoutRowGoal helps you see: is it a statistics problem?

on March 7, 2018

SQL Server Management Studio version 17.5 adds a welcome feature for execution plans: a new visual attribute named EstimateRowsWithoutRowGoal.

EstimateRowsWithoutRowGoal was added to the XML for execution plans as of SQL Server 2017 CU3.

What’s a row goal?

Sometimes the SQL Server optimizer notices something in a query that indicates it’ll pull back a limited number of rows.

This could be a TOP, an IN or exists clause, or something like a FAST hint on the query.

A simple example: a query with a FAST hint

Here’s our example query:

with NameRank AS (
    SELECT 
        ReportYear,
        RANK () OVER (PARTITION BY ReportYear ORDER BY NameCount DESC) as RankByReportYear,
        Gender,
        FirstNameId,
        NameCount
    FROM agg.FirstNameByYear AS fnby
)
SELECT
    NameRank.ReportYear, 
    NameRank.RankByReportYear,
    NameRank.Gender, 
    fn.FirstName,  
    NameRank.NameCount
FROM NameRank
JOIN ref.FirstName as fn on NameRank.FirstNameId=fn.FirstNameId
WHERE RankByReportYear <= 3
ORDER BY ReportYear DESC, RankByReportYear ASC
OPTION (FAST 1);
GO

At first glance, the plan might look like we have a statistics problem

Highlighting one of the bars flowing through the plan, there’s a big difference between the estimated number of rows and the actual number of rows.

We might think this is a statistics issue at first glance.

You can now confirm if there’s a row goal at play - but you need to go into operator properties

One important thing to know is that the row goals don’t show up on those bars between queries or the tooltips in SSMS.

You need to select an operator in the plan, then look at its properties.

In this case, the properties show that if we hadn’t set a row goal, the estimated number of rows would have been a whole lot closer to the actual rows– at least for this operator in the plan.

Don’t just look at row goals in one part of the plan

If I go farther to the left in my plan, things are different for the nested loop join!

At this point in the plan, the row goal is closer to the actual number of rows than the estimate would have been if the query didn’t have the fast hint.

If we’re thinking about tuning this query, it helps to be aware of how the row goal may be impacting different parts of the plan, so the detailed information at the operator level is very helpful.

Does EstimateRowsWithoutRowGoals appear in estimated plans?

Yes. You get ‘Estimated Number of Rows’ and ‘EstimateRowsWithoutRowGoal’ in an estimated plan (but not ‘Actual Number of Rows’.

Does EstimateRowsWithoutRowGoals show up in Query Store?

Yes - you get ‘Estimated Number of Rows’ and ‘EstimateRowsWithoutRowGoal’ in the plan in Query Store, too.

Why isn’t it named ‘Estimated Rows Without Row Goals?’

Maybe developers have to pay by the letter for property names these days? Just guessing.

By the way, I’m not saying you should use FAST hints everywhere

FAST hints are simply a fun way to show and play with row goals.

I dig more into these hints and other ways to set row goals in the course Query Tuning with Hints and Optimizer Hotfixes. If you just can’t get enough row goals, you can also check out Paul White’s article on SQLPerformance.com.

What were the top 3 names from the last few years?

Emma and Olivia are on top for names in the last few years.

It is interesting that we do have a lot more name diversity in the United States in recent years than we used to have. If you change the sorting up, there were 99,680 Linda’s born in 1947!