How to tell how much memory is actually needed by my SQL Server 2000?

The easiest way of doing it is to go onto your server,
doing a CTR ALT DEL, bringing up task manager, then
looking for SQL Server under the processors list.
Peter
"I'm just preparing my impromptu remarks."
Winston Churchill

>--Original Message--
>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 if, for example, we made 1TB of
RAM
>available, will SQL just use all memory assigned to it
until it has
>the whole DB in memory? If not, what's the cut off? Or is
the fact
>that the memory usage of our instance of sql peaks at
using all the
>memory an indication that we do not have enough available?
>Any help would be greatly apprechiated.
>Thanks.
>.
>I don't know if that method will work.
We are running SQL 2k EE on Win2k AS. We are using /3GB /PAE and SQL Server
is configured to use 6144 MB.
Task manager reports that sqlservr.exe is using 115,524K on the process tab
however on the performance tab the physical memory is reported as
Total 7863624
Available 1184100
Keith
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...[vbcol=seagreen]
> The easiest way of doing it is to go onto your server,
> doing a CTR ALT DEL, bringing up task manager, then
> looking for SQL Server under the processors list.
> Peter
> "I'm just preparing my impromptu remarks."
> Winston Churchill
>
>
> starting to
> memory is
> RAM, a min of
> too
> let SQL
> uses, of course
> is
> memory" =
> average "free pages"
> amount of memory
> min we
> actually used
> best way?
> RAM
> until it has
> the fact
> using all the|||Keith,
I think you have a point there.
However Task Manager will always show the amount of memory
that has been allocated to a process, and is very accurate
(as far as I know)
I suppose the question(s) is more of
1. How much memory has been allocated
2. How much of that memory is in use
3. How much more memory can be accessed
Anyway as it piked my interest I dug out my copy of
Performance Monitoring with SQL Server 2000 and set up the
counters Total Server Memory and Target Server Memory.
As you know it will show percentage of the actual memory
useage on the server.
Currently our server is set to 48% of all server memory,
which translates roughly to the amount of memory shown as
used in Task Manager, though I will freely admit its not
completely accurate.
I think the answer then is there is no way of totally been
able to predict the amount of memory being used, as both
things give different figures.
So I think I will change my mind and go down your route
using the profiler if nothing more than the fact you take
the findings at a regular time and do something with them.
Anyway thanks for that.
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>I don't know if that method will work.
>We are running SQL 2k EE on Win2k AS. We are
using /3GB /PAE and SQL Server
>is configured to use 6144 MB.
>Task manager reports that sqlservr.exe is using 115,524K
on the process tab
>however on the performance tab the physical memory is
reported as
>Total 7863624
>Available 1184100
>--
>Keith
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:15d501c4bc26$57a92ae0$a601280a@.phx.gbl...
of[vbcol=seagreen]
to[vbcol=seagreen]
that[vbcol=seagreen]
server[vbcol=seagreen]
(the[vbcol=seagreen]
is[vbcol=seagreen]
of[vbcol=seagreen]
is[vbcol=seagreen]
available?[vbcol=seagreen]
>.
>|||Spot on. Windows counters such as these in task manager and process
and working set in perfmon cannot monitor memory that is enabled by
AWE, 4GB+. I wish it were that easy.
In my situation, because the min and max memory is set to 4GB and 6GB
none of this can be monitored by the usual tools, it's all AWE memory.
What i need to know is;
-Should i be concerned that SQL memory usage peaks at using all memory
assigned to it?
-Should it peak at 100% mem usage, is this normal?
-If i assigned, for example, 40GB of memory would it just keep using
memory until it had the hole db in memory (20gb db).
-If that's not the case when does it stop using memory?
Your thoughts,
Thanks.|||comments inline
Keith
"Tim Richardson" <tim@.specialmail.co.uk> wrote in message
news:25879432.0410280128.5080316a@.posting.google.com...
> Spot on. Windows counters such as these in task manager and process
> and working set in perfmon cannot monitor memory that is enabled by
> AWE, 4GB+. I wish it were that easy.
> In my situation, because the min and max memory is set to 4GB and 6GB
> none of this can be monitored by the usual tools, it's all AWE memory.
> What i need to know is;
> -Should i be concerned that SQL memory usage peaks at using all memory
> assigned to it?
No

> -Should it peak at 100% mem usage, is this normal?
Yes
INF: SQL Server Memory Usage
http://support.microsoft.com/defaul...b;en-us;q321363

> -If i assigned, for example, 40GB of memory would it just keep using
> memory until it had the hole db in memory (20gb db).
It might use slightly more RAM [than 20GB] because it would (could) have
data and query plans in cache. It might also decide that it wants
additional memory for SQL Agent, user connections, and so on.

> -If that's not the case when does it stop using memory?
When it does not need any more.

> Your thoughts,
> Thanks.