how to simulate a low-memory state when running a query

I am trying to better understand how SQL Server's execution engine behaves in a low-memory state. Speaking in terms of memory grants, I am wondering if there is some way to force GrantedMemory to be equal to RequiredMemory. (My guess is that there's an undocumented trace flag which will do that. Does anyone know what it is?)

If you're on SQL Server 2012 SP3 or better, you can use the MAX_GRANT_PERCENT hint to cap a single query's memory grant. However, you'd have to mess with the number to figure out which percent gives you the correct value (to force GrantedMemory to be equal to RequiredMemory).

See here for more details.

Prior to that, you'd have to use Resource Governor (Enterprise only though).

StackExchange Database Administrators Q#177601, answer score: 14