Excessive memory grants are extremely problematic in SQL Server. These excessive grants do not just happen out of the blue. Memory grants are directly linked to the queries. When you write a bad query and you can kill your server. One of the reasons bad queries kill servers is because they try to steal all of the memory. This article will help find these hog queries so they can be tuned.
There is a lot of science and art that is mixed together to help performance tune. Performance tuning is rather enjoyable because the rewards are usually very satisfying. There is nothing like the feeling of making a query go turbo speed.
When I say turbo speed, there is a bit of a tongue in cheek there. However, there is some truth to it. The causes of slow performance vary from query to query but the results of tuning usually all result in significantly faster query performance.
One such example of this happened when I encountered a query with a 118GB memory grant. I had to double check that figure multiple times just to make sure. Clearly there was a complaint that the query was way too slow. Sadly, the client only recognized the pain of the query when trying to migrate to AWS. Prior to the migration, they had oversized bare metal servers with top-shelf hardware that could conceal the worst of performance issues.
Memory – You’ll understand what happiness is
Digging into this particular query, I found a great deal of wrong with how it was written. I won’t digress into all of the bad anti-patterns, suffice it say there were plenty. These anti-patterns helped contribute to the 118GB memory grant. When tuning was sufficient and the query was within acceptable performance timings, the memory grant was
Continue reading on jasonbrimhall.info.
