SQL SERVER 2022 – Persistence and Percentile Memory Grant Feedback

When running queries in SQL Server, optimizing query performance and memory utilization can be challenging. However, SQL Server 2022 introduces powerful enhancements to memory grant feedback, a feature that dynamically adjusts memory grants for query execution. In this comprehensive blog post, we will delve deeper into memory grant feedback, exploring its key aspects and specifically focusing on the new features introduced in SQL Server 2022.  We will particularly focus on Persistence and Percentile Memory Grant Feedback. I have prepared this note for one of my clients who regularly work with me on Comprehensive Database Performance Health Check and now wants to upgrade to SQL Server 2022.

Understanding Memory Grant Feedback

Memory grant feedback is a feature introduced in SQL Server to optimize memory allocation for query execution. It addresses situations where a query receives either too much or too little memory, leading to suboptimal performance. By dynamically adjusting the memory grant size based on the actual memory requirements, memory grant feedback improves query concurrency and reduces costly disk spills. I have previously blogged about this topic in detail over here: SQL SERVER – Introduction to Memory Grant Feedback.

Batch Mode Memory Grant Feedback

Batch mode memory grant feedback was introduced in SQL Server 2017 (14.x) and Azure SQL Database. It focuses on recalculating the actual memory required for a query and updating the grant value for the cached plan. This feature reduces excessive memory grants that hinder concurrency and fixes underestimated grants that cause disk spills. Enabling batch mode memory grant feedback requires setting the compatibility level to 140 or higher for the database.

Row Mode Memory Grant Feedback

Row mode memory grant feedback, introduced in SQL Server 2019 (15.x) and later versions, extends the functionality of batch mode memory grant feedback. It adjusts memory grant sizes for both batch and row mode operators, further improving memory allocation. To enable row mode memory to grant feedback in Azure SQL Database, the compatibility level must be set to 150 or higher for the database.

Memory Grant Feedback Persistence and Percentile Grant

SQL Server 2022 introduces two significant enhancements to memory grant feedback: memory grant feedback persistence and percentile grant mode. These features work together to optimize memory allocation and improve query performance. Let’s take a closer look at how these features actually work.

It’s important to note that memory grant feedback persistence and percentile grant mode are enabled by default in Azure SQL Database and SQL Server 2022 (16.x). However, to take advantage of these features, you need to ensure that the database is set to compatibility level 140 or higher and that the Query Store is enabled and in a “read write” state.

Memory Grant Feedback Persistence: In previous versions of SQL Server, memory grant feedback adjustments were not persisted beyond plan cache evictions. This meant that if a plan was evicted from the cache, the feedback process had to start again, leading to suboptimal performance for the first few executions of the query after eviction. To address this limitation, SQL Server 2022 introduces memory grant feedback persistence.

With memory grant feedback persistence enabled, the adjustments made to memory grants are stored in the Query Store, along with other query information. This ensures that the memory grant adjustments persist across cache evictions. By retaining this feedback, SQL Server can provide consistent and accurate memory grants even after plan cache evictions, resulting in improved query performance.

Percentile Grant Mode: Another enhancement introduced in SQL Server 2022 is percentile grant mode. This mode complements the existing memory grant feedback functionality and aims to further optimize memory allocation based on historical data. By leveraging historical information from past query executions, SQL Server calculates memory grants based on a high percentile of grant sizing requirements.

With percentile grant mode enabled, SQL Server considers the memory grant needs of queries over time rather than relying solely on the most recent execution. This approach reduces oscillation in memory grant sizes and provides more accurate memory grants, minimizing spills and optimizing memory allocation.

Combining Persistence and Percentile Grant Mode: By combining memory grant feedback persistence and percentile grant mode, SQL Server 2022 offers a robust solution for memory allocation optimization. Memory grant adjustments are now stored persistently in the Query Store, ensuring their availability even after cache evictions. Additionally, memory grants are calculated based on a high percentile of historical grant sizing requirements, resulting in more accurate and efficient memory usage.

Benefits and Considerations: These enhancements in SQL Server 2022 address the limitations of previous memory grant feedback implementations. By persisting memory grant adjustments and incorporating historical data through percentile grant mode, SQL Server can provide better memory allocation and performance optimizations.

Monitoring CE Feedback with T-SQL

To monitor the activity and results of CE feedback, you can use the following T-SQL query:

SELECT
    qsq.query_id,
    qsqt.query_sql_text,
    qsp.query_plan,
    qspf.feature_desc,
    qspf.state_desc,
    qspf.feedback_data,
    qspf.create_time,
    qspf.last_updated_time
FROM sys.query_store_query AS qsq
INNER JOIN sys.query_store_plan AS qsp
    ON qsp.query_id = qsq.query_id
INNER JOIN sys.query_store_query_text AS qsqt
    ON qsqt.query_text_id = qsq.query_text_id
INNER JOIN sys.query_store_plan_feedback AS qspf
    ON qspf.plan_id = qsp.plan_id
WHERE qspf.feature_id = 2;

This query retrieves information from the Query Store tables to display details about queries and plans with memory grant feedback. It includes the query ID, SQL text, query plan, feature description, state description, feedback data, creation time, and last updated time.

Enabling and Disabling Memory Grant Feedback

Memory Grant feedback is, by default, enabled in SQL Server 2022 along with Query Store features. To enable Memory Grant feedback, set the database compatibility level to 160 or higher. The Query Store must be enabled and in READ_WRITE mode for the relevant databases.

To change compatibility at the database level, use the following T-SQL code:

USE [master]
GO
ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160
GO

To enable query store at the database level you can use the following script:

-- Enable Query Store at the database level
USE [master]
GO
ALTER DATABASE [DbName] SET QUERY_STORE = ON
GO
ALTER DATABASE [DbName] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO

To enable Memory Grant feedback at the database level, use the following T-SQL code:

-- Enable Batch Mode Memory Grant Feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;
GO
-- Enable Row Mode Memory Grant Feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
GO
-- Enable Memory Grant Feedback Percentile at the database level
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = ON;
GO
-- Enable Memory Grant Feedback Persistence at the database level
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;
GO

To disable Memory Grant feedback at the database level, use the following T-SQL code:

-- Disable Batch Mode Memory Grant Feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;
GO
-- Disable Row Mode Memory Grant Feedback at the database level
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
GO
-- Disable Memory Grant Feedback Percentile at the database level
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;
GO
-- Disable Memory Grant Feedback Persistence at the database level
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;
GO

To disable Memory Grant feedback at the query level, use the query hint:

-- Disable Batch Mode Memory Grant Feedback at Query Level
SELECT * FROM YourTableName OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
-- Disable Row Mode Memory Grant Feedback at Query Level
SELECT * FROM YourTableName OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Notes to remember:

• There is no hint available to disable Memory Grant Feedback Persistence and Percentile Grant at the query level when I am writing this blog post.
• Memory Grant Feedback Persistence and Percentile Grant will only work if Batch Mode and Row Mode memory grant feedback is enabled.

Conclusion

In conclusion, the introduction of the memory grant feedback persistence and percentile grant mode in SQL Server 2022 brings significant improvements to memory allocation optimization. These features ensure consistent feedback across plan cache evictions, leverage historical data for more accurate memory grant calculations, and ultimately enhance query performance in database environments. You can always reach out to me on YouTube.

Reference: Pinal Dave (http://blog.SQLAuthority.com)