Find queries with high memory grants – using Query Store

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.

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: