?
Solved

SQL Server not using enough memory

Posted on 2006-05-01
7
Medium Priority
?
315 Views
Last Modified: 2010-08-05
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.



0
Comment
Question by:monosodiumg
6 Comments
 
LVL 27

Expert Comment

by:ptjcb
ID: 16578039
Is the SQL Server set to use dynamic memory or do you have a MIN/Max?
0
 
LVL 6

Expert Comment

by:tatw
ID: 16578420
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
http://support.microsoft.com/kb/811891/en-us

Also, are you using PAE in boot.ini? For server that has more than 4 GB ram should use this option.
http://support.microsoft.com/kb/283037/en-us

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16579121
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Author Comment

by:monosodiumg
ID: 16584103
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.
0
 
LVL 2

Accepted Solution

by:
ewahner earned 1500 total points
ID: 16585361
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
go
sp_configure 'max server memory', '5120'
go
reconfigure with override

If SQL is set to 5120...it will show that it is using all 5120.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16587997
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.



0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question