SQL Query Memory Consumption in SQL Server 2025

Problem

I recently faced a query whose memory consumption was relatively high. I tuned it via indexing and rewriting, but it is interesting to note that SQL Server 2025 executes the same query using less memory compared to SQL Server 2022. In this article, I will execute a query under compatibility level 160 (SQL Server 2022) and then compare its execution plan with when it runs under compatibility level 170 (SQL Server 2025).

Solution

SQL Server performance tuning focuses on reducing query execution time and minimizing resource consumption. As a DBA, you should care about resource consumption because the server resources are limited, and when a query consumes a lot of resources like memory, concurrency is reduced. It means that when the memory grant for a query is high and multiple sessions run it simultaneously, other queries cannot start execution because no memory is available for them, and the resource semaphore wait on the server increases.

SQL Query Memory Consumption in SQL Server 2025

Set Up Test Environment

My materials for this demonstration are:

SQL Server 2025 RC0 (or the preview, both work well)
StackOverflow database
A query to find the top 100 users with the highest reputation, considering only the single top user from each location

SQL Server 2025 RC0 was released recently, and you can download it from the Microsoft website. The StackOverflow database is an open source database from StackOverflow.com.

Compatibility Level 160 Test

First, I switch the database compatibility level to 160, and then execute the following query. To show the actual execution plan of a query, simply press Ctrl + M in SSMS.

Use master
GO
ALTER DATABASE StackOverflow SET COMPATIBILITY_LEVEL = 160;
GO
Use StackOverflow
GO
;With Tbl AS
 (
 Select 
   ROW_NUMBER() Over(Partition By u.Location Order By u.Reputation Desc) As rn,
   u.Id,
   u.Age,
   u.CreationDate,
   u.DisplayName,
   u.DownVotes,
   u.EmailHash,
   u.LastAccessDate,
   u.Location,
   u.Reputation,
   u.UpVotes,
   u.Views,
   u.WebsiteUrl,
   u.AccountId
 From dbo.Users u Where u.Location Is Not Null And Location <> '')
 Select Top 100 
   t.Id,
   t.Age,
   t.CreationDate,
   t.DisplayName,
   t.DownVotes,
   t.EmailHash,
   t.LastAccessDate,
   t.Location,
   t.Reputation,
   t.UpVotes,
   t.Views,
   t.WebsiteUrl,
   t.AccountId
 From Tbl t
 Where t.rn = 1
 Order By t.Reputation Desc
GO

Query Plan Review

The optimizer chooses to scan the entire clustered index, then sorts the records by Reputation in descending order and uses a Window Aggregate operator:

SQL Server used these two operators (Sort and Window Aggregate) just to generate the row number. Pay attention to the following image:

The optimizer receives the records from the Window Aggregate operator, sorts them by Reputation in descending order, and then filters them to find the records with row number of one. Note that SQL Server sorts the records twice because we used the ORDER BY clause two times in the query: once to calculate the Row Number and once more to sort the final result. If you right-click on the SELECT operator in the execution plan and then choose Properties, you can see memory grant info for the query on the right-hand side:

The granted memory is 2704 MB, and “IsMemoryGrantFeedbackAdjusted” equals “No: First Execution.” This means that memory grant feedback doesn’t adjust memory for the first compile and associated execution. If I execute the query more times, say three more, the memory grant reduces from 2768 MB to 946 MB, and “IsMemoryGrantFeedbackAdjusted” changes to “Yes: Stable.”

;With Tbl AS
 (
 Select 
   ROW_NUMBER() Over(Partition By u.Location Order By u.Reputation Desc) As rn,
   u.Id,
   u.Age,
   u.CreationDate,
   u.DisplayName,
   u.DownVotes,
   u.EmailHash,
   u.LastAccessDate,
   u.Location,
   u.Reputation,
   u.UpVotes,
   u.Views,
   u.WebsiteUrl,
   u.AccountId
 From dbo.Users u Where u.Location Is Not Null And Location <> '')
 Select Top 100 
   t.Id,
   t.Age,
   t.CreationDate,
   t.DisplayName,
   t.DownVotes,
   t.EmailHash,
   t.LastAccessDate,
   t.Location,
   t.Reputation,
   t.UpVotes,
   t.Views,
   t.WebsiteUrl,
   t.AccountId
 From Tbl t
 Where t.rn = 1
 Order By t.Reputation Desc
