Query Store Improvements I’d Like To See For SQL Server: Memory Grant Columns

Posted on by Erik Darling

Spirited Away

This week’s posts are a series of short, and, admittedly lazy ones about improvements I’d like to see in Query Store.

I’m away from home for the Holiday Week, and I don’t have a lot of time to write at the moment. Normally I’d put these all into one post. I do hope you, dear reader, will forgive me. In the spirit of the holidays, or something.

Problem Two

When you run a query, you might get a memory grant, for example to hash or sort data. In the query plan, there are a whole bunch of columns that give you information about the memory grant:

  • SerialRequiredMemory         
  • SerialDesiredMemory          
  • RequiredMemory               
  • DesiredMemory                
  • RequestedMemory              
  • GrantWaitTime                
  • GrantedMemory                
  • MaxUsedMemory                
  • MaxQueryMemory               
  • LastRequestedMemory          
  • IsMemoryGrantFeedbackAdjusted

And if you read the query plan documentation, you’ll get some explanation of them, too.

Provide memory grant estimate as well as actual runtime memory grant information. Serial required/desired memory attributes are estimated during query compile time for serial execution. The rest of attributes provide estimates and counters for query execution time considering actual degree of parallelism. SerialRequiredMemory: Required memory in KB if the query runs in serial mode. The query will not start without this memory. SerialDesiredMemory: Memory estimated to fit intermediate results in KB if the query runs in serial mode. RequiredMemory: Required memory in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialRequiredMemory. DesiredMemory: Memory estimated to fit intermediate results in KB for the chosen degree of parallelism. If the query runs in serial mode, this is the same as SerialDesiredMemory. RequestedMemory: Memory in KB which the query requests the memory manager to grant. This can be smaller than sum of RequiredMemory and DesiredMemory if it exceeds the maximum allowed for single query. GrantWaitTime: Time in seconds if the query has to wait for successful memory grant. MaxUsedMemory: Maximum memory in KB used by the query. MaxQueryMemory: Maximum memory in KB allowed for single query. LastRequestedMemory: Memory in KB which was requested by the query from the memory manager during the last execution. IsMemoryGrantFeedbackAdjusted: Information regarding if the memory grant in this plan is adjusted based on memory grant feedback.

In Query Store, you get limited memory grant information in query_store_runtime_stats. But if you look in dm_exec_query_stats, there’s a whole lot more.

SQL Server Query Store
grim

It’s a much more complete view of the memory grant, and it’s a damn shame that you have to rely on the plan cache for this, because it’s so unreliable. Clearing out, dropping plans, recompiles, etc. etc. etc.

It would be marvelous if Query Store retained information like the size of the grant, so you can figure out if the amount of memory that it used was anywhere near reality. The ideal columns are not very useful to me, but the total and used are wonderful to compare.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts