SQL Server Performance Forum – Threads Archive

SQL Server Memory Allocation

Hi, This is my first post to your forum…so take it easy on me! I have a couple of SQL Server 2000 Enterprise Edition boxes (active-active cluster) with 8 GB of RAM a piece running Windows Server 20003. I’ve ensured that the /PAE switch is in c:oot.ini. The OS sees the full 8 GB or RAM on each box. But, I can’t seem to get SQL Server to consume more than 1.7 GB of RAM. By the way, we just implemented the cluster. I’m testing before migrating several databases to the cluster. Here’s how I tried to force SQL Server to consume more and more RAM (again, to see the instance consume up to its max server memory setting of 3.5 GB.) Since our two nodes fail over to each other, we limit SQL Server to 3.5 GB on each box. I created a table with a text field and pinned the table to memory with dbcc pintable. I watched the sqlservr.exe process in Task Manager allocate more and more memory as I inserted more and more data into my pinned table. But, when I got to 1.7 GB of mem usage for the sqlservr.exe process, it just stop growing and would not go beyond 1.7 GB. Is there a limit to how much data you can pin to memory? I wouldn’t think this would be the case. I’ve ensured that the sp_confige run value for ‘awe enabled’ is set to 1. My question is this: why can’t SQL Server use over 1.7 GB of RAM on these boxes? Any ideas? I’d appreciate any help from any of you pros out there. I’m in my 4th year as a SQL Server DBA. Thanks, Tom

Did you run Performance Monitor, SQL Server Memory Manager–> SQL total memory, SQL Target Memory? I would like to know what those counters show.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, Perfmon shows 1,676,912 for both Total and Target Memory for max, min, avg, etc. So, yes, it appears SQL Server is only consuming 1.6 GB rather than 1.7 GB. Tom

Ok. SQL try to reach all available memory when they need.
May be there is low activity and SQL donÂ´t need more memory than that. How about pages/sec? is near 0 all the time?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, We figured it out. We had to have the /3GB boot switch in the boot.ini in addition to the /PAE switch. (since we are not planning on having over 16 GB of RAM…see KB article below) With the sp_configure ‘awe enabled’ run value = 1, SQL Server will try to allocate the full amount of RAM specified in the ‘max server memory’ setting upon startup. After we added the /3GB switch to boot.ini and rebooted the server, SQL Server immediately came up consuming the amount we specified in max server memory (which on our test box was set to 2.5 GB. http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech#6 Thanks for your help, Tom

]]>