Rebecca Lewis explains an issue with memory grant feedback in SQL Server:
Before SQL Server runs a query, it estimates how much memory it needs for sorting and joining. But what if it gets it wrong?
- Too little memory → Spills to tempdb (slow)
- Too much memory → Starves other queries
SQL Server 2017+ tries to fix bad estimates based on previous calls with ‘Memory Grant Feedback’. Kinda like: ‘Last time I gave you 2GB but you only used 50MB. Next time I’m giving you less.’
This is great in theory, though Rebecca shows a case in which the end result might not be great.