How to determine how much RAM my SQL server is actually using???

[Tim Richardson]

I've been doing a lot of reading on this and my head is starting to
hurt! It seems to be quite a feat to work out how much memory is
actually being used by our server.

I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of
4GB and a max of 6GB is assigned to SQL server.

I'm trying to work out whether we've assigned enough or too
much/little memory to SQL server. My first thought was to let SQL
dymanically manage its own memory and see how much it uses, of course
when AWE (/3GB /PAE) is enabled it will just use all that is
available.

In perfmon "target server memory" = 6.1GB, "total server memory" =
6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).

My second thought was to use "total pages" - the average "free pages"
= average mem used, therefore giving me the average amount of memory
used by SQL. I found out that SQL uses a min of 4GB (the min we
assigned) and the max of all the memory, 6GB.

Is there an easier way of finding out how much memory is actually used
in this situation or is going by the above average the best way?

What i'm unsure about is will SQL just use all memory assigned to it
until it has the whole DB in memory? 20GB including indexes etc....

Any help would be greatly apprechiated.

[Tim Richardson]

ti*@specialmail.co.uk (Tim Richardson) wrote in message news:<25**************************@posting.google. com>...

I've been doing a lot of reading on this and my head is starting to
hurt! It seems to be quite a feat to work out how much memory is
actually being used by our server.

I'm running W2K advanced server with SQL 2000 EE, 8GB of RAM, a min of
4GB and a max of 6GB is assigned to SQL server.

I'm trying to work out whether we've assigned enough or too
much/little memory to SQL server. My first thought was to let SQL
dymanically manage its own memory and see how much it uses, of course
when AWE (/3GB /PAE) is enabled it will just use all that is
available.

In perfmon "target server memory" = 6.1GB, "total server memory" =
6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).

My second thought was to use "total pages" - the average "free pages"
= average mem used, therefore giving me the average amount of memory
used by SQL. I found out that SQL uses a min of 4GB (the min we
assigned) and the max of all the memory, 6GB.

Is there an easier way of finding out how much memory is actually used
in this situation or is going by the above average the best way?

What i'm unsure about is will SQL just use all memory assigned to it
until it has the whole DB in memory? 20GB including indexes etc....

Any help would be greatly apprechiated.

Looks like everyone is just as stumped as me!! Could really do with
some help on this one!

[Erland Sommarskog]

Tim Richardson (ti*@specialmail.co.uk) writes:

ti*@specialmail.co.uk (Tim Richardson) wrote in message

news:<25**************************@posting.google. com>...

I'm trying to work out whether we've assigned enough or too
much/little memory to SQL server. My first thought was to let SQL
dymanically manage its own memory and see how much it uses, of course
when AWE (/3GB /PAE) is enabled it will just use all that is
available.

In perfmon "target server memory" = 6.1GB, "total server memory" =
6.1GB, "total pages" = 768000 ( x 8KB = 6.1GB).

My second thought was to use "total pages" - the average "free pages"
= average mem used, therefore giving me the average amount of memory
used by SQL. I found out that SQL uses a min of 4GB (the min we
assigned) and the max of all the memory, 6GB.

Is there an easier way of finding out how much memory is actually used
in this situation or is going by the above average the best way?

What i'm unsure about is will SQL just use all memory assigned to it
until it has the whole DB in memory? 20GB including indexes etc....

With 6MB as the max limit, it seems unlikely that SQL Server would be
able to fit 20 GB into the cache. To do that it would need at least
21 GB, with the rough assumption that you need 1 GB for the query plans
and everything else.

But I will have to admit that this is not my best game, not the least
when you bring in that much of memory. Some of my MVP colleagues know
this kind of stuff a lot better. Have you tried posting to
microsoft.public.sqlserver.server?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp