SBS2008 Memory management with SQL

It seems that SBS 2008 has an insatiable thirst for system resources, and since many of our deployments are somewhat limited in terms of hardware, we're looking for ways to decrease the physical memory usage of SBS 2008. its not uncommon for many of our sites to see 90-99% memory utilization with 8GB RAM under no significant load. One of the things that has stood out to us are the Share Point and WSUS SQL processes. I've read that these will use the maximum allotment of RAM unless its needed by another process, but when i look at \\.\pipe\mssql$microsoft##ssee\sql\query and check the RAM allocation its listed as
Minimum Server Memory : 1280
Maximum Server Memory: 6400
Are these values reasonable for the built-in OS databases? Considering that if Share Point isnt being used, I'd like to know how low we can set these values and maintain a stable environment.

I've looked fairly thoroughly and havent been able to find any authoritative guide to Tuning the Memory usage on SBS 2008, and what little i have seen has been very vague or suggesting that i run 32GB of RAM, so any advice on this would be helpful.
ITAsked:
Who is Participating?
 
DonConnect With a Mentor Network AdministratorCommented:
I have limited ours to 256 without any noticeable issues
0
 
DonNetwork AdministratorCommented:
0
 
ITAuthor Commented:
I know that i *can* limit this, but more importantly is what the limits should reasonably be.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
Cliff GaliherCommented:
RAM that isn't being used is RAM that is going to waste. This is true for desktops and servers alike. RAM is faster than disk access, so you *want* to see high memory usage. More specifically, however, SQL and Exchange are particularly transaction oriented, so they have been designed to grab memory to improve performance. While you *can* limit memory, this actually decreases performance, not increases it, so in most cases it is best to leave the defaults as is.

-Cliff
0
 
DonNetwork AdministratorCommented:
@redeyeinc

limiting my WSUS SQL memory allowed our other applications that also use SQL to perform much better.

So there are definitely logical useful reasons to throttle SQL instances from hogging memory.
0
 
ITAuthor Commented:
@Cliff

i realise and appreciate that, however if all the RAM is being used this does not allow for any volatility in the workload without lots of paging, and since we're not using Share Point, and our updates are being deployed manually there is no sound reasoning for allowing the SQL db to use as much RAM as is currently allocated for it.
0
 
ITAuthor Commented:
@dstewartjr

I'm waiting to touch base with the Microsoft team before making the change, but i'll let you know how it goes.
0
 
Anthony PerkinsCommented:
The only reason you need to limit the memory used by SQL Server, is if you are sharing the server with other applications (this is not recommended).  Otherwise SQL Server operates best with as much memory as you can afford.
0
 
ITAuthor Commented:
@acperkins

SBS2008 includes SQL for managing its own OS db's. In this case its not optional to run these on a different server.
0
 
Anthony PerkinsCommented:
If you cannot run SQL Server standalone, then you have no choice but to cripple it, by setting the max memory used by it.
0
 
ITAuthor Commented:
The answer didnt really give any insight as to the reasoning behind it, and it was merely anecdotal, however it appears to be an accurate solution.
0
 
Cliff GaliherCommented:
To be very clear here, SQL and Exchange will both give up memory if they detect other applications requesting it, so the "using all RAM does not provide volatility and causes paging" is actually not accurate. If you have a quick spike, there will be *some* paging, but not much, and if an app is requesting memory long enough for SQL to detect it, SQL will give up what it is using. Again, this is some automatic performance tuning, and it actually works very well.

As far as not running sharepoint and doing manual updates, you still missed the third thing that SQL is used or on SBS. The daily monitoring reports all use data that is regularly gathered, stored in SQL, and then reported on. this is so baked into SBS that it isn't really guttable like WSUS or SharePoint. Letting SQL run as intended adds a fairly significant performance increase to both requested reports and the automated report generation, as well as better performance all around while the server collects and writes that data for later performing. By limiting SQL, this data is still being collected, but actually causes more paging which has a negative impact on your I/O across the board.

-Cliff
0
 
atysxxCommented:
SQL wil release memory... at least that is suppost to happen but some cases to late....whic can lead to out of memory...


When u have more instances it wil be a problem because they al want memory.


its better to set some limits...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.