Interesting question about memory usage - standard edition

Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
>> Hi,
>> I have sql server 2000 standard edition (windows 2000
>> server - NOT advanced) on the server which has 2560MB of
>> memory. This edition has restriction of 2048MB of memory
>> it can use (max memory parameter set to this value).
>> I'm making performance analysis of whether we need to
>> upgrade memory on this server (and use /awe) - we're
>> about to add some additional load to the system.
>> I collected statistics using performance monitor. Among
>> many counters, I was monitoring Available Memory (MB).
>> This value varies from 325 to 660.
>> Given that 512MB of memory is totally invisible to the
>> sql server, does this mean that we're hitting the wall
>> regarding memory for sql server? I also noticed
something
>> strange, not aligned with bol, that sql server doesn't
>> release memory at all, because even if the server is
>> idle, available memory doesn't grow higher than 660MB
>> (there is nothing other than sql server that uses this
>> memory - dedicated database server)... Since performance
>> monitor is in this case useless (or not?), how can you
>> determine how much memory database engine really
>> uses/needs?
>> Thanks
>
>.
>