SQL Server Performance

SQL Server 2005 not using all memory

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by matto, Jul 23, 2007.

  1. matto New Member

    I am using SQL Server 2005 sp2 EE on Windows server 2003 SP1. I have 32GB RAM, PAE AWE enabled. sp_configure shows max server memory of 30GB. But my diagnostic tool (Spotlight) shows 26.3GB in use and never goes higher. Any ideas why SQL won't use the remaining 3GB memory?

    Thanks
  2. MohammedU New Member

    I think sql server 2005 memory allocation works different than sql 2000. In sql 2005 it takes its memory as needed upto configure max server memory where as in 2000 it takes all the memory at startup.

    Run the perfmon and check TOTAL SERVER MEMORY and TARGET SERVER MEMORY counter to see how much available and how much sql is using...


    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

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

    Both counters are at 26.3GB. Does that mean it's using all it needs? Our database is big enough that it should go up to 30GB. It's strange - we have 6 identical servers and they all stop at 26.3GB

    Thanks for your reply.
  4. MohammedU New Member

    Did you enable "lock in memory pages"

    MohammedU.
    Microsoft SQL Server MVP
    Moderator
    SQL-Server-Performance.com

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

    Check whether SQL Server Service account has the "lock pages in memory" OS privilege?

    In your SQL Server log, near the beginning, do you see an entry that says
    "Using locked pages for buffer pool"?


    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.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.
  6. matto New Member

    I see in the Group Policy Object editor that the account running SQL is assigned to Lock Pages in Memory.

    I haven't been able to find "Using locked pages for buffer pool" in the SQL log however.
  7. satya Moderator

  8. satya Moderator

    ... just another though what other counters are you montiroing using SYSMON

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    @http://www.askasqlguru.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.

Share This Page