current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 6 down vote favorite

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.

share|improve this question

migrated from stackoverflow.com May 4 '13 at 18:55

This question came from our site for professional and enthusiast programmers.
1  
A recent article I wrote about memory over-commit on SQL Servers may be useful for you to read. –  Jon Seigel May 5 '13 at 17:24

2 Answers 2

up vote 8 down vote accepted

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

share|improve this answer
    
Thank you Scott –  fixit May 4 '13 at 17:57
3  
An additional consideration is that you should always leave enough headroom for the operating system to do its normal operating system things. Otherwise the OS and SQL Server are going to be fighting over some parts of memory and ultimately SQL Server will lose and get relegated to the page file. –  Aaron Bertrand♦ May 4 '13 at 22:19
    
@AaronBertrand Good point. I rephrased that section. –  Scott Chamberlain May 4 '13 at 23:05
2  
There is nice Report in SSMS about SQL server memory: blogs.msdn.com/b/buckwoody/archive/2007/10/17/… You can track which part eats most memory –  DiGi May 4 '13 at 23:16
up vote 0 down vote

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)

share|improve this answer

Your Answer

 
discard

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