MicrosoftMy favorite thing to use Resource Governor for is to cap per-query memory grants, but...
It doesn't seem like there's a way to reduce the global limit on how much total memory requests can use as a whole (~75% of max server memory). MIN and MAX_MEMORY_PERCENT don't seem to quite get there.
On SQL Servers with large amounts of memory (which I realize isn't a problem in Azure PaaS 😃), 75% could represent quite a large chunk of buffer pool stolen away.
There are times where I'd rather hit RESOURCE_SEMAPHORE earlier and have queries wait, to not have to rebuild useful buffer pool data from dreaded disk repeatedly. Waiting on PAGEIOLATCH is far more frustrating, especially when these queries are hitting a similar set of tables/indexes.
MicrosoftSo you want a TOTAL_MAX_MEMORY_GRANT_PERCENT for each workload group? Or a single server-level config for max grant memory?
Have you seen cases where limiting individual query grants, either with hints or with RG, would still consume too much total grant memory?
I suppose it's tempting to make it a server-level setting alongside min/max server memory, but I'm not sure if I'd want this one to be so accessible. This could be a pretty powerful footgun. It seems like a more specialized configuration change that I'd expect someone to make alongside something like MAX_MEMORY_GRANT_PERCENT.
But yes, if someone is hitting RESOURCE_SEMAPHORE, and you drop MAX_MEMORY_GRANT_PERCENT down to 10%, you will generally increase concurrency and allow more queries to run, ask for smaller grants, but still end up hitting the ~75% total.
Instead of three queries running and getting ~25% a piece, you end up with 8-10 queries running simultaneously with the lower grants. RESOURCE_SEMAPHORE is gone, but so is most of your buffer pool (assuming the grants are fully utilized).
MicrosoftSo in the specialized scenarios you are describing, are you ok with queries waiting for memory for longer or even timing out (either with memory grant timeout or client timeout) if that preserves data in BP?
You know what I am going to ask for next :). Can you file this on aka.ms/sqlfeedback to get some votes/comments?
