SQL Server Performance

/3GB memory usage

Discussion in 'Performance Tuning for DBAs' started by Hari, Mar 31, 2005.

  1. Hari New Member

    I upgraded the SQL SErver from standard to Enterprise edition recently on a Windows 2000 server. Even though the system has 4GB memory, still only 1.7 GB is used by SQL server. I have set /3GB option and also awe is turned on. why it is not using more memory?
    The startup parametrs are set to use memory dynamically upto a max of 2.8 GB. But still no improvement.
    Could anyone help me with this?
  2. Luis Martin Moderator

  3. satya Moderator

    Capture PERFMON counters to assess more information about memory usage, ensure the performance is not degraded with the changes.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. derrickleggett New Member

    First of all, you don't need awe enabled or the /PAE switch if you only have 4GB of RAM, so take them off. Second, either set the MIN and MAX RAM to 2.8GB and leave it at that, or expect it not to use the entire 3GB unless it needs it. Look at your total and target SQL Server memory counters. If they are even, SQL Server basically doesn't think it needs more RAM. In addition, look at the buffer cache hit ratio and the cache hit ratio. If they are above 97%, then you aren't going to probably see a huge benefit with more RAM.

    How big are the databases on this server?


    When life gives you a lemon, fire the DBA.
  5. gurucb New Member

    /3GB switch is going to make the SQL Server address space to be 3GB instead of 2GB by default. So, if sql server does not have a big load that it will not use the 3Gigs of RAM even if you have more memory.

    In the perfmon

    Check for Virtual Bytes max for the SQL Server process, that should have touched 3GB some time. That indicates that SQL Server virtual address space has increased to 3GB.

    Also if you have only 4Gigs of RAM there is no need to apply /PAE swtich. What are teh values for Target Server memory and Total Server memory.

    If Target Server Memory is more that total server memory and if physical server memory is available that the additional space is going to be committed.
  6. djmaro76 New Member

    I'm running into the same issue as Hari in this original post. I have done my homework with this issue and I just can't make sense as to why the /3GB switch doesn't seem to be working as designed.

    Windows 2000 Server, SQL 2000 Developer Edition, 4GB RAM, /3GB switched turned on in the boot.ini. PAE and AWE are disabled.

    I ran a load simulator in SQL that creates a temp table and populates the table with 100,000 rows and just loops itself. This used up the RAM fairly quickly but RAM usage stops climbing when I reach about 1.7GB.

    I can't seem to get SQL to use more than 1.677 GB RAM. I'm using SQLEM to set the MIN and MAX size for memory to 3GB. Perfmon shows Target Server Memory @ 1.677 GB and Total Server Memory @ 1.677 GB. Process - Working Set never goes above 1.7 GB. Buffer Cache Hit Ratio is at 99% constantly. Page file is around 5% usage

    So I can see that if the buffer cache hit ratio is over 90%, it doesn't need more RAM. How can this be when I'm running a stress test on the server and it stops at 1.7GB. RAM should still be climbing until I reach my 3GB limit as mentioned in SQLEM, Server, Properties, Memory tab.

    It's almost like the /3GB switch in the boot.ini isn't doing anything.

  7. satya Moderator

    BTW are you getting any performance issues while running these queries.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. djmaro76 New Member

    Performance doesn't seem to be too bad when executing the stress test. The cpu stays around 50%. This box has 4 700mhz procs.

    Do you suggest another counter in perfmon to find my problem?
  9. Adriaan New Member

    About djmaro76's problem:

    Does SQL 2000 Developer Edition support more than 2 GB of RAM? I thought only Enterprise Edition did that.
  10. djmaro76 New Member

  11. satya Moderator

    BOL defines:
    SQL 2K Developer edition will support upto:
    64 gb - Windows 2000 DataCenter
    8 gb - Windows 2000 Adv.server
    4gb - Windows 2000 server
    2gb - Win2k Prof.
    3gb - WIN NT4 Enterprise server
    2gb - Win NT 4 server
    2gb - Win NT Workstation

    Collect PERFMON counters for Memory, physical disk, process, processor, SQL Server memory etc. for fruther assessment.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  12. djmaro76 New Member

    Sayta, per your request.
    Available Kbytes – 1686388.840
    Cache Bytes – 114914918.400
    Page Reads/Sec – 0.109
    Page Writes/sec – 0.000

    % Disk Read Time – 0.337
    % Disk Time – 0.386
    % Disk Write Time – 0.050
    % Idle Time – 99.646
    Avg. Disk Bytes/Read – 10000.340
    Avg. Disk Bytes/Transfer – 6309.415
    Avg. Disk Bytes/Write – 5137.297
    Disk Bytes/Sec – 22328.323
    Disk Read Bytes/sec – 8529.921
    Disk Reads/Sec – 0.853
    Disk Transfer/sec – 3.539
    Disk Write Bytes/Sec – 13798.402
    Disk Writes/sec – 2.686
    Split IO/Sec – 0.109

    Process: Total
    Page File Bytes – 1930123715
    Pool Paged Bytes – 1128578.200
    Working Set – 1966914929

    Processor: Total
    % DPC Time – 0.053
    % Interrupt Time – 0.276
    % Privileged Time – 1.708
    % Processor Time – 6.259
    % User Time – 4.548
    APC Bypasses/sec – 62.484
    DPC Bypasses/sec – 0.000
    DPC Rate – 1.160
    DPCs Queued/sec – 163.787
    Interrupts/sec – 503.230

    SQL Server: Memory Manager
    Granted Workspace (KB) – 102.480
    Target Server Memory (KB) – 1678650.320
    Total Server Memory (KB) – 1678650.320

  13. satya Moderator

    Nothing wrong with the collected values, and try to run DBCC SHOWCONTIG on mostly used tables to see the any fragmentation issues.

    If the current performance is not affected then I suggest to leave the settings as is.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  14. djmaro76 New Member

    I'm only using this server to test the /3GB switch option. If I can get this to work properly, then my goal is to make recommendations to clients to make changes to the server to take full advantage of the RAM. But for some reason, this server just won't use all the RAM. It doesn't make sense that the server just doesn't need the extra RAM and decides to not use more than 1.7 GB.

    We have clients with servers that have 4 GB or more RAM and they are only using SQL 2000 Standard Edition. I'm trying to justify the need to upgrade to SQL 2000 Enterprise Edition. I was able to take another server with 8GB and enable AWE so now SQL uses 6.5GB for itself.

    I'm not too worried about performance issues at this point. I just need to get that darn /3GB switch to actually give SQL that extra 1GB of RAM. Everything on this server checks out. My boot.ini has /FASTDETECT /3GB.

    I'm a little confused on something. In some of the threads, I see recommendations of enabling PAE and AWE on a server with only 4GB RAM. But in other threads, I see suggestions of turning it off and only using the /3GB switch. If PAE and AWE are only for servers with more than 4GB, why are there recommendations floating around to enable those options?
  15. djmaro76 New Member

    I found one issue that might be the cause of all my problems but could use some feedback.

    Will the /3GB switch work if you have less than 4 GB of RAM in the server?

    The server I supplied the PERFMON counters for this morning only has 3,800,866KB of total RAM. I was assuming that 3.8GB was actually 4GB minus some RAM that the OS takes for itself.
  16. djmaro76 New Member

    Anyone have any ideas on my previous question? I'm thinking that the reason I can't get SQL to use more than 1.7 GB is because Windows only notices 3.8GB of RAM. I just need someone to verify this for me.

    The server does have 4GB but it allocates 256mb for the video card.

    I have searched the net and can't seem to find a true answer.
  17. djmaro76 New Member

    I found the problem. It has nothing to do with 3.8 GB as apposed to 4GB. The problem is that I'm running Windows 2000 Server Standard and that version doesn't support the /3GB switch. Only 2000 Advanced Server supports this feature.


    Live and learn!

  18. dhilditch New Member

    I believe also if you have 2003 server that you'll find the OS uses 1GB and applications are automatically allowed 3GB which SQL Server pretty much immediately grabs. Not sure if this is the same for Windows 2000 Server - I suspect probably not and it has a max of 2GB per application.

    Small Businesses
  19. retroman New Member

    Hi all,

    After reading lot of articles on this large memory isssues on MSSQL, I made a table summary assumping a server with 8GB of phy memory and following OS and MSSQL installed

    OS : Win 2K Adv Server or Win 2k Datacenter or Win 2003 Server or Win 2003 Enterprise or Win 2003 Datacenter
    MSSQL : MSSQL 2000 Enterprise or MSSQL 2000 Developer

    The table is the memory achievable with the possible memory settings combination in OS and MSSQL:

    Settings..............Kernel Mode Memory (OS)............Appl Mode Memory (e.g. MSSQL)..........Extended Mem > 4GB range
    /PAE, AWE enabled..........2GB..........................................6GB (2GB + 4GB ext mem)..................4GB
    /PAE, /3GB.....................1GB..........................................3GB..................................................4GB
    /PAE, /3GB, AWE enabled.1GB...........................................7GB (3GB + 4GB ext mem).................4GB

    Can someone care to confirm my understanding of this whole things correct?

    P/S: Sorry for the dotted line to align the columns.
  20. retroman New Member

    Can someone in this forum pls comment/confirm on the my understanding above.
    I need senior people to assure my understanding/absorbtion from threads/articles that I have read.

    Thank you.
  21. derrickleggett New Member

    That looks good retro. There really isn't a reason to put Datacenter on the list though. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] You have to buy a complete "datacenter solution" from a vendor to even get into that product line. It's extremely expensive. Other than that though, it looks great.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  22. retroman New Member


    I just tested the AWE memory in SQL Server 2000 (SP3a) on Windows Server 2000 Standard Edition with 4GB phy memory.

    No /3GB switch and No /PAE switch in boot.ini

    1. Grant 'Lock Page in Memory' to Administrator (because MSSQLServer startup account is LocalSystem)

    sp_configure 'show advanced options', 1
    sp_configure 'awe enabled', 1
    sp_configure 'min server memory', 3666
    sp_configure 'max server memory', 3666

    3. Restart MSSQLServer service

    Interestingly Total Server Memory & Target Server Memory in Perf Monitor showed same figure around 3GB respectively.

    From AWE article on this site http://www.sql-server-performance.com/awe_memory.asp) stated use /3GB switch for server with 4GB and below. But I didn't set that in boot.ini.

    My question is does that mean that AWE and /3GB switch on its own has the same effect in a Windows 2000 Standard Edition that will set the user mode memory to 3GB and kernel mode memory to 1GB?

Share This Page