SQL Query Memory Consumption in SQL Server 2025

By SQLYard.com | November 2025

Introduction

Memory grants are one of the most critical — and often overlooked — parts of SQL Server performance tuning. When a query requests more memory than it needs, concurrency drops. When it asks for too little, spills to tempdb occur.

With SQL Server 2025, Microsoft introduced improvements to query memory grant feedback and execution plan optimization, allowing the engine to manage memory far more efficiently than in SQL Server 2022. In this article, we’ll explore how these changes impact query performance, show side-by-side comparisons between compatibility levels 160 and 170, and walk through a hands-on workshop to analyze memory behavior yourself.

---

Why Query Memory Consumption Matters

Each query in SQL Server requests a memory grant — a reserved amount of memory used for operations like sorting and hashing. When one query consumes too much, others must wait on a resource semaphore, blocking concurrency .

SQL Server’s memory grant feedback mechanism (introduced in 2017) adjusts grants based on previous executions. SQL Server 2025 improves this feedback loop, making adjustments faster and more accurate.

The result: more stable memory usage and less wasted RAM per query.

---

Test Environment Setup

For this demonstration, we’ll use:

• SQL Server 2025 RC0 or RC1
• StackOverflow sample database (freely available at StackOverflow.com)
• A query that finds the top 100 users with the highest reputation, returning only the top user per location.

---

Step 1 – Test Under Compatibility Level 160 (SQL Server 2022)

First, switch your database to compatibility level 160 and execute the following query.

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.Reputation, t.Location
FROM Tbl t
WHERE t.rn = 1
ORDER BY t.Reputation DESC;
GO

To capture memory details, enable Include Actual Execution Plan in SSMS (Ctrl + M).

You’ll see that the optimizer performs a clustered index scan and uses both a Sort and a Window Aggregate operator. This is necessary to compute ROW_NUMBER() and order results by reputation.

---

Observing Memory Grants

Right-click on the SELECT operator in the execution plan and open Properties.

You’ll notice:

• Granted Memory: ~2700 MB
• IsMemoryGrantFeedbackAdjusted: No (First Execution)

Run the same query three more times:

GO 3

Now the granted memory reduces to roughly 946 MB, and IsMemoryGrantFeedbackAdjusted changes to Yes: Stable.

This confirms that memory grant feedback successfully adjusted the query’s memory footprint after multiple executions .

---

Step 2 – Test Under Compatibility Level 170 (SQL Server 2025)

Let’s now enable SQL Server 2025 features.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;
GO

Re-run the same query:

;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.Reputation, u.Location
FROM dbo.Users u
WHERE u.Location IS NOT NULL AND Location <> ”
)
SELECT TOP 100
t.Id, t.DisplayName, t.Reputation, t.Location
FROM Tbl t
WHERE t.rn = 1
ORDER BY t.Reputation DESC;
GO

Comparing Execution Plans

The query plan now changes slightly. The optimizer filters results before the final sort, reducing intermediate row counts and memory needs.

Memory granted: ~1427 MB
IsMemoryGrantFeedbackAdjusted: No (First Execution)

Run the query three more times:

GO 3

The memory grant stabilizes at roughly 467 MB, half the amount used under SQL Server 2022’s compatibility level.

---

Workshop: Measuring Query Memory Behavior Yourself

Follow these steps to test on your own server.

Step 1 – Enable Query Statistics

SET STATISTICS TIME, IO ON;

Step 2 – Capture Query Metrics

Run your query in both compatibility levels (160 and 170), recording the Granted Memory (KB) from the execution plan each time.

Step 3 – Monitor Resource Semaphores

SELECT * 
FROM sys.dm_exec_query_memory_grants
WHERE grant_time IS NOT NULL
ORDER BY requested_memory_kb DESC;

This DMV helps you identify which queries are consuming the most memory and whether they are queued for a grant

Step 4 – Analyze with Query Store

If Query Store is enabled, you can compare Memory Grant (KB) over time by querying:

SELECT 
    qs.query_id,
    qs.avg_memory_grant_kb,
    qs.last_execution_time
FROM sys.query_store_runtime_stats qs
ORDER BY qs.avg_memory_grant_kb DESC;

Step 5 – Optimize

Try:

• Rewriting queries to reduce multiple ORDER BY or nested sorts.
• Adding appropriate indexes:

CREATE INDEX IX_Location_Reputation 
ON dbo.Users (Location, Reputation) 
WITH (DATA_COMPRESSION = PAGE);

This reduces the need for sorting in memory.

---

Summary

In this test, SQL Server 2025 under compatibility level 170 used about 50% less memory for the same workload compared to 2022’s level 160.

The key takeaways:

• Memory grant feedback has become faster and more accurate.
• The query optimizer applies better row-reduction before sorts.
• Improvements in execution plan heuristics reduce unnecessary grants.

While not every workload will see the same results, the overall trend is clear: SQL Server 2025 delivers more efficient memory consumption per query, improving concurrency and stability across busy systems.

---

Final Thoughts

Memory pressure remains one of the biggest hidden causes of performance degradation. SQL Server 2025 continues Microsoft’s push toward self-tuning behaviors — from intelligent query processing to adaptive feedback loops.

If you’re upgrading from SQL Server 2019 or 2022, enabling compatibility level 170 is a simple way to unlock these benefits immediately.

Always monitor DMVs, Query Store, and execution plans to confirm gains in your own environment, and benchmark before and after the upgrade.

---

References

1. Microsoft Docs – Memory Grant Feedback Overview
2. MSSQLTips – SQL Query Memory Consumption in SQL Server 2025 (Mehdi Ghapanvari, 2025)
3. SQLYard Blog – SQL Server 2025 Intelligent Query Processing Deep Dive
4. Brent Ozar – Troubleshooting Resource Semaphore Waits
5. SQLSkills – Query Memory Management Internals (Paul Randal)