What’s The Point?
Whoever called memory a “bank” was a smart cookie. Everything you get from RAM is a loan.
In SQL Server, queries can get memory loaned to them while they execute. The most common reasons for memory grants are Sorts and Hashes. You may also see them for an Optimized Nested Loops Join, but whatever.
Memory is such an important aspect of query and overall server performance that it really helps to understand when there’s pressure on it, and where it’s coming from.
It’s sort of funny, you read any article about PAGEIOLATCH waits, and people are sitting there telling you that you have a problem with your disk subsystem and whatnot and to investigate that. Buy SSDs.
They never tell you to add memory to be less reliant on disk. I do, but that’s because I love you and want you to be happy and smart.
But this ain’t about that, it’s about this. And this is query memory grants.
How Much?
If you’re on Standard Edition, or using the default Resource Governor settings on Enterprise Edition, any query can come along and suck up up to ~25% of your server’s max server memory setting.
Ain’t that just crackers?
Would you also believe that SQL Server will give out 75% of that setting to queries, and there’s no way to control that? At least not without a bunch of complicated Resource Governor
All that can add up to some severe contention issues. Especially because SQL Server tends to way overestimate memory grants. String columns are lit’rally the devil.
But SQL Server isn’t totally out to get you, either. Some operators can share memory, and parallelism splits up memory grants across threads.
At least until SQL Server 2019, where Batch Mode On Row Store made everything… well, more crackers.
If you’ve been using columnstore for a while, you probably already know these pains.
Fixing Them?
If you need to “fix” a specific query quickly, you can use the MIN and MAX grant percent hints. That allows you to set high and low boundaries for what a single memory can be granted for memory.
Remember that indexes put data in order, and having ordered data can increase the chances of you getting order-friendly algorithms, and decrease your need to ask for memory to sort data in.
Consider joins and aggregates:
- Hash Joins are typically chosen for large, unordered sets
- Merge Joins require ordered sets
- Hash Aggregates are typically chosen for large, unordered sets
- Stream Aggregates require ordered sets
If you index columns appropriately, you make the choice for using ordered algorithms more likely. Without an index putting that data in order, the optimizer would have to choose to add a Sort to the query plan for them to occur.
That can sometimes be costed out of the realm of existence, and that’s fine. Sorts can be pretty rough.
If you need some help figuring that stuff out, check out these posts:
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.