SQL Server 2005 not using all memory | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server 2005 not using all memory

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
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.
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.
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.
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.
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.
Seehttp://msdn2.microsoft.com/en-us/library/ms190730.aspx and follow whether it exists or not. 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.
… 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.
]]>