If you’re increasing your server’s memory, simultaneously adding more max memory for SQL always seems like a good thing. Let’s talk about what changes when you change the setting for max memory. For the basics, start here.
In SQL Server, increasing max memory will give more memory for data stored in buffer and all sorts of other good things. There is one bad side: your queries that request memory grants may change how much memory they request.
Hopefully your servers have more memory than my blog/presentation laptop. This demo scales, so don’t worry about the small size in my test.
Starting position: max memory at 3 GB
We need a query that wants a memory grant, so I’ll use a query with a sort that returns a lot of rows.
SELECT Tags, Title
FROM Posts
WHERE PostTypeId = 1
ORDER BY Title
Here’s the memory grant from the actual execution plan’s properties. The query wants 615,264 KB of memory, but it was only granted 548,256.
More power! What happens with increased SQL Server max memory?
Same query but with more max memory. In this case, the execution plan stayed the same but the memory grant increased.
This time, all the desired memory was granted. With the lower max memory setting, the query requested 548,256 and now it requests 615,264.
When changing max memory, test
I wanted to write this post to prove that changing SQL Server max memory has an impact on your query’s performance. For this query, a higher memory grant could be a good thing.
If you’re changing SQL Server settings, testing the impact on a test system is recommended.