Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Aug 7 '12 at 9:56

This question came from our site for professional and enthusiast programmers.

    
Why limit it? Was it using too much memory? If it wasn't "stealing" it from other programs .. :) Although an interesting question; I hope the answers talk about how to profile SQL Server memory usage/access. –  pst Aug 7 '12 at 0:09
    
Consider asking about this at serverfault.com as well –  craig65535 Aug 7 '12 at 0:21
1  
Your "server" has 2 GB of RAM and is running SQL Server and a web server? No offense, but would you like to borrow my phone as an upgrade? –  Aaron Bertrand Aug 7 '12 at 1:35
    
@pst I was experiencing an issue where MSSQL server wasn't releasing memory back to the OS when it needed it. I have edited my post with more information –  Rollcredit Aug 7 '12 at 1:39
1  
This other server had SQL Server and the web sites were using memory for compiling web pages, and you never saw an issue? Was it less busy then? Or was it not compiling pages? I don't think any amount is "adequate" and not knowing your budget, the ability to upgrade the server, or how big your databases will get, I will stop at "more than 2 GB." –  Aaron Bertrand Aug 7 '12 at 1:46

1 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...

share|improve this answer
    
Thanks for the detailed response. I have updated my question with additional information about why I chose to limit the memory of MSSQL. –  Rollcredit Aug 7 '12 at 1:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.