Today, we are going to discuss an interesting case related to the Rowgoal Optimizer in SQL Server. While this scenario may not occur frequently, it is important to understand how this feature works and its impact on query performance.

Recently, one of my clients faced an issue after upgrading to SQL Server 2019. Although they experienced overall improved performance, one specific query was running slower. After careful analysis and attempts to optimize the query, I discovered that disabling the Rowgoal Optimizer actually improved the query’s performance.

Before we dive into the details, let’s briefly understand what the Rowgoal Optimizer is. The Rowgoal Optimizer is a feature introduced in SQL Server 2019 that aims to improve query performance by setting a goal for the number of rows to be returned. This helps the optimizer choose a more efficient execution plan.

In the case I encountered, disabling the Rowgoal Optimizer for the specific query resulted in faster execution. However, it is important to note that this may not be a generic solution and should be tested on a case-by-case basis.

Let’s take a look at an example query to understand how to disable the Rowgoal Optimizer:

SELECT TOP 100 [CustomerID],[PickedByPersonID]
FROM [Sales].[Orders]
WHERE CustomerID = 3
OPTION (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));

By adding the “DISABLE_OPTIMIZER_ROWGOAL” hint to the query, we can disable the Rowgoal Optimizer for that specific query. This can be useful when you observe that the optimizer’s choice of execution plan is not optimal for your query.

It is important to note that the impact of enabling or disabling the Rowgoal Optimizer may vary depending on the query and the data being queried. Therefore, it is recommended to test this feature with your own queries and determine which approach works best for your specific scenario.

Remember, the Rowgoal Optimizer is just one of the many features available in SQL Server to optimize query performance. It is always recommended to analyze and understand the execution plans, statistics, and indexes to make informed decisions when optimizing your queries.

Stay tuned for more articles on SQL Server optimization techniques and best practices!