Re: SQL Server 2000 and Memory

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 02/25/04 

Date: Wed, 25 Feb 2004 13:10:46 -0500

There is a long and involved debate over what should be 'install' and what
should be 'configure'. IMHO, there should be an external configuration
pack that sets everything appropriately. Maybe four or so predefined ones
for common setup configurations (low-end shared, dedicated small, dedicated
med, dedicated large) with a user-definable one so people can slipstream the
configuration changes into the install.

Until that happens, you will need to write a configuration script that
forces some settings onto SQL, especially max server memory, and remmeber to
run it right after an install. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Richard Speiss" <rspeiss@mtxinc.com> wrote in message
news:uEc2mY8%23DHA.1732@TK2MSFTNGP12.phx.gbl...
> Hi Geoff,
>
> I agree in bigger installation that you would expect SQL Server to be on a
> dedicated computer (my preference as well) but this is a rare occurance
with
> the size of our clients.  Even if they have a "dedicated" box it is
usually
> dedicated to doing other things as well (like MS Exchange).  In some cases
> they need the full SQL Server due to the database size but the daily
volume
> of transactions is not high enough to justify having a separate box that
> does that and only that.
>
> I believe this same behaviour is found in MSDE which according to the MS
> website is meant for embedded database type applications.  So if a client
is
> running MSDE the odds are very high that they are running other
applications
> with it on the same computer. These are retail stores and it is mostly
sales
> that are being recorded so I don't consider the database as being hit hard
> at all.  RAM usage just slowly grows until everything starts swapping out
to
> the disk
>
> I am just wondering if claiming all RAM should be the default behaviour or
> perhaps there should be a question during installation on how it should be
> configured (e.g. All RAM, 70% of RAM, Fixed Amount of RAM, etc)
>
> Thanks for your thoughts
>
> Richard Speiss
>
>
>
> "Geoff N. Hiten" <SRDBA@Careerbuilder.com> wrote in message
> news:u18ZGf0%23DHA.2520@TK2MSFTNGP11.phx.gbl...
> > The behavior you are experiencing is by design.  Most SQL servers in
> > production use are dedicated boxes.  Given the licensing costs,
especially
> > for Enterprise Edition, it is simply not cost effective to do otherwise.
> On
> > a dedicated box, the memory use of the rest of the system is pretty much
> > constant so giving back memory is not a big issue.  As for your
situation,
> > it is the paging that will trigger the memory shrink.  That only happens
> if
> > SQL thinks it can deal with the reduced memory footprint.  If you are
> > hitting SQL hard and using an Office app, you seriously need to think
> about
> > a dedicated server.
> >
> > -- 
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "Richard Speiss" <rspeiss@mtxinc.com> wrote in message
> > news:uXY$w0y%23DHA.1548@TK2MSFTNGP12.phx.gbl...
> > > I know that SQL Server takes almost all available RAM by default.
> > > The docs also state that it is supposed to give it back when the OS
> > requests
> > > it.
> > >
> > > On a typical session that has been running for a few days on a
computer
> > with
> > > 512MB of RAM and Windows 98 (MSDE) or 2000 or XP, when I look at Task
> > > Manager I can see that the computer has allocated 512+ of RAM and is
> > paging
> > > out to the disk (e.g. 700-800MB).  This slows things down incredibly.
> > > Usually on my clients computers they will have a few open applications
> > like
> > > Word, Excel, etc.  Closing these down helps gets the RAM usage closer
to
> > the
> > > physical RAM available.
> > >
> > > This results in my changing the configuration of SQL Server to use a
> > maximum
> > > amount of RAM so as not to impact any other applications that are
> running.
> > > This isn't the most effecient as I am now artifically limiting the
space
> > > available for SQL Server.
> > >
> > > I would prefer to leave it dymanic but my experience has been that SQL
> > > Server almost never gives back the RAM and the OS goes onlong its
merry
> > way
> > > paging future memory requests out to disk.
> > >
> > > Based on the messages I see in this forum it doesn't look like I am
the
> > only
> > > one having this problem.  The usual solution is to configure the
server
> to
> > > not dynamically allocate the RAM.  It sounds to me like dynamically
> > > allocating RAM really doesn't work consistently.
> > >
> > > Just out of curiousity, has anyone seen SQL Server give back RAM or is
> > there
> > > something I am missing?
> > >
> > > Thanks for your thoughts
> > >
> > > Richard Speiss
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: lost memory on a 4GB amd64
    ... I have the same problem with 45 Tyan S2885 boards, ... The one running machine has the following configuration: ... Both board do not run stable with more than 2GB ram usable. ... I will try to install SLES 8 on the new boxes to see if it runs ...
    (Linux-Kernel)
  • Re: SQL Server 2000 and Memory
    ... I like the idea of the external configuration with some predefined options. ... > configuration changes into the install. ... > Senior Database Administrator ... > I support the Professional Association for SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Least System Draining Music Player
    ... my PC slows down so much that I can't even open internet ... But it is very configurable and during install you can ... configuration, so from that I have an idea that it does not stress as much ... On my old pc, 450mhz, 128mb ram, winamp used to be fine. ...
    (rec.music.hip-hop)
  • Re: SQL 2005 RTM - Reporting Services dbs missing
    ... RS2005 install using the config tool however again, ... Jasper Smith (SQL Server MVP) ... >> Did you select the default configuration for RS during install? ... >> run through the Reporting Services Configuration tool? ...
    (microsoft.public.sqlserver.setup)
  • Re: TD Performance, slow due to Q811279 installed twice? or just slow?
    ... Make sure that you buy additional 512 MB of ram. ... Or for bigger projects upgrade to 2GB of RAM. ... > configuration stuff. ... > Note when you install this QFE, as re-installing it over an original ...
    (microsoft.public.windowsxp.embedded)
Loading