Guessotron
Most of these signs have to do with wait stats. One could venture out and say that if you have way less memory than data, you need more memory, but… If the server is sitting around bored, who cares?
If we’re going to spend money on memory, let’s make sure it’ll get used. When I’m talking to people with performance problems that memory would solve, here are some of the top reasons.
You’re In The Cloud Where Storage Sucks
Okay, okay, storage can totally suck other places, too. I’ve seen some JBOD setups that would make you cry, and some of them weren’t in the cloud. Where you need to differentiate a little bit here is that memory isn’t going to help slow writes directly. If you add a bunch more memory and free up some network bandwidth for writes by focusing the reads more from the buffer pool, it might.
Look, just avoid disk as much as possible and you’ll be happy.
You’re Using Column Store And/Or Batch Mode
Good column store compression can often rely on adequate memory, but you also need to account for the much larger memory grants that batch mode queries ask for. As more and more workloads move towards SQL Server 2019 and beyond, query memory needs are going to go up because Batch Mode On Row Store will become more common.
You’re Waiting On RESOURCE_SEMAPHORE A Lot
This wait shows up when a bunch of queries are contending for memory grants, but SQL Server has given out all it can. If you run into these a lot, it’s a pretty good sign you need more memory. Especially if you’ve already tuned queries and indexes a bunch, or you’re dealing with a vendor app where they refuse to fix anything.
Other things that might help? The MAX_GRANT_PERCENT hint or Resource Governor
You’re Waiting On RESOURCE_SEMAPHORE_QUERY_COMPILE A Lot
This is another “queue” wait, but it’s for query compilation rather than query execution. Having more memory can certainly help this quite a bit, but so can simplifying queries so that the amount of memory SQL Server has to throw at compiling them chills out a little. You can start by reconsidering those views nested 10 levels deep and the schema design that leads you to needing a 23 table join to construct one row.
You’re Waiting On PAGEIOLATCH_SH Or PAGEIOLATCH_EX A Lot
These waits show up when data pages your query needs aren’t already there. The more you see these, the more latency you’re adding to your workload by constantly shuffling out to disk to get them. Of course, there’s other stuff you can do, like clean up unused and overlapping indexes, compress your indexes, etc. But not everyone is comfortable with or able to do that.
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.
I found this to be very helpful information. You explained the Wait Stats in a way that someone with only a passing knowledge of SQL Server can understand. Good job.
Thanks, Don! Glad to know you’re still out there reading!
What about “AppDomain (blah blah) is marked for unload due to memory pressure?”
“There is insufficient system memory in resource pool ‘internal’ to run this query.”
“Windows successfully diagnosed a low virtual memory condition.” (Microsoft Windows Resource Exhaustion Detector)
That last one gets logged in the System event log.
That’s an interesting one. I’m not sure what the memory rules are for when that happens, though. Do you know?
Thanks!
That last one, you mean? Resource Exhaustion Detector? I’ve only seen that happen to SQL Server when there’s something else competing with it for RAM that shouldn’t be. Like a Powershell cmdlet with a memory leak.
It just means all your memory is used up. “When the commit charge is approaching the upper limit of virtual memory.”
Sometimes SQL Server will shut itself down when that happens. Really dire from a DBA perspective.