1) Just to be clear, do you mean the hint MAX_GRANT_PERCENT requires Resource Governor? Only, it seems to be working fine without us enabling Resource Governor, insomuchas it's limiting the requested memory as instructed.

2) Yep, agreed, we'll definitely revisit this.

3) That's a good idea! Our 2016 lab is significantly smaller right now; but then, I only really need to see to what extent the filter complexity throws out the cardinality estimations in 11 vs 12/13 compatibility mode. That would be enough to guide us. Better still perhaps, and to your point about trace flag 4199, this trace flag on 2012 is something we can test in a full size environment. We'll look into that.

4) Definitely considering Column Store indexes, but we're waiting until we upgrade from 2012 to 2016 to get some of the latest improvements in Column Store indexes. (Finally i make mention of the fact we're on 2012, SP3!)

5) So, we have 1 TB RAM on the machine (physical server). 840 GB is allocated to SQL Server, of which 640 GB is showing as Maximum Workspace Memory. ** Is memory maxed out, well, in effect; taking into account our comfort levels 🙂

But I'd be grateful to hear your ideas both for the yes and no answers to "Is it maxed out on the hardware?" !!

** We have 4 NUMA modes so the maximum workspace each troublesome query is being allocated is one quarter of that, c. 160 GB. That is until we specify the hint "MAX_GRANT_PERCENT = 10", which results in an allocation of 16 GB.

Which leads me to Bonus Q1: On the subject of confirming that workspace memory requested (and granted) is influenced by MAX_GRANT_PERCENT, one thing I have noticed is that we're getting spills to tempdb when i specify MAX_GRANT_PERCENT = 10, 20 or even 50 per cent. The spill is prevented somewhere between 51 and 75 %. Why I find this so strange is that sys.dm_exec_query_memory_grants is showing max_used_memory as less than 5 GB.

Are there intricacies, or even inadequacies, of sys.dm_exec_query_memory_grants I am overlooking?

Needless to say, this unanticipated spilling may, after all, threaten the very feasibility of our plan to limit workspace memory per query ... along the lines of your original reply 🙂 .

And, if I may, bonus Q2: How can i tell how much data is being spilled? I can see in the Actual Plan that it's spill level 1, and I can see that tempdb allocations, per sp_whoisactive, don't go over 500,000 pages (please note: it's not far off this when there's no spill). But is that a reliable way to understand the scale of the spill? In the plan, I don't see any of the stuff about how many threads spilled or how many pages were spilled. That's weird not to see that, right? The spill in this case is from a Hash Match, not the Sort that i originally suspected was the big workspace memory consumer.

And bonus Q3: By the way, any ideas as to why Microsoft wouldn't expose a "Max memory per query (KB)" configuration setting, bearing in mind they have a "Min memory per query (KB)"? It's academic I know, but curiosity has forced me to ask this question!

Thanks again.