Re: how to determine if memory is slowing the server down

Tech-Archive recommends: Speed Up your PC by fixing your registry

On Mon, 17 Mar 2008 16:24:00 -0700, lucienne
<lucienne@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


I would like to apply the SQL teaming but ama little unsecure about it.
Can it just be done without taking to much risks for the databases?

The taming, limiting SQL Server's memory allocation. Never did that
before I started implementing and supporting SBS. If it's good idea,
yes I think so. The largest memory hogs I've seen so far, are the ISA
MSDE and the embedded 2005 database that WSUS 3.0 uses.

Others may correct me: There should be no risks for the databases, but
services may fail or become unreliable if you tame the instances too
aggressively.

I think there are at least two good reasons for limiting the buffer
pool allocation:

(1) SQL Server has a tendency to allocate a lot more memory for its
buffer pool than needed. On the other hand, SQL Server is also good at
releasing parts of this memory to the OS or other apps when needed.
However, this might not happen fast enough. Books online state:

If an instance of SQL Server is running on a computer where other
applications are frequently stopped or started, the allocation and
deallocation of memory by the instance of SQL Server may slow the
startup times of other applications. Also, if SQL Server is one of
several server applications running on a single computer, the system
administrators may need to control the amount of memory allocated to
SQL Server.
http://msdn2.microsoft.com/en-us/library/ms180797.aspx

This should apply to SBS as well.

(2) You won't get sensible memory alerts without this tweaking.

A good starting point could be to be careful. For instance I've
advices on reducing the MSFW instance to 100 MB. MS has a KB where
they use 512 MB as an example. (With ISA you should consider logging
files instead, but that's another topic).

jas
.

Relevant Pages

  • Re: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 C
    ... I assume that you are having SQL Server 2005 STANDARD EDITION. ... If thats correct then you will not see the pre allocated-AWE allocation. ... Please set your min memory allocation to to same as max memory allocation ... Manager as paging file use. ...
    (microsoft.public.sqlserver.clustering)
  • Re: 300MB DB uses 1.4GB RAM!?
    ... DBCC MEMORYSTATUS will tell you how SQL Server memory manager has ... So, if every page was in memory, there's 300mb. ... the more ram SQL takes the better!! ... allocation for SQL Server. ...
    (microsoft.public.sqlserver.clustering)
  • RE: Memory issues with 64-bit SQL Server 2005 on 64-bit Win 2003 Clust
    ... I'd focus on what is causing SQL Server page. ... running something else on the server that is eating up a lot of memory. ... Each node has 4 dual cores, 8GB RAM. ... see the total memory allocation for all instances is 7380 MB which leaves 812 ...
    (microsoft.public.sqlserver.clustering)
  • Re: Memory management
    ... Since SQL Server wants to keep the Buffer Cache in memory, ... > OS to using 1 gig of memory? ... >> memory allocation split. ...
    (microsoft.public.sqlserver.clustering)
  • Re: SQL-Server 2005, edition specifications
    ... I'd just like to point out that SQL Server 2000 Standard Edition is not ... limited to 2GB databases. ... Perhaps you meant how much memory SQL Server could ...
    (microsoft.public.sqlserver.server)