SQL Server not using enough memory

SQL Server 2000 Ent on W2K Advanced SP4.
Server running with /AWE and 8G of RAM.
SQL Server uses ~100M (yes M) accoridng to task manager and we are seeing slowness and timeouts.

Task mgr shows total memory usage at 6.5G leaving 1.5G free. The sum of the memory consumption for all process in task mgr is less than 2G so:

1.What's using most of the 6.5G supposedly allocated or how do I investigate that?
2.Is SQL server really using only 100M and how can I check that? Therea re about 10 dbs and a few dozen nominal users with up to 2000 actual users. We shoudlbe getting far better perf from that box than we are seeing, based on comparisons with other boxes.

LVL 12
Who is Participating?
ewahnerConnect With a Mentor Commented:
Execute sp_configure

Look at the following values:

awe enabled
max server memory (MB)

awe enabled should have min = 0, max = 1, config_value = 1, run_value = 1

You must set the max server memory value or the OS will not likely allocate the memory and force you to page.  Generally speaking I leave the OS with at least 3GB.  My servers all have 32GB.  So in your case set max server memory to: 5120 If it is set to like 2147483647, then you are probably set to dynamic.  I am guessing that SQL Server is the only thing running on this server.  If it is not then you may not want to set these values as it may affect performance of other apps.

To set the max server memory or the awe enabled you would execute the following:

sp_configure 'awe enabled', 1
sp_configure 'max server memory', '5120'
reconfigure with override

If SQL is set to 5120...it will show that it is using all 5120.
Is the SQL Server set to use dynamic memory or do you have a MIN/Max?
Please take a look at the below article from MS
SQL Server only uses 2 GB of memory even though the AWE option is enabled

Also, are you using PAE in boot.ini? For server that has more than 4 GB ram should use this option.

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Guy Hengel [angelIII / a3]Billing EngineerCommented:
There might be another reason to the slowlyness: missing indexes.
Please check your tables if they have indexes that correspond to the where clauses of the queries.

Also, how big is the database (data file) and the transaction log file?
monosodiumgAuthor Commented:
Thanks guys. Your points addressed:

Set to allocate memory dynamically

PAE is set in boot.ini.

SQL server working set size is pretty constantly around 100M and never goes much beyond that.

The counters "Target server memory" and "Total  server memory" for the "SQL Server:Memory Manager" object are both steady at 6.3GB. That seesmt obe saying SQL server is using most of the memory. But the working set size is only 100M and I guess that's all that's showing up in Task Manager. What's SQL Server doing with the other 6.2G?

There are about 30 applications spread across 3 servers. They used to perform better. There has been no sinificant change in volume of data or load since they last performed OK. I am awaiting specific data on file sizes etc.

It's beginning to look to me like a connection pooling issue but independently I'd like to understand the memory numbers above.
FROM BOL (SQL Server 2000):

Do not set set working set size if you are allowing SQL Server to use memory dynamically. Before setting set working set size to 1, set both min server memory and max server memory to the same value, the amount of memory you want SQL Server to use.

On startup SQL Server reserves approximately 384MBs for MemToLeave. The rest is in the buffer pool. It may look like SQL Server is not using all the memory. You should use perfmon counters instead. They will be more accurate. You should also remove the Set Working Size configuration because you are using memory dynamically.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.