Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696

    Unanswered: AWE on SQL2005x64 Cluster

    I currently have a 3 node(A/A/P) cluster running SQL2005x64 each with 64GB RAM running 1 instance of SQL on each Active Node.

    DBCC MemoryStatus shows
    VM Reserved 1589060
    VM Committed 63736
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    Which clearly shows 64GB is not being used.

    I tried to Enable AWE, but got message
    Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

    The service account that starts SQL Server has been granted this privilege via the steps in "How to: Enable the Lock Pages in Memory Option (Windows)"

    http://msdn.microsoft.com/en-us/library/ms190731.aspx

    /PAE parameter exists in boot.ini.

    I'm stumped.

    Thanks.

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Dude. AWE is so yesterday (32 bit). You don't need AWE on a 64 bit server.

    By default your server will use the memory it needs until there is no more memory available. Let your server run in production for a while and the memory usage will go up. When you restart SQL Server, your data cache is dumped and the memory usage starts over. I'm guessing one of the following:
    1. You ran DBCC MemoryStatus shortly after starting SQL Server
    2. You ran DBCC MemoryStatus on a database that is not in production
    3. Your database isn't very large.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    AWE is a BAD IDEA for an x64 server. I can't think of any case where it might help 64 bit SQL to enable AWE.

    The DBCC output that you posted just shows that your server isn't doing much (doesn't need memory), which is as it should be.

    -PatP

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Thanks guys, kinda learning 2005 as I go along.

    cascred, it's brand new server, I was comparing DBCC MemoryStatus output to a server that has a 300GB DWH that has been in production for 5-6 months.

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Do you recommend setting min/max memory settings to sql server, or let it handle it "out of the box" ?

    Thanks

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would only change the Min/Max Memory settings on an x64 machine if it's memory usage started to give me trouble.

    -PatP

  7. #7
    Join Date
    Sep 2005
    Posts
    161
    Quote Originally Posted by PMASchmed
    Do you recommend setting min/max memory settings to sql server, or let it handle it "out of the box" ?
    Thanks
    I like to set the max memory at 1Gb below the physical memory available. This will leave a little breathing room for the OS and misc. services. But I don't think you'll shoot yourself in the foot if you let the server handle it.

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by cascred
    I like to set the max memory at 1Gb below the physical memory available. This will leave a little breathing room for the OS and misc. services. But I don't think you'll shoot yourself in the foot if you let the server handle it.

    Many thanks for your input, have a good weekend.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Neither PAE nor AWE are needed for 64-bit architecture. In A/A/P environment with 64GB of RAM I would put a max memory at no more than 58GB. I see posts relating to min/max memory, but I don't see any reference to what kind of memory everybody is trying to limit. min/max are related to process memory, which is procedure cache, plus data buffer. Up till now MS has not come up with a controlling mechanism to guage the data cache independently from procedure cache, so there is no "real" tool to scientifically approach this issue.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by rdjabarov
    Neither PAE nor AWE are needed for 64-bit architecture. In A/A/P environment with 64GB of RAM I would put a max memory at no more than 58GB. I see posts relating to min/max memory, but I don't see any reference to what kind of memory everybody is trying to limit. min/max are related to process memory, which is procedure cache, plus data buffer. Up till now MS has not come up with a controlling mechanism to guage the data cache independently from procedure cache, so there is no "real" tool to scientifically approach this issue.

    My concern is if that both instances fail over to one node, would the 58GB per instance cause a problem although the node has 64GB of RAM ?

    From the Sybase days we always went with the 80/20 rule for data cache vs. procedure cache.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When both instances end up on your P server, the instances will not try to take all 58GB each. The max limit literally limits the ability of the instance to use more than specified amount of memory. But since we're not setting the min memory, then the instances will try to take only as much as they really need (assumption is that you're working in 2k5 environment).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Quote Originally Posted by rdjabarov
    Up till now MS has not come up with a controlling mechanism to guage the data cache independently from procedure cache, so there is no "real" tool to scientifically approach this issue.
    SQL 2008 seems to bring some relief!
    http://sqlblog.com/blogs/adam_machan...ure-cache.aspx
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It doesn't seem, - it may, but there is no guarantee
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •