Understanding Query Memory Grants (400)

Speaker: Maciej Pilecki

Many of your queries, especially those involving sorts and hashes, use memory to execute (called workspace memory). The amount of memory needed by the query is estimated by the Query Optimizer during execution plan generation and it’s essentially an informed guess. Shoot too low and you will end up with performance problems due to sorts and hashes spilling to tempdb. Shoot to high and you might exhaust available server memory and hurt your concurrency, with queries waiting to acquire the memory grant.
In this session we will look into the query memory grants: the fundamentals, the resource semaphores governing the grant process, configuration options and diagnostic tools available as well as some troubleshooting techniques. You will learn some “dirty tricks” to force the Query Optimizer to do what you want it to do.