Determining SQL Server memory requirements

Question

I just a bit of an issue there where a SQL Server database was eating up quite a lot of RAM for its size.

A bit of background:

The database is for a small content managed website. The CMS is custom built in ASP which a bit of unoptimised queries littered throughout, but nothing too bad. The actual database takes up 132MB on disk.

Now back to SQL Server.

When looking through the server options I came across the Memory section. I noticed it was set to 2147483647MB which got me thinking that setting this value to something smaller could be a way to deal with the issue.

I set it to 500MB, which is quite a big difference.

So far the website is running great and there has been no more issues with memory on the server.

My question:

How can I determine what is the best value to set this to? Is 500MB to low? There is going to be another database on this server. It will be much smaller though, about 50MB.

EDIT (More Information)

I should add that the issue that caused me to seek out this memory setting was that of SQL Server not releasing memory back to the OS when it needed it. I run an old site that requires pages to be compiled on the fly (i.e. doesn't have a bin folder) and there was an issue with some pages not being compiled when needed. There was a server out of memory error.

This particular server has 2GB of RAM.

Answer 1

I'm not sure I believe there was ever an actual issue. Just because max server memory was set to 2 petabytes doesn't mean it would ever use that much. This is the functional equivalent of setting it to infinity, and just means you trust SQL Server to use as much memory as it needs.

Setting it to 500 MB seems artificially low to me. While your database is currently 128 MB on disk, you are probably not running any query patterns that lead to any performance issues even with that limit on memory. But when SQL Server needs more than 500 MB of memory, you're going to start noticing. SQL Server is designed to behave as if it is the only server on the machine, so it will take memory as it needs it, and won't release it unless the operating system needs it and applies pressure to SQL Server (discussions of LPIM aside). Restricting SQL Server's memory this way (or taking it away) will just cause performance degradation as it is much more expensive to page data in and out of memory, onto disk, etc. which will most certainly be required as your database grows. Think about trying to put a pig into a tube sock - eventually you're going to need a bigger sock. Why wait until the current sock rips?

I suggest a higher limit, but I can't tell you what that would be. You didn't disclose how much memory is on the server, whether it is x86 or x64, or what else is going to be running there. If the server is dedicated to SQL Server, I see absolutely no reason to save memory - what are you saving it for?

For a bit of an anecdotal observation, I don't think I've ever seen any instance of SQL Server on any kind of physical or virtual machine limited to 500 MB, and I have very serious doubts that this could possibly be the optimal setting for you in the long run.

I'd also suggest doing research into SQL Server settings and then changing them, instead of the other way around. This situation sounds a lot like saying "What does this button do?" after pressing it...