AWE

Our production database server has 16GB of memory. It is running Windows 2003 Server Enterprise and SQL Server 2000 Enterprise. In the boot.ini we have the /PAE option. In SQL Server we have AWE enabled, dynamic memory managemement, 0MB min, and 14336MB maximum. The database server's only purpose is to run SQL Server.
So I have three questions based on this information.
1. Have we configured our server correctly for using AWE?
2. Did we allocate SQL Server too much/too little memory (14336MB)?
3. How would I tell if SQL Server actually using the memory we gave it? Are there some specific counters I should look at? Quest Performance Analysis is not much help as it simply shows that around 15400MB of memory are in use. If you look at task manager it is the same thing. I cache hit ratio since this server has been put in has averaged above 97%.

Here is a query that will return some memory counter information:select
* from sys.dm_os_performance_counters
where counter_name = 'Total Server Memory (KB)'
or counter_name = 'Target Server Memory (KB)'
The 'Total Server Memory (KB)' will show you how much SQL Server is using in the buffer cache, not the total amount of memory SQL Server is actually using.

I guess I should have scrolled my screen a little to the right to see that SQL Server 2000 was being used. Any way this query will work in SQL Server 2000 and 2005 for the matter:select
* from master.dbo.sysperfinfo
where counter_name = 'Total Server Memory (KB)'
or counter_name = 'Target Server Memory(KB)'

It works but all I see is 14436 for both items which is the upper limit I specified for SQL Server.

That means SQL Server has consumed up to the max. If you stop and restart SQL Server the total number should be less than the target.

I should have added that over time should see you total memory grow and eventually it might reach the max. SQL Server only release memory if it detects that the system is under memory pressure.

I ran the statement in my test envrironment, then restarted the SQL Server service, then ran it again. Here are the results:
Before
SQLServer:Memory Manager Target Server Memory(KB) 10493904 65536
SQLServer:Memory Manager Total Server Memory (KB) 10493904 65536
After
SQLServer:Memory Manager Target Server Memory(KB) 10492912 65536
SQLServer:Memory Manager Total Server Memory (KB) 10492912 65536
It is the same in all of my envrionments.

[quote user="satya"]
Are you having any performance issues lately?
Are you getting any memory based alerts?
[/quote]
I am not having in performance problems or alerts. I just wan to find out if SQL Server is effectively using the 14GB of memory it has been allocated.

Based on those numbers looks like you are using 10 GB.

10GB is what have set as the upper limit on my test server. How could it be using 10GB worth of memory right after a SQL Server restart?

Now that is a very good question. I would check the minimum server memory and verify it truly is set to 0 (zero).

[quote user="Greg Larsen"]
Now that is a very good question. I would check the minimum server memory and verify it truly is set to 0 (zero).
[/quote]
Output from sp_configure:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 14336 14336
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 0 0
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
This is not a 64 bit install.

looks like you have not set the minimum server memory. Based on what you have provided I'm guessing SQL gets to the max memory very quick after reboot. I have heard of this happening.

I just found the following statement in BOL:
Important Instances of SQL Server 2000 running in Address Windowing Extensions (AWE) memory mode do allocate all the full amount of memory specified in max server memory on server startup. For more information about AWE memory, see Managing AWE Memory.
I guess that would explain it.