13

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?)

14

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.