If you have a database server that looked like it had been suffering from memory contention, you should check what queries were being run that had high memory requirements. Before the SQL Server database engine can execute a submitted query, SQL Server grants the requested memory to the query, such memory is requested for sort operators, hash operators, and parallelism operators in the execution plan. Without the memory grants, a query cannot be started to execute, and the most important point is a query which is waiting in a Resource Semaphore queue can be only executed when ALL lower-cost queues do not contain any other waiting queries. The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016, a common scenario for using this feature is identifying top n queries (by execution time, memory consumption, etc.) in the past x hours. The column max_query_max_used_memory from sys.query_store_runtime_stats DMV is the maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Below script uses the Query Store to find out the top 10 queries with the highest memory grants over the last 24 hours in an user database:
USE [master]
GO
ALTER DATABASE [AppDB] SET QUERY_STORE = ON
GO
ALTER DATABASE [AppDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
GO
USE [AppDB]
GO
DECLARE @hours int = 24;
SELECT TOP 10 DB_NAME() AS DB, S.[name] AS SchemaName, O.[name] AS ObjectName,
SUBSTRING(t.query_sql_text, 1, 1000) AS QueryText,
CAST(MAX(rs.max_query_max_used_memory * 8 / 1024.0) AS decimal(9, 2)) AS MaxMemoryMB
FROM sys.query_store_query AS Q
JOIN sys.query_store_query_text AS T ON Q.query_text_id = T.query_text_id
JOIN sys.query_store_plan AS P ON Q.query_id = P.query_id
JOIN sys.query_store_runtime_stats AS RS ON P.plan_id = RS.plan_id
JOIN sys.query_store_runtime_stats_interval AS RSI ON Rs.runtime_stats_interval_id = RSI.runtime_stats_interval_id
LEFT JOIN sys.objects AS O ON Q.object_id = O.[object_id]
LEFT JOIN sys.schemas AS S ON O.schema_id = S.[schema_id]
WHERE RSI.start_time > DATEADD(hour, -@hours, GETDATE())
AND t.query_sql_text NOT LIKE '%SELECT TOP 10 DB_NAME() AS DB%'
GROUP BY S.[name], O.[name], SUBSTRING(T.query_sql_text, 1, 1000)
ORDER BY MaxMemoryMB DESC;
