Maximum memory setting in SQL Server 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


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.
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;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO


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.