![\"PlanWithSpill\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2018\/03\/PlanWithSpill.png\" "\\"PlanWithSpill\\"")
<\/a><\/p>\nWhen spill happen, the intermediate resultsets (or parts of them) do get written to TempDB. And read back. And potentially written and read back again, and maybe a few more times. This can be horribly slow.<\/p>\n Of course, there\u2019s a chance that the estimate will be wrong in the other direction. Too large. It\u2019s not as obviously bad, but it can limit the throughput of the system. Instead of the query running really slowly, it may have to wait before it runs at all, waiting for the memory to be granted. (RESOURCE_SEMAPHORE).<\/p>\n These were really hard problems to fix. There isn\u2019t a query hint to request more or less memory than the estimates would allocate (though you can specify, as a percentage of the resource pool, the max and min memory to be allocated), so fixes had to be creative, typically tricking the optimiser into thinking there were more or fewer rows than there really were, or that the rows were wider (there are some lovely tricks that can be done with CROSS APPLY for example)<\/p>\n Adaptive memory grants don\u2019t do anything to correct the optimiser\u2019s mis-estimates. What they do, is allow the query processor to learn from the mistakes. If a query\u2019s memory grant is significantly over or under what is needed, then a note is made of that, somewhere in memory, and the next time the query runs, the memory grant is adjusted to a value based on what the previous execution needed.<\/p>\n So, if we run the example from above a second time, making absolutely no changes in the process, the spills are gone.<\/p>\n This is great, unless you have a particular pattern in your workload, where one query will sometimes have a small number of rows flowing through it, and sometimes a large number. This is not a problem specific to Memory Grant Feedback. It\u2019s been around for a long time, we call it bad parameter sniffing in many cases.<\/p>\n So let\u2019s try a test of running the same query multiple times, alternating between parameter values that return small row counts and parameter values that return large row counts. The plan is the same in all cases, it\u2019s a reused cached plan, and it\u2019s one that\u2019s not bad for the larger row counts (hash join, hash aggregate), so we don\u2019t have the typical bad parameter sniffing problem, but the memory grant will oscillate, being based upon the previous query\u2019s execution. I\u2019m going to execute the stored procedure 200 times.<\/p>\n And I should mention that this is an extreme case. I specifically constructed a scenario where the memory grant required by one execution would be completely inappropriate for the next one. This is not (I hope) something that would happen in the real world.<\/p>\n I monitored what was happening with Extended Events, with the memory_grant_updated_by_feedback and memory_grant_feedback_loop_disabled events.<\/p>\n The results were kinda as expected.<\/p>\n And then something interesting happened. I didn\u2019t clear the cache or anything, this was as the procedures executed in a loop.<\/p>\n After 8 executions, each with a memory grant update, both the execution count and the count of updates to the grant reset to 1.<\/p>\n This happened again 8 executions later<\/p>\n And again 8 executions later<\/p>\n Then, finally, after 32 executions, the update is disabled.<\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n