2000 Enterprise does not utilize max memory with AWE enabled
-
I'm running a very large active database and have AWE enabled with 5GB max memory set. Orignally, I discovered that AWE wasn't enabled and I was only utilized the 2GB limit. After configuring AWE, I set the max memory limit to 4GB. I immediantly saw results where SQL Server was using the available memory and the Buffer Cache took everything it could grab, after seeing those results, I set the max server memory up to 5GB, hoping to see the Buffer Cache taking advantage of the additional memory. However, after reconfiguring the max memory and restarting the SQL service, it doesn't appear to be taking advantage of the additional 1GB. My Max Total Memory counter still shows utilization at 4GB, despite sp_configure showing 5GB is set as the max value. Additionally, utilizing tools such as SQL on Spotlight, doesn't show SQL Server utilizing the 5GB limit, with the buffer cache stopping just below 4GB as before.
I know for sure that my database can take advantage of all the memory in the world, so I'm confused as why it's not taking it. The only thing that has me wondering, is that my Windows paging file is also set at 4GB... Is there possibly a physical ram to VM ratio that SQL Server is expecting in order to utilize a set physical ram amount?
I'm loosing sleep over this problem, any suggested would be great, thank!
-
- what's your server's ram total ?
- The OS also need ram to manage AWE
- keep in mind that task-manager doesn't show correct figures for awe.
User perfmon to find out memory usage !
- which boot.ini parameters are you using (/3Gb /pae ??)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It sounds like you are missing the boot.ini parameters that ALZDBA has mentioned. Also, as ALZDBA has said, you want to leave memory for the OS. I recommend 1 GB for servers up to 16 GB total RAM and 2 GB for servers with 16 GB and up. Also, make sure if you are running SQL Server using a domain account, that that account has been granted the lock pages in memory privilege.
-
I have 8GB total, with currently 3GB free, which was the same amount free when I initially configured 4GB max server memory. I'm looking at the Total Server Memory counter under the SQL Server Memory Manager perf group, which is indicating only 4GB is being used.
I have the /pae switch under my boot.ini, but do not have the /3GB loaded. I'm a bit wary using the 3GB, due to hearing mixed results of problems that it may cause. I know the /PAE switch is required in order to utilize over 4GB (I'm running Win2k3 32bit Enterprise), but would the /3GB switch play an actual roll in the amount of physical ram SQL Server will take? Or just the amount of VM?
-
Yes, I've made sure that the domain account launching SQL Server has locked pages into memory enabled...
-
I've read of various negative side effects of having the /3GB switch, such as unexpected errors caused when the OS needed to access above the 1GB mark. I can't afford any sort of instability whatsoever, so I want to avoid using this switch if possible. Also, I plan on upgrading my server to 32GB, therefore the /3GB would have to be removed anyway.
-
here's a good article about awe.
www.sql-server-performance.com/awe_memory.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
If you are running SQL 2000 SP4 I believe there is a hotfix (KB899761) which fixes an issue where SQL only utilizes half of the physical memory installed in the box. I am having a similar issue (8GB total RAM, SQL only using 4GB even though I have set it to use 6.5GB max)and am planning on installing this hotfix this weekend. Hope this helps!
-
Set /PAE in boot.ini to take advantage of RAM and check KB Microsoft for potential problem as described by Chris.
-
This is probably my problem!!! The hotfix explains exactly my problem! Thanks man! Saved my life!
-
Glad I could help....
-
Your page file is also too small. Microsoft recommends the page file be 1.5 to 2 times the size of the physical memory. With 8GB in your machine, your page file should be 12-16GB.
-
Bear in mind that SQL Server will not consume 100% of the max memory you set if it does not need it. So it's not all that unusual that it is using less than the available RAM. If it only needs 4 GB of RAM, it will only use 4GB of RAM even if you give it 7 GB.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
The hotfix was the problem, after installing it, SQL started utilizing the 5GB for the buffer cache. I am aware that my paging file is set a bit low... I'm working on a place I can put that.
I do have another question, which I might open a new thread for. But does it make any sense to be running a 32-bit shop on a database that's about 70GB in size? As well as experiencing I/O contention as being the biggest bottleneck... I can already tell that by enabling AWE and increasing the max server memory, that my disk que length counters have dropped significantly... Thought I don't think AWE alone will help me in the long run.
Thanks everyone for all the advice!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply