SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Windows 2003 Enterprise Sp1/MSSQL 2000 Enterprise 8.0.2.39 Not able to Increase Target Server Memory...


Windows 2003 Enterprise Sp1/MSSQL 2000 Enterprise 8.0.2.39 Not able to Increase Target Server Memory

Author
Message
Julie-66576
Julie-66576
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 122
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



Julie-66576
Julie-66576
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 122
Apparently, there is a hotfix for sp4 related to awe memory.



MANU-J.
MANU-J.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2912 Visits: 8766
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
SQLPhil
SQLPhil
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1572 Visits: 740
Have you got PAE (physical address extension) enabled in the boot.ini file? I'm assuming this is a 32-bit server.
EdVassie
EdVassie
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5777 Visits: 3865
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 14 Mar 2017: now over 40,000 downloads.Disclaimer: All information provided is a personal opinion that may not match reality.Quote: 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
Julie-66576
Julie-66576
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 122
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search