SQL Server Performance

AWE is not enabled

Discussion in 'General DBA Questions' started by sahil, Mar 30, 2007.

  1. sahil New Member

    Hi All,

    We have 16 GB of memory on our server. We have allocated 12 GB to SQL server and left 4 for OS. sp_configure shows run value of 12288 but task manager shows 4.55 GB is being utilized. Even server property shows max values 12288 MB. No min since SQL will use dynamically.

    Below is current boot.ini setting.
    [boot loader]
    timeout=0
    default=multi(0)disk(0)rdisk(0)partition(2)WINDOWS
    [operating systems]
    multi(0)disk(0)rdisk(0)partition(2)WINDOWS="Windows Server 2003 Enterprise x64 Edition" /noexecute=optout /fastdetect

    SQL 2000 Enterprise Ed
    SP4 (8.00.2191)

    How do i find if AWE is confgiured properly.
    Why task manager is not showing usage of 12 GB.
    How do i find how much memory is being used by SQL.

    Thanks All.

    -Sahil
  2. Luis Martin Moderator

    "Why task manager is not showing usage of 12 GB."

    Because task manager is a old NT tool. Don't use it to find those counters.
    Run performance monitor with SQL Server Target Memory and SQL Server Memory to find out real counters.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. sahil New Member

    Thanks Luis.

    This is the server on which i am missing SQL performance counters. That issue is not reslove yet, therefore i can not run mentioned below counters.

    Any other thoughts.

    thanks for your help.


    quote:Originally posted by LuisMartin

    "Why task manager is not showing usage of 12 GB."

    Because task manager is a old NT tool. Don't use it to find those counters.
    Run performance monitor with SQL Server Target Memory and SQL Server Memory to find out real counters.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.




  4. MohammedU New Member

    How do i find if AWE is confgiured properly.
    Check "Lock in page memory" configured, Max Server momory setup and check sql server memory usage through perfmon not task manager...

    Why task manager is not showing usage of 12 GB.
    Task Manager will not give you correct info...

    How do i find how much memory is being used by SQL.
    Total Server Memory and Target Server memory counter in perfmon...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. Luis Martin Moderator

    I forget about sql counters.

    Run Monitor Profiler, event:Memory (is windows, not SQL) and check:Available MB,Pages/sec,

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  6. Luis Martin Moderator

    quote:Originally posted by LuisMartin

    I forget about sql counters.

    Run Monitor Profiler, event:Memory (is windows, not SQL) and check:Available MB,Pages/sec.
    Post results.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided �AS IS� with no warranties for accuracy.





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  7. MohammedU New Member

    I believe Luis is reffering to Performance Monitor(Sysmon) not profiler...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. sahil New Member

    Thanks Mohammad and Luis.
    I have found error message below in SQL Error log:
    Cannot use Address Windowing Extensions because lock memory privilege was not granted.
    I have found that SQL Service account doesn't have lock memory privilege on the server since we recenlty changed the service account. I have requested NT team to add it, i hope it will resolve the issue.

    In performance monitor Memory: Available MBytes: 10843
    Available KBytes: 11104192.

    Thanks for your help!
    -S

    quote:Originally posted by MohammedU

    I believe Luis is reffering to Performance Monitor(Sysmon) not profiler...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  9. MohammedU New Member

    Yes, once you guys configured the lock in pages.... make usure max server memory is configured properly and AWE is enabled....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  10. sahil New Member

    We are going to allocate max 12 GB to SQL and leave 4 for OS.
    Shoud we configure min 4 or 6 GB as well or it doesn't matter when we have configured it dynamically. What is the recommended setting for min memory? Please advise.

    Thanks for your help Mohammad!

    Sahil






    quote:Originally posted by MohammedU

    Yes, once you guys configured the lock in pages.... make usure max server memory is configured properly and AWE is enabled....


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  11. MohammedU New Member

    When AWE is enabled memory is not dynamic any more in SQL 2000...

    If it is dedicated sql server you don't need to leave 4GB to OS...2GB should be fine for OS...

    Max memory to sql can configured: 14GB.
    Min memory you can configure to 1GB...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. sahil New Member

    Yes this is dedicated SQL server. I will do the same as you suggested.

    Thanks for GREAT support!


    quote:Originally posted by MohammedU

    When AWE is enabled memory is not dynamic any more in SQL 2000...

    If it is dedicated sql server you don't need to leave 4GB to OS...2GB should be fine for OS...

    Max memory to sql can configured: 14GB.
    Min memory you can configure to 1GB...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  13. satya Moderator

    What is the PF (page file) usage on the server?

    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. sahil New Member

    Total page file memory is 16 GB and 12.9 is free.
    since we have added the service accoutn to lock page in memory, awe is enabled
    i have allocated max 12 GB to sql and min 4 Gb.

    Thanks All.





    quote:Originally posted by satya

    What is the PF (page file) usage on the server?

    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.
  15. MohammedU New Member

    Is your dedicated sql server paging when max server memory set to 12gb?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  16. satya Moderator

    Where is that PF stored?

    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. sahil New Member

    Yes Mohammed this is dedicated sql server.

    Thanks
    Sahil


    quote:Originally posted by MohammedU

    Is your dedicated sql server paging when max server memory set to 12gb?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  18. sahil New Member

    It's in C drive.

    Thanks Satya.


    quote:Originally posted by satya

    Where is that PF stored?

    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.
  19. satya Moderator

    On the second thought for the error "Cannot use Address Windowing Extensions because lock memory privilege was not granted" ensure the SQL Server services account has relevant privileges on the machine. When you say it is a local admin that user still requires the permissions under the DOMAIN policies, not the local policies.

    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.
  20. sahil New Member

    Thanks Satya!

    We are using this server account on few other servers and i think it has enough permissions. Since we have added it in lock page.. awe is enabled.




    quote:Originally posted by satya

    On the second thought for the error "Cannot use Address Windowing Extensions because lock memory privilege was not granted" ensure the SQL Server services account has relevant privileges on the machine. When you say it is a local admin that user still requires the permissions under the DOMAIN policies, not the local policies.

    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.
  21. MohammedU New Member

    Is your server still paging?
    Check memory used by sql server using and Page file usage through perfmon...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page