SQL2K Ent - AWE Memory Usage
-
Hi Guys,
We are using a server that has 8GB of RAM, and as it's solely a SQL box I'm trying to get SQL to utilise 6GB RAM. Am I in a 32 bit nightmare?
The details are as follows:
- 4 AMD Opteron 852's, 8GB RAM
- Windows Server 2003, Enterpise, Service Pack 1.
- SQL Server 2000 Enterprise, Service Pack 4
- /PAE switch in use
- AWE is enabled in SQL Server
- Max Server Memory is set to 6144 MB
- SQL Service account is in "Lock pages in memory" in local security policy
Am I missing something? SQL is still only utilising 3.8GB of RAM. I have checked the Buffer Manager\Target pages and Total pages perfmon counters and also the Memory Manager\Total Server Memory counter and they are all just under 4GB.
There is still well over 3GB available RAM on the system, is this Server 2003 keeping the 3-4GB available, or is it something to do with the Opteron's support of PAE, or have I just missed something obvious and need shooting?
Please Help!
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font] -
On a course at the moment but I do recall something about Opteron and memory a while back when we upgraded our servers.
Can't recall off the top of my head, but I'll go through my mails as soon as I can and post back, if someone doesn't beat me to it...
-
Did you install the fix in the following article?
-
If you are looking at SQL memory use with Windows Task Manager, remember that Task Manager cannot report on any use above the 4GB line. SQL may be using 6GB but Task Manager will never show this.
You need to check in the SQL errorlog. If you set SQL memory to 6GB, and SQL reports it is using AWE memory on startup, then you have got the 6GB.
You should also consider adding the /3GB switch to boot.ini, as this will release an extra 1 GB for use by SQL or other applications. If you ever get to 16 GB on your box, then /3GB must be removed or Windows will run out of internal memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
The last post is probably correct. You cannot see AWE memory use through any of the normal memory indicators. The best way to see if it is being used is to check the AWE buffers for SQL server.
Go to performance monitor and add the AWE lookup maps/sec counter that is under the SQL Server Buffer Manager. If you are getting activity, AWE is being used. If you are not, then you have a configuration problem.
There are a few other counters in there that can tell you how much AWE memory is being used and if you have enough total memory, but I cannot type an entire book in here. If you are not getting a message in the SQL error logs indicating that it cannot allocate the minimum memory specified and you are getting AWE activity in the lookup map counter, you are probably ok.
-
Thanks for all the replies.
It is definitely using AWE. Prior to enabling AWE, SQL was only using 1.7GB, after enabling it is using just under 4GB.
I know that Task Manager won't show the amount of AWE memory being used, in fact it only shows SQL as using 90MB.
SQL is definitely only using 4GB at the moment, not only do the Buffer Manager perfmon counters say it is, but I have just over 3.5GB free RAM still on the server.
I've just read the KB article that Xanthos pointed to and it looks like it may be the answer. I haven't installed it yet, I'll need to do that out of hours just in case, but I'll post back and let you guys know!
Rob
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font] -
Thanks for all your help, biggest cookie goes to Xanthos for suggesting the hot fix.
The issue was the bug in SQL 2000 SP4 on 32 systems, and applying the hot fix has fixed it. I'm sure you'll all be glad to know that SQL is now using 6GB of RAM on that server and the performance difference is noticeable.
Thanks again,
Rob.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply