SQL SERVER – Disable Rowgoal Optimizer

3 years ago

Today I am going to discuss a very interesting case which I had observed with my client while working on Comprehensive Database Performance Health Check. First of all, we should remember that scenario that I am going to discuss is not going that frequent and does not happen that often. In my career, this is I think 2 or 3 rd time I have seen this one, so do not consider this as a generic solution. Let us see how Disable Rowgoal Optimizer works.

Brief History – Disable Rowgoal Optimizer

Recently one of my clients was facing an interesting issue after upgrading to SQL Server 2019. They were overall getting amazing performance for their system after upgrading to SQL Server 2019, however, one of their queries was running slow. After carefully observing and doing a few attempts to improve the query, I found that whenever I disable row optimizer the query was running faster and decided for that query to disable the new feature of row optimizer.

While I am not able to reproduce the same query here due to NDA as well as other issues. Let us understand how we can disable this feature for a single query. You can read more about this topic here: SQL SERVER – Row Goal and Performance.

Here is a query with a row optimizer hint not specified with a compatibility level of SQL Server 2019.

SELECT TOP 100 [CustomerID],[PickedByPersonID]
FROM [Sales].[Orders]
WHERE CustomerID = 3

Here is the execution plan of the query.

Here is another query with a hint of the row optimizer disabled with a compatibility level of SQL Server 2019.

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

Here is the execution plan of the query.

You can see in the second case when the row goal optimizer is disabled.

In the case explained in the example, it is not possible to say if enabling or disabling the hint is helpful or not and that is the goal of the post. You need to try this out with your query and decide which works the best.

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