SQL Server Performance

Physical Memory Utilization

Discussion in 'SQL Server 2005 General DBA Questions' started by CanadaDBA, Feb 12, 2007.

  1. CanadaDBA New Member

    Environment: Win2003 SP1, SQL Server 2K5

    My server has 16GB RM but it is using only 3GB. And I see my server is using 3GB of Virtual Memory, too. Why my physical memory is not being utilized? How can I increase Physical Memory usage and decrease VM usage?


    CanadaDBA
  2. MohammedU New Member

  3. satya Moderator

  4. CanadaDBA New Member

    When I check the "Use AWE to allocate memory" in "Server Properties" window, get the following error:

    Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process. (Microsoft SQL Server, Error: 5845)

    CanadaDBA
  5. satya Moderator

    Review MSDN article How to: Enable the Lock Pages in Memory Option (Windows)http://msdn2.microsoft.com/en-US/library/ms190730.aspx and restarting the machine typically helps permissions granted to the sql server service account take effect.




    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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  6. CanadaDBA New Member

    Satya, it says: On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance. Although not required, we recommend locking pages in memory when using 64-bit operating systems.

    I have 16GB RAM and want to use AWE but I don't want to impact the server's performance. Should I go ahead and enable the lock pages in memory option?


    CanadaDBA
  7. joechang New Member

    if this server is dedicated to SQL and properly configured, thats not an issue

    performance can be degraded because the locked pages cannot be paged out, reducing the memory available for other apps

    you have no other apps
  8. CanadaDBA New Member

    Inhttp://msdn2.microsoft.com/en-US/library/ms190731.aspx it says run the following to set the max server memory to 6GB but when I ran it, it set my server to 6MB!!!


    sp_configure 'min server memory', 1024
    RECONFIGURE
    GO
    sp_configure 'max server memory', 6144
    RECONFIGURE
    GO

    In server properties I see the Maximum server memory (in MB) is set to 2147483647.

    Note: I stop and start SQL Server but didn't reboot the machine.


    quote:Originally posted by satya

    Review MSDN article How to: Enable the Lock Pages in Memory Option (Windows)http://msdn2.microsoft.com/en-US/library/ms190730.aspx and restarting the machine typically helps permissions granted to the sql server service account take effect.




    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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

    CanadaDBA
  9. MohammedU New Member

  10. joechang New Member

    i think you are confused,
    why do you think your server was set to 6MB?
  11. MohammedU New Member

    Where do you see 6 MB?
    Use perfmon counters TOTAL SERVER MEMORY and TARGET SERVER MEMORY to check the memory ... do not use task manager.


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  12. CanadaDBA New Member

    MohammedU, my server is a 32 bit.

    I tried DBCC memorystatus and it shows 0 for AWE Allocated. I removed the check mark from "Use AWE to allocate memory" in Server Properties but the results for the DBCC was the same.

    But sp_configure 'awe enabled', 1 says: Configuration option 'awe enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.

    What does this mean?

    CanadaDBA
  13. satya Moderator

    If run_value is set to 1, AWE is enabled on the server.

    To view the behavior, examine the SQL Server: Memory Manager/Total Server Memory (KB) counter in System Monitor. On a computer that is running SQL Server Service Pack 3 (SP3), this value may be up to the amount of physical memory that is on the computer. On a computer that is running SQL Server SP4, this value will never be more than 50 percent of the physical 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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  14. CanadaDBA New Member

    Satya,

    OS is Windows 2003 SP1
    The machine has 16GB RAM.
    SQL Server: Memory Manager/Total Server Memory (KB) counter in System Monitor shows 1598208.

    What this number means? If this number is in KB, then does it mean I have almost 1.6GB?

    CanadaDBA
  15. MohammedU New Member

  16. satya Moderator

    It means 1560GB (16gb), isn;t it.

    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. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  17. CanadaDBA New Member

    Microsoft SQL Server 2005 Enterprise Edition
    Version: 9.00.2047.00
    Operating System: Microsoft Windows NT 5.2 (3790)

    Memory status on Tuesday at 10AM:
    Physical Memory:
    Total RAM: 16 GB
    Free: 13.4 GB
    Free %: 84%

    Virtual Memory:
    Max Size: 18.8 GB
    Free: 15.8 GB
    Free %: 84%

    Why my system is using VM while it has 13.4 GB RAM available?


    quote:Originally posted by MohammedU

    Did you enable locking pages? as Satya mentioned...
    http://msdn2.microsoft.com/en-US/library/ms190730.aspx

    What is your SQL build?
    If you are on SP4 without cumulative patch or hot fix SQL can't see all memory...seet the following article...

    Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4
    http://support.microsoft.com/kb/899761


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    CanadaDBA
  18. CanadaDBA New Member

    After I checked the "Use AWE to allocate memory" and set 'lock pages in memory' privilege last week, I rebooted SQL Server not the machine. <br /> <br />Today, I had the chance to reboot the machine. You can see the memory status in my previous post. It changed to the following after reboot:<br /><pre id="code"><font face="courier" size="2" id="code"><br />Physical Memory:<br />Total RAM: 16 GB<br /> Free: 11.8 GB<br /> Free %: 74%<br /><br />Virtual Memory: <br />Max Size: 18.8 GB<br /> Free: 13.8 GB<br /> Free %: 77%<br /></font id="code"></pre id="code"><br />It's great progress. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Now a question raised and that is why the virtual memory usage is increased too?<br /><br />CanadaDBA
  19. joechang New Member

    do not worry about vm use,
    what you do not want is excessive page file use,
    page file != vm

    vm max size should phy mem + page file size

    look in your C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG
    or where you put the MSSQL directory
    for the ERRORLOG file
    open it with notepad

    look for an entry concerning AWE up top
  20. CanadaDBA New Member

    Joechang, it says "Address Windowing Extensions is enabled."<br /><br />I rebooted my server at 10 am and here is the information at 4:30 pm:<br /><pre id="code"><font face="courier" size="2" id="code"><br />Physical Memory:<br />Total RAM: 16 GB <br /> Free: 10.2 GB <br /> Free %: 64%<br /><br />Virtual Memory: <br />Max Size: 18.0 GB <br /> Free: 12.2 GB <br /> Free %: 68%<br /></font id="code"></pre id="code"><br />You are right. It shows the max size of VM is 18 GB while I haven't set my Windows paging files to be 18 GB. My mistake was that I thought this VM is the Windows paging size! <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />My server is now utilizing the RAM and thanks to everyone who respond and contributed with this post.<br /><br />CanadaDBA
  21. joechang New Member

    what do you have max server memory set to?
    6140MB ?
    is that what you want?
    for 16GB, 15360 is probably good
  22. CanadaDBA New Member

    Here is the information from Server Properties window:

    Server memory options
    Minimum server memory (in MB): 37
    Maximum server memory (in MB): 2147483647

    Other memory options
    Index creation memory (in KB, 0 = dynamic memory): 0
    Minimum memory per query (in KB): 1024

    These number were there when I started this job. With knowledge of my environment, do you think I should change them?


    quote:Originally posted by joechang

    what do you have max server memory set to?
    6140MB ?
    is that what you want?
    for 16GB, 15360 is probably good

    CanadaDBA
  23. CanadaDBA New Member

    My server at 9:30 am, a day after reboot:


    Physical Memory:
    Total RAM: 16 GB
    Free: 1.55 GB
    Free %: 10%

    Virtual Memory:
    Max Size: 18.0 GB
    Free: 3.53 GB
    Free %: 20%


    Is it good or something is wrong? Referring to my previous post, should I modify any of those numbers?

    CanadaDBA
  24. joechang New Member

    lets see

    you bought 16GB of memory for your system
    you are using 14.5 tot, probably 14G for SQL

    this is a good place to start
    with careful observation, potentially you could bump up amount until
    free RAM drops to 500MB
    but this should be only over time

    did you want to use the memory for something else?
    some adminds want as much free memory as possible
    so basically its just there to keep the room warm
  25. CanadaDBA New Member

    SQLServer is the only program on the server.

    Do you mean it is ok to use the RAM upto 15.5GB?

    You didn't mentioned about the following that I sent in previous post:

    Here is the information from Server Properties window:

    Server memory options
    Minimum server memory (in MB): 37
    Maximum server memory (in MB): 2147483647

    Other memory options
    Index creation memory (in KB, 0 = dynamic memory): 0
    Minimum memory per query (in KB): 1024


    CanadaDBA
  26. MohammedU New Member

  27. joechang New Member

    the closer you want to get to nearly zero free memory
    the more skill it takes to avoid crippling your system in transient operations
    i suggest leave alone for now, until you learn more about how the system behaves

    if you consider that the 1.5G not being used costs about $300,
    how much pain are you willing to risk to get at it?
  28. CanadaDBA New Member

    I remember in this line Maximum server memory (in MB): 2147483647 it was (in KB) than MB. I think after I rebooted the server and with the AWE setting and with /PAE switch it was changed to MB.

    Should I change the 2147483647 number to 14.5GB x 1024 = 14848MB?


    CanadaDBA
  29. joechang New Member

    2147483647 is a default setting
    leave it alone

    some one back at the Microsoft campus was smart enough to figure out how much memory SQL should use
    if you are not smarter than he/she is, leave it alone
  30. MohammedU New Member

    What do you think there are smart enough? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Read BOL for completed details...<br /><br />SQL Server 2005 Books Online <br />Enabling AWE Memory for SQL Server <br /<a target="_blank" href=http://msdn2.microsoft.com/en-us/library/ms190673.aspx>http://msdn2.microsoft.com/en-us/library/ms190673.aspx</a><br /><br />MohammedU.<br />Moderator<br />SQL-Server-Performance.com

Share This Page