Memory required for SQL Server 2000

We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.In most situations, SQL Server's memory allocation will be lowest when
you first start the service. SQL Server will cache each data page as it
is read from disk until a minimum free memory threshold is reached (from
memory the threshold is 10MB by default). SQL Server then removes least
recently used pages from its cache, and releases memory, as necessary in
order to maintain the free memory threshold. This means that the longer
the service runs the more memory it will use (up to a limit). This is
Microsoft's dynamic memory management model.
One exception to this "increasing memory use" model is when you
configure SQL Server to grab its entire memory allocation on service
start-up, in which case all the memory, up to the configured maximum
limit, is reserved up front. However, there are no situations (that I
can think of) where SQL Server initially uses more memory and then
releases a substantial portion of it to "stabilise" at a certain level
(unless you start messing around with DBCC to flush the data buffer &
procedure cache).
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:

>We have been running an application on SQL Server 2000 on a Windows 2000
>Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
>From the statistics, we find that the memory utilization of that Windows
>2000 Server is around 200MB of memory. In this way, we attempt to change
>the RAM from 2GB to 1GB. However, we find that the memory utilization is a
s
>high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
>We just thinking the rise of memory required is only for the first 1 to 2
>hours OR not ? If there any reason for a SQL Server 2000 to grab more
>memory when we start it up ? Will the memory be stabilized to become 200MB
>again ?
>
>Your advice is sought.
>
>|||Dear Mike,
Thank you for your advice.
If the physical RAM is 2GB and I change the maximum RAM setting for the SQL
Server 2000 to 1.5GB. Does it mean that the SQL Server 2000 will use most u
p to 1.5GB RAM ?
Thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:OYx8BvknGHA.3340@.
TK2MSFTNGP02.phx.gbl...
In most situations, SQL Server's memory allocation will be lowest when you f
irst start the service. SQL Server will cache each data page as it is read
from disk until a minimum free memory threshold is reached (from memory the
threshold is 10MB by default). SQL Server then removes least recently used
pages from its cache, and releases memory, as necessary in order to maintain
the free memory threshold. This means that the longer the service runs the
more memory it will use (up to a limit). This is Microsoft's dynamic memor
y management model.
One exception to this "increasing memory use" model is when you configure SQ
L Server to grab its entire memory allocation on service start-up, in which
case all the memory, up to the configured maximum limit, is reserved up fron
t. However, there are no situations (that I can think of) where SQL Server
initially uses more memory and then releases a substantial portion of it to
"stabilise" at a certain level (unless you start messing around with DBCC to
flush the data buffer & procedure cache).
mike hodgson
http://sqlnerd.blogspot.com
Robert wrote:
We have been running an application on SQL Server 2000 on a Windows 2000
Server with 2GB of memory (As a Virtual Machine on VMWare ESX Server).
From the statistics, we find that the memory utilization of that Windows
2000 Server is around 200MB of memory. In this way, we attempt to change
the RAM from 2GB to 1GB. However, we find that the memory utilization is as
high as around 1GB. In this way, we change the RAM from 2GB to 1GB.
We just thinking the rise of memory required is only for the first 1 to 2
hours OR not ? If there any reason for a SQL Server 2000 to grab more
memory when we start it up ? Will the memory be stabilized to become 200MB
again ?
Your advice is sought.|||It means that SQL Server will be allowed, at most, a 1.5GB address
space. It does not necessarily mean this will be backed by physical
memory. If there is not enough physical memory to support SQL Server's
requests, in addition to the other applications requesting memory on the
box (including the OS itself), then part of that memory space will be
backed by virtual memory, which, needless-to-say, is not a
high-performance situation. The exception to this, which does not apply
to you because you are playing with less than 4GB of physical memory, is
AWE memory, which is always satisfied from physical memory (it is never
swapped to disk).
*mike hodgson*
http://sqlnerd.blogspot.com
Robert wrote:
[vbcol=seagreen]
> Dear Mike,
> Thank you for your advice.
> If the physical RAM is 2GB and I change the maximum RAM setting for
> the SQL Server 2000 to 1.5GB. Does it mean that the SQL Server 2000
> will use most up to 1.5GB RAM ?
> Thanks
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:OYx8BvknGHA.3340@.TK2MSFTNGP02.phx.gbl...
> In most situations, SQL Server's memory allocation will be lowest
> when you first start the service. SQL Server will cache each data
> page as it is read from disk until a minimum free memory threshold
> is reached (from memory the threshold is 10MB by default). SQL
> Server then removes least recently used pages from its cache, and
> releases memory, as necessary in order to maintain the free memory
> threshold. This means that the longer the service runs the more
> memory it will use (up to a limit). This is Microsoft's dynamic
> memory management model.
> One exception to this "increasing memory use" model is when you
> configure SQL Server to grab its entire memory allocation on
> service start-up, in which case all the memory, up to the
> configured maximum limit, is reserved up front. However, there
> are no situations (that I can think of) where SQL Server initially
> uses more memory and then releases a substantial portion of it to
> "stabilise" at a certain level (unless you start messing around
> with DBCC to flush the data buffer & procedure cache).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>|||For a little more background info about it, see the blog entry I just wrote:
http://sqlnerd.blogspot.com/2006/07...sql-server.html
Plus there's a stack of info about it in BOL and several quality
publications including The Guru's Guide to SQL Server Architecture and
Internals
<http://www.amazon.com/gp/product/02...8498336?ie=UTF8>
by Ken Henderson and Inside SQL Server 2000
<http://www.amazon.com/gp/product/07...8498336?ie=UTF8>
by Kalen Delaney.
*mike hodgson*
http://sqlnerd.blogspot.com
Mike Hodgson wrote:
[vbcol=seagreen]
> It means that SQL Server will be allowed, at most, a 1.5GB address
> space. It does not necessarily mean this will be backed by physical
> memory. If there is not enough physical memory to support SQL
> Server's requests, in addition to the other applications requesting
> memory on the box (including the OS itself), then part of that memory
> space will be backed by virtual memory, which, needless-to-say, is not
> a high-performance situation. The exception to this, which does not
> apply to you because you are playing with less than 4GB of physical
> memory, is AWE memory, which is always satisfied from physical memory
> (it is never swapped to disk).
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Robert wrote:
>