SQL Server not using enough memory

Posted on 2006-05-01
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.

Question by:monosodiumg
    LVL 27

    Expert Comment

    Is the SQL Server set to use dynamic memory or do you have a MIN/Max?
    LVL 6

    Expert Comment

    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.

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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?
    LVL 12

    Author Comment

    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.
    LVL 2

    Accepted Solution

    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 will show that it is using all 5120.
    LVL 27

    Expert Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now