GO 3

This means memory grant feedback has been applied and the granted memory is now stable, meaning that what was granted for the previous execution is the same as what is granted for the current execution.

To learn more about memory grant feedback, refer to this link: Memory grant feedback on the Microsoft website.

Compatibility Level 170 Test

I’m going to change the database compatibility level to 170 and run the test once more.

Alter Database Current Set Compatibility_Level = 170
GO
;With Tbl AS
 (
 Select 
   ROW_NUMBER() Over(Partition By u.Location Order By u.Reputation Desc) As rn,
   u.Id,
   u.Age,
   u.CreationDate,
   u.DisplayName,
   u.DownVotes,
   u.EmailHash,
   u.LastAccessDate,
   u.Location,
   u.Reputation,
   u.UpVotes,
   u.Views,
   u.WebsiteUrl,
   u.AccountId
 From dbo.Users u Where u.Location Is Not Null And Location <> '')
 Select Top 100 
   t.Id,
   t.Age,
   t.CreationDate,
   t.DisplayName,
   t.DownVotes,
   t.EmailHash,
   t.LastAccessDate,
   t.Location,
   t.Reputation,
   t.UpVotes,
   t.Views,
   t.WebsiteUrl,
   t.AccountId
 From Tbl t
 Where t.rn = 1
 Order By t.Reputation Desc
GO

Query Plan Review

As you can see in the image below, the query execution plan has changed:

The optimizer receives the records from the Window Aggregate operator, filters them to find the records with row number of one and then sorts them by Reputation in descending order.

Note: The first part of the execution plan did not change, so I skipped showing its image.

As shown in the image, the memory grant is 1427 MB, and “IsMemoryGrantFeedbackAdjusted” equals “No: First Execution”.

Remember that the granted memory was 2768 MB under compatibility level 160. If I run the query three more times, the memory grant reduces from 1427 MB to 467 MB, and “IsMemoryGrantFeedbackAdjusted” changes to “Yes: Stable.”

;With Tbl AS
 (
 Select 
   ROW_NUMBER() Over(Partition By u.Location Order By u.Reputation Desc) As rn,
   u.Id,
   u.Age,
   u.CreationDate,
   u.DisplayName,
   u.DownVotes,
   u.EmailHash,
   u.LastAccessDate,
   u.Location,
   u.Reputation,
   u.UpVotes,
   u.Views,
   u.WebsiteUrl,
   u.AccountId
 From dbo.Users u Where u.Location Is Not Null And Location <> '')
 Select Top 100 
   t.Id,
   t.Age,
   t.CreationDate,
   t.DisplayName,
   t.DownVotes,
   t.EmailHash,
   t.LastAccessDate,
   t.Location,
   t.Reputation,
   t.UpVotes,
   t.Views,
   t.WebsiteUrl,
   t.AccountId
 From Tbl t
 Where t.rn = 1
 Order By t.Reputation Desc
GO 3

You can view the results:

Under compatibility level 160, after we ran the query four times, the memory grant was 946 MB compared to 467 MB.

Summary

I’m not saying that SQL Server 2025 running compatibility level 170 will always uses less memory for every query. But, in this example, SQL Server 2025 ran the query using about half the memory with compatibility level 170 versus 160.

Also, this article was not about writing the most efficient query, it was more about showing the performance differences. Here is a different version of the query that would perform better:

Create Index IX_Location_Reputation On dbo.Users (Location, Reputation) With (Data_Compression = Page)
GO
Drop Table If Exists #Tbl
GO
Select u.Location, Max(u.Reputation) As Reputation
Into #Tbl From dbo.Users u
Where u.Location Is Not Null And Location <> ''
Group By u.Location
GO
Select Top 100
 u.Id,
 u.Age,
 u.CreationDate,
 u.DisplayName,
 u.DownVotes,
 u.EmailHash,
 u.LastAccessDate,
 u.Location,
 u.Reputation,
 u.UpVotes,
 u.Views,
 u.WebsiteUrl,
 u.AccountId
From #Tbl t Inner Join dbo.Users u
 On t.Location = u.Location And t.Reputation = u.Reputation
Order By u.Reputation Desc

Next Steps

Resources

Community

Subscribe