I had a server that looked like it had been suffering from memory contention. I wanted to see what queries were being run that had high memory requirements. The problem was that it wasn’t happening right now – I needed to be able to see what had happened over the last 24 hours.
Enter Query Store. In the run-time stats captured by Query Store are included details relating to memory.
I decided to use the column max_query_max_used_memory from sys.query_store_runtime_stats. In books online this is defined as:
Maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval.
I took my script from the following post https://matthewmcgiffen.com/2017/11/01/capture-the-most-expensive-queries-across-your-sql-server-using-query-store/ and modified it to look just at this metric.
Here’s the script, it collates figures across all databases that have Query Store enabled and returns the top 50 queries with the highest memory grants. This is looking over the last 24 hours, but you can easily modify that to look at details for any interval you are interested in:
--Gather and report on most memory hungry queries DECLARE @Reportinginterval int; DECLARE @Database sysname; DECLARE @StartDateText varchar(30); DECLARE @TotalExecutions decimal(20,3); DECLARE @TotalDuration decimal(20,3); DECLARE @TotalCPU decimal(20,3); DECLARE @TotalLogicalReads decimal(20,3); DECLARE @SQL varchar(MAX); --Set Reporting interval in days SET @Reportinginterval = 1; SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval, GETUTCDATE()) AS varchar(30)); --Cursor to step through the databases DECLARE curDatabases CURSOR FAST_FORWARD FOR SELECT [name] FROM sys.databases WHERE is_query_store_on = 1 AND state_desc = 'ONLINE'; --Temp table to store the results DROP TABLE IF EXISTS #Stats; CREATE TABLE #Stats ( DatabaseName sysname, SchemaName sysname NULL, ObjectName sysname NULL, QueryText varchar(1000), MaxMemoryGrantMB decimal(20,3) ); OPEN curDatabases; FETCH NEXT FROM curDatabases INTO @Database; --Loop through the datbases and gather the stats WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = ' USE [' + @Database + '] INSERT INTO #Stats SELECT DB_NAME(), s.name AS SchemaName, o.name AS ObjectName, SUBSTRING(t.query_sql_text,1,1000) AS QueryText, (MAX(rs.max_query_max_used_memory)/128) AS MaxMemoryMB FROM sys.query_store_query q INNER JOIN sys.query_store_query_text t ON q.query_text_id = t.query_text_id INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id INNER JOIN sys.query_store_runtime_stats_interval rsi ON rs.runtime_stats_interval_id = rsi.runtime_stats_interval_id LEFT JOIN sys.objects o ON q.OBJECT_ID = o.OBJECT_ID LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE rsi.start_time > ''' + @StartDateText + ''' GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000) OPTION(RECOMPILE);' EXEC (@SQL); FETCH NEXT FROM curDatabases INTO @Database; END; CLOSE curDatabases; DEALLOCATE curDatabases; --Report Results SELECT TOP 50 DatabaseName, SchemaName, ObjectName, QueryText, MaxMemoryGrantMB FROM #Stats WHERE QueryText not like 'INSERT INTO #Stats%' --Exclude current query ORDER BY MaxMemoryGrantMB DESC; DROP TABLE #Stats;