Finding High Memory Grants Queries using Query Store

Users are complaining the application is slow, system support engineer or developer find out the SQL Server response is slow when he tried to run a simple query using SSMS. You find the CPU loading is not high, so you as a DBA you wonder it's memory pressure, and you are right. By checking the Memory Consumption report, you see the Page Life Expectancy is low (just a few seconds PLE). You run the Paul Randal's SQL Server Wait Statistics (or please tell me where it hurts…) wait stats checking script, RESOURCE_SEMAPHORE wait is the top wait, meaning queries were waiting for memory grants so it can begin executing. Memory grants are used for performing query operations like sorts and hashes. You are asked to identity which queries in the SQL Server requested high memory grants. You want to look at historical query data that can survive plan cache removal and server restart. In this case, the Query Store feature and its DMVs are able to help you. Firstly, you need to enable query store for each user-databases (by default it's off).

-- Enable Query Store on each user-database
EXEC sp_MSforeachDB '
USE [?];
IF DB_NAME() NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
    ALTER DATABASE [?] SET QUERY_STORE = ON;
END
'; 

Let the daily workload runs for a while, so query data and wait statistics are populated into the query store on each database. Then you can run the following diagnostic query which check all the query stores on every database.

EXEC sp_MSforeachDB 'USE [?]; SELECT TOP 5 DB_NAME() AS DB, MAX(qtxt.query_sql_text) AS sample_sql_text, MAX(p.query_id) AS query_id
   ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb
   ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb
   ,SUM(count_executions) AS count_query_executions
FROM sys.query_store_runtime_stats rts
JOIN sys.query_store_plan p
  ON p.plan_id = rts.plan_id
JOIN sys.query_store_query q
  ON p.query_id = q.query_id
LEFT OUTER JOIN sys.query_store_query_text qtxt
  ON q.query_text_id = qtxt.query_text_id
GROUP BY q.query_hash
HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB
ORDER BY SUM(avg_query_max_used_memory) DESC
OPTION (MAX_GRANT_PERCENT = 5)';

Below is a sample result:

You can copy the query_id column value from the above result, then open Query Store > Tracked Queries, enter the query id value to see the execution plan, then check any missing index suggestions (better view in XML to show all index suggestions) and warning.

Query Store is a feature that I always suggest that every user-databases must enable it.