I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.

As is noted elsewhere, SQL Server regularly takes up to 98% of physical memory, which appears to slow down the web application running on the server.

In Server Properties in SSMS, under Memory, Maximum Server Memory (in Mb) is set to: 2147483647 Sql Server

My question is, what would be the recommended number to put in the maximum server memory box, given the amount of memory I have available, and that the same server is also running the web application?

Additionally, is it safe to make a change to this number, while SQL Server is running?

Thank you for your advice.

  • 2
    A recent article I wrote about memory over-commit on SQL Servers may be useful for you to read.
    – Jon Seigel
    Commented May 5, 2013 at 17:24

5 Answers 5


I will answer the last question first: Yes, you can change it while the server is running without issue. If you want to change the value via SQL you can do it with the following query

sp_configure 'show advanced options', 1;
sp_configure 'max server memory', 4096;

See this page for more details about setting memory on SQL server.

Your first question, unfortunately the answer is: I can't tell you, I'm not there.

There is a 1,000,000 things you need to factor in when allocating memory. How big are result sets from the queries, how often are they run, would a query that used to take 20 ms be ok to now take 200 ms?

Sql's defaults assumes that it is the only thing running on the server, so it just sets the memory to MAX_VALUE and it stops growing when all available memory is in use (and on dedicated hardware that is fairly close to what you want to happen(see Aarons comment for a possible caveat)). Normally any web server or other software interacting with the database would be on different hardware communicating to it over the network.

You really just need to just set it to a value you think is sane, and if your webserver is still memory choked lower it. If SQL is not giving you enough performance after you give the webserver the memory it needs you will need to either buy more ram or move the SQL to dedicated hardware.


You should never allow sql server to take all of the memory on the box.. It does depend on what is running on that box and how much memory the box has, but I always ensure that I leave 10% of the memory for the O\S.


At my current company where i am hired i always use the minimum and maximum settings for SQL Server. The maximum, if the server is only being used for SQL Server i always set on MAX memory - 1gb for OS. The minimum in our case at 2GB's.

But that is proven for our installations. Not every installation and usage are the same, and i still feel that memory is always not doing what you want ;)

Might be a known thing in your case, but also look at the swapping of memory. When you only have 2GB on the server and you have the OS and SQL Server running on a "high load", the memory consumption will get too high where the OS will tend to do some swapping. Make sure that your paging file(s) are not on the OS drive, or else you might end up with a totally jammed system (been there before and didn't liked it)


I just changed "max server memory" on live server without issues. Seems like it is set as 2147483647 by default on all databases (but I don't know how to actually read it's value from SQL before change)...


Stick with the defaults in 99% of cases. This will allow dynamic management of memory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.