SQL SERVER – Row Goal and Performance

During the recent consulting engagement Comprehensive Database Performance Health Check with SQL Server 2017/2019, my client asked me if I can help them enable the new feature of Row Goal. This was a very interesting question to me and realize that many people may know to have clarity about what is the Row Goal and what is the impact of it on the performance of SQL Server.

What is Row Goal?

When the query optimizer estimates the cost of a query execution plan, usually it qualifies all the rows from all the sources (tables, views, functions etc) and builds the execution plan. There are certain keywords (like Top, FAST, IN, EXISTS etc) that cause the query optimizer to build an execution plan that returns a smaller number of rows. When SQL Server optimizer encounters such operators, it uses a mechanism called row goal which estimates a lesser number of rows and builds an estimated query plan for it.

In simple words, when you write a query with certain keywords, SQL Server uses row goals to build a better and efficient execution plan which is more suitable for less number of rows.

The Row Goal always existed in SQL Server for many different versions. However, the latest version of SQL Server 2019 has introduced the new EstimateRowsWithoutRowGoal query execution plan attribute, which informs us if query plan is build with the help of Row Goals are not. This attribute of EstimateRowsWithoutRowGoal is available for SQL Server 2017 Cumulative Update 3 (CU3) and Microsoft SQL Server 2016 Service Pack 2 (SP2) as well.

Row Goals in Action

Let’s see a quick demonstration of SQL Server sample database WideWorldImporters. First, we will run the query as it is. Note that I am using the latest SQL Server Management Studio 18.4 and SQL Server 2019 CU1 (which was the latest when I was writing this blog post).

Here is the query which I ran while keeping the execution plan turned on.

-- Writing a Regular SELECT Statement
SELECT TOP 25 *
FROM [Sales].[Invoices] i
GO

You can see in this example if that SQL Server already knows due to the keyword top 25 it has to retrieve only 25 rows it has estimated only 25 rows to read and an actual number of rows was only 25.

Let us run the following query which uses the hint to disable row goal.

-- Disable Rowgoals with Hints
SELECT TOP 25 *
FROM [Sales].[Invoices] i
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

You can see in this example if that SQL Server does not know how many rows it has to retrieve even though there is a TOP 25 keywords and used in the query. Due to this reason, it estimates that it will be retrieving a lot more rows and builds the execution plan according to it. However, in all reality eventually, it will be only reading 25 rows.

Well, that’s it. If we disable the row goal attribute of the optimizer, there is a pretty good chance that we will end up with the poor performance of most of the queries as the estimated execution plan will be always for all the qualified rows.

If you are using multiple tables and joining them together, you may find your entire estimated plan to use very different operators when the hint DISABLE_OPTIMIZER_ROWGOAL is used. We will discuss this topic on future blog posts.

Summary

Do not use hint DISABLE_OPTIMIZER_ROWGOAL as for the most queries you do not need to disable the feature of Row Goal. If you need further help, you can always reach out to me via the Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)