SQL Server Performance

Memory is limiting to 4GB

Discussion in 'Performance Tuning for Hardware Configurations' started by Deano, Jul 5, 2007.

  1. Deano New Member

    Hi There
    I am after a bit of help with Memory Usage on SQL 2000
    The configuration
    SQL 2000 EE SP4
    Windows 2003 R2 EE SP2
    SQL is configured on Active/Passive Failover
    Active Server has 8GB RAM

    No matter what options I select SQL will not use more than 4GB of RAM
    It starts up with exactly 4Gb, but it will not use anymore no matter what the load.

    AWE is enabled
    /PAE /3GB are on (even tried without /3GB)
    Perf counters show it also uses exaclty 4GB and it wont change.
    Have played with many options for Max and Min Memory.

    SQL Service Account has Lock Pages in Memory

    I have read all the articles here and in many other places to make this work, but why won't it use more than 4GB RAM
    Is there a bug that I am not aware of?
    This server is dedicated for SQL.
    I am really getting stuck here, any help appreciated

    Thankyou in advance

    Dean


    affinity mask-2147483648214748364700
    allow updates0100
    awe enabled0111
    c2 audit mode0100
    cost threshold for parallelism03276755
    Cross DB Ownership Chaining0111
    cursor threshold-12147483647-1-1
    default full-text language0214748364710331033
    default language0999900
    fill factor (%)010000
    index create memory (KB)704214748364700
    lightweight pooling0100
    locks5000214748364700
    max degree of parallelism03200
    max server memory (MB)4214748364775087508
    max text repl size (B)021474836476553665536
    max worker threads3232767255255
    media retention036500
    min memory per query (KB)512214748364710241024
    min server memory (MB)0214748364766206620
    nested triggers0111
    network packet size (B)5123276740964096
    open objects0214748364700
    priority boost0111
    query governor cost limit0214748364700
    query wait (s)-12147483647-1-1
    recovery interval (min)03276700
    remote access0111
    remote login timeout (s)021474836472020
    remote proc trans0100
    remote query timeout (s)02147483647600600
    scan for startup procs0100
    set working set size0100
    show advanced options0111
    two digit year cutoff1753999920492049
    user connections03276700
    user options03276700

  2. satya Moderator

    Have you collected the stats using PERFMON counters for memory?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. joechang New Member

    the relevent counters for this are below
    you did not mention what specific counter you were citing at 4G
    hence we cannot tell you anything meaningful


    SQLServer:Buffer Manager -> Database pages
    SQLServer:Buffer Manager -> Total pages

    SQLServer:Memory Manager -> Total Server Memory (KB)


  4. Deano New Member

    quote:Originally posted by satya

    Have you collected the stats using PERFMON counters for memory?

    Thankyou for your reply
    Yes, I have collected the stats
    At Service Startup and throughout SQL usage - both Target Server Memory and Total Server Memory run at 4GB (4166160 kb) they stay at this figure when running with a few kb of this)

    Something else that I have noticed is in SQL Enterprise Manager, Server Properties, Memory. It is set to dynamically assign memory with matching min and max figures from sp_configure as expected. But if you look at the greyed out section below for Use a fixed memory size (MB) it is set to 4095, why is it doing this and not going all the way up to 8190 at the top of the scale?
    I have also tried setting this option to around 7000 and it still doesn't make any different.

    regards
    Dean
  5. joechang New Member

    you should run

    exec master..xp_msver 'PhysicalMemory'
  6. Deano New Member

    quote:Originally posted by joechang

    the relevent counters for this are below
    you did not mention what specific counter you were citing at 4G
    hence we cannot tell you anything meaningful


    SQLServer:Buffer Manager -> Database pages
    SQLServer:Buffer Manager -> Total pages

    SQLServer:Memory Manager -> Total Server Memory (KB)

    Thankyou for your reply, I have been logging the counters for the past few hours this morning as users are starting.

    SQLServer:Buffer Manager -> Database pages
    2258 - 76650 and rising (with a peak at 204717), more users are connecting this morning after lasts nights changes/testing

    SQLServer:Buffer Manager -> Total pages
    519824

    SQLServer:Memory Manager -> Total Server Memory (KB)
    4165840-4166568 - This figure has not moved since service startup (but why not higher at initial startup)

    exec master..xp_msver 'PhysicalMemory'
    8190 (8587497472)

    Hope this helps.

    Dean


  7. joechang New Member

    i find it very unusual you peak database pages is 200K but total pages 512K

    what does your db do?
    what is the size of data?
    does your app not use stored proc?

    what are the other counter vallues (for pages)
    SQLServer:Buffer Manager ->

    ie, procedure cache pages, reserved pages, stolen pages
  8. Deano New Member

    quote:Originally posted by joechang

    i find it very unusual you peak database pages is 200K but total pages 512K

    what does your db do?
    what is the size of data?
    does your app not use stored proc?

    what are the other counter vallues (for pages)
    SQLServer:Buffer Manager ->

    ie, procedure cache pages, reserved pages, stolen pages

    I appreciate your assistance, but the issue I am having is related to the memory not being available to SQL from startup even though I am telling it to use it, not the performance of the server.

    Procedure Cache Pages
    27919

    Reserved Pages
    270

    Stolen Pages
    29664

    At the time of the above checks - Database Pages 142658

    There is 12GB of DBs at the moment and growing.
    DBs are used for many different things.
    Some DB's used stored procedures.

    Dean

  9. joechang New Member

    i don't give a damn about your performance
    i am wondering why the number are strange
    something must be screwed up

    but for you immediate issue
    it is almost as if you did not have EE installed
    if AWE is not active, there is no way SQL could be using 4GB
    it would be 2 or 3 GB unless you have Windows 64-bit installed
  10. joechang New Member

    ugh!
    are you on build 2039?

    get a later hotfix
    this was very widely discussed when it came out
    but most of us probably assumed everyone had seen it by now
  11. ajitgadge New Member

  12. satya Moderator

    Not sure why you are referring to apply that hotfix as the issue seems a different one.

Share This Page