July 6, 2009 at 4:26 pm
Hi,
I have a sql 2000 cluster server environment on windows 2003 Enterprise sp1 with 8GB of memory on each machine. The sql instance is patched with sql 2000 sp4 and has AWE enabled. With or without /3GB in the boot.ini file, I could only get 4036568 KB of memory for Target sever Memory from perf mon result, even if I set min and max server memory to be above 5 GB. The total server memory is also at 4036568 KB.
Am I missing anything here? Why wouldn't the server honor the max memory I set up? I know the AWE is working, as without that, the sql server will only use about 2 GB memory. The operating system and enterprise manager both shows 8 GB of memory available.
Please help if you have any suggestions!
Thanks.
Julie
July 7, 2009 at 6:57 pm
Apparently, there is a hotfix for sp4 related to awe memory.
July 9, 2009 at 2:04 pm
http://support.microsoft.com/kb/899761
FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4
MJ
July 10, 2009 at 1:44 am
Have you got PAE (physical address extension) enabled in the boot.ini file? I'm assuming this is a 32-bit server.
July 10, 2009 at 6:01 am
On a 32-bit server the Perfmon TargetServerMemory results and Task Manager will never report more than 4GB memory in use.
In order to see if you are using more than 4GB memory you need to:
a) Look in the SQL Error Log for when SQL is started - you shold see a message that AWE memory has been allocated.
b) Look in the Perfmon SQL AWE counters for details of memory use above the 4GB line.
32-bit Windows is not able to directly memory above the 4GB line. The PAE features in Windows allow it to divide the memory above 4GB into 4K pages and give each page a slot number. Programs that are PAE-aware (such as SQL Server database services) can allocate these slots. When a PAE-aware program wants to read data in one of the slots, Windows copies it to a location below the 4GB line and the program can access it. If the page gets updated then Windows has to copy it back to its location above the 4GB line. If it has only been read then it can be discarded if a program requests to read a different slot.
Even though this is a slow way of using memory, the ability to cache daat read from disk into memory above the 4GB line allows programs to get better performance than always reading everything from disk.
In order to get fast and direct read and write access to memory above the 4GB line you need to run 64-bit Windows and 64-bit SQL Server.
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
July 14, 2009 at 11:30 am
Hi,
Thank you all for the replies. I applied http://support.microsoft.com/kb/899761 hot fix to one of the active cluster instance and now am able to see more than 4GB for target server memory and total server memory in perf mon when I extend the max memory to over 4 GB. So the hotfix applears to be working!
However, when I tried to install the same hotfix on another instance on the same cluster, the hotfix couldn't detect the active instance on that machine. The active instance didn't show up in the list for me to check and install. I looked at the hotfix installation log... it didn't report finding the active instance at all. I double checked the instance, it is running fine on that machine and it is patched to 2039 with sp4 as required by this hotfix.
What could be wrong with this instance? How does the hotfix pick up the active instance information? By looking in the registry?Ps.the instance itself has been running fine and is one of our main production server.
Thanks.
Julie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply