Return
-
Title
SQL PI query causes very high memory grants in SQL Server 2022 -
Description
The "Get Active Requests" query in SQL PI is assigned very high memory grants by SQL Server 2022. This can cause other queries to wait on the RESOURCE_SEMAPHORE wait type. The user is mitigate the issue by recompiling the query plan and killing the SQL PI spid. What is the cause?
-
Cause
The may be is related to new SQL Server 2022 feature which is enabled by default - "Memory Grant Feedback Persistence" which is used to track memory consumed by a query and tell to optimizer how much memory grant to query. -
Resolution
Quest R&D does not recommending to turn the feature off, but it could be that this feature causes anomalies in granting memory. https://www.sqlservercentral.com/articles/memory-grant-feedback-persistence-in-sql-server-2022
The following query can be used to determine if MGF is enabled:
SELECT value from sys.database_scoped_configurations where name = 'MEMORY_GRANT_FEEDBACK_PERSISTENCE'