Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Windows 2003 Enterprise Sp1/MSSQL 2000 Enterprise 8.0.2.39 Not able to Increase Target Server Memory Expand / Collapse
Author
Message
Posted Monday, July 6, 2009 4:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 19, 2010 5:09 PM
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



Post #748164
Posted Tuesday, July 7, 2009 6:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 19, 2010 5:09 PM
Points: 16, Visits: 122
Apparently, there is a hotfix for sp4 related to awe memory.


Post #748960
Posted Thursday, July 9, 2009 2:04 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Post #750615
Posted Friday, July 10, 2009 1:44 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:14 AM
Points: 1,442, Visits: 728
Have you got PAE (physical address extension) enabled in the boot.ini file? I'm assuming this is a 32-bit server.
Post #750863
Posted Friday, July 10, 2009 6:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 8:26 AM
Points: 2,894, Visits: 3,278
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 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #750981
Posted Tuesday, July 14, 2009 11:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 19, 2010 5:09 PM
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



Post #752895
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse