[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SBS2008 Memory management with SQL

Posted on 2011-04-27
13
Medium Priority
?
1,424 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:IT
  • 5
  • 3
  • 2
  • +2
13 Comments
 
LVL 47

Expert Comment

by:Donald Stewart
ID: 35479297
0
 

Author Comment

by:IT
ID: 35479313
I know that i *can* limit this, but more importantly is what the limits should reasonably be.
0
 
LVL 47

Accepted Solution

by:
Donald Stewart earned 1000 total points
ID: 35479336
I have limited ours to 256 without any noticeable issues
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.

 
LVL 60

Expert Comment

by:Cliff Galiher
ID: 35480049
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
 
LVL 47

Expert Comment

by:Donald Stewart
ID: 35480399
@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
 

Author Comment

by:IT
ID: 35483303
@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
 

Author Comment

by:IT
ID: 35483321
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35483542
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
 

Author Comment

by:IT
ID: 35484218
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35484583
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
 

Author Closing Comment

by:IT
ID: 35484890
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
 
LVL 60

Expert Comment

by:Cliff Galiher
ID: 35486446
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
 

Expert Comment

by:atysxx
ID: 36103300
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question