max server memory

Question

Post reply

max server memory

amira

SSChasing Mays

Points: 628
More actions
February 21, 2007 at 8:17 am

#153445

Hi, I have a Win2K3/SQL2000 running on a cluster with 3 Active nodes and each with node has 3 instances of SQL.
Our DBAs asked us to apply the patch: SQL2000-KB899761-v8.00.2040-x86x64-ENU.exe (not all memory available when AWE......). When we do that, on one of the nodes, one instance gets stock in "Starting Cluster Services...." and it never goes any where. I was wondering if anyone can tell me:
1- How can I check and see if the patch was actually applied correctly to other nodes and instances.
2- What am I supposed to see under Maximum and Minimum when running "sp_configure" (I see 0 for Min and 1 for Max right now)
Any other ideas/comments.

Thanks
A.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sumit-314810 SSC-Addicted Points: 402 More actions · Answer 1

Check the Following Article

AWE-enabled SQL Server 2000 may take a long time to start

http://support.microsoft.com/kb/329914/en-us

Use the workaround

{

On a Windows 2000 cluster, if the time that the operating system (OS) takes to zero the memory is longer than the Pending Timeout period permitted for the SQL Server resource group to come online in the cluster, the SQL Server resource cannot come online.

}

So just increase the "Pending Timeout" from default value of 180 secs.

Also to check if AWE is enabled. Check for Total Server Memory/ Target Server Memory Counters on Perfmon for Memory Manager on SQL Server.

They represent SQL memory utlilization in bytes.

If the patch is applied version would be 2040 and also check the binaries listed under binn directory of both the nodes

Set max and min server to default until and unless this is not a dedicated SQL Server. If this is not a dedicated SQL Server adjust max server memory to 80% of the Physical Memory.

amira SSChasing Mays Points: 628 More actions · Answer 2

That makes sense! what doesn't make sense to me is that our DBAs asking for this - and here is why, see if you agree:

We have 3 instances on each node, if we pre-define Max Server Memory (SP_CONFIGURE 'max server memory', *****), we would give each node an equal amount of 1/3 of the physical memory, and that is less than the half it could manage to get dynamicaly, if the resources were left available.

If we don't pre-set the Max Server memory, well, I am not sure what happens the day we need to re-start the server, would each instance compete for allocating the entire physical memory to itself, causing potential problems?!

MohammedU SSCoach Points: 19644 More actions · Answer 3

There are many bugs in SP4... they have been fixed in cumulative patch...or atleast you have to apply hotfix related awe issue...

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

http://msdn2.microsoft.com/en-us/library/ms162820.aspx

Cumulative Hotfix for SQL Server 2000 Service Pack 4 - Build 2187 for x86 and x64 Based Systems

http://www.microsoft.com/downloads/details.aspx?FamilyID=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en

If you have multi instance with larg memory and awe enabled servers...it is advisable to use Max Server Memory instead of leaving to handled by sql.

MohammedU
Microsoft SQL Server MVP

MohammedU SSCoach Points: 19644 More actions · Answer 4

There are many bugs in SP4... they have been fixed in cumulative patch...or atleast you have to apply hotfix related awe issue...

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

http://msdn2.microsoft.com/en-us/library/ms162820.aspx

Cumulative Hotfix for SQL Server 2000 Service Pack 4 - Build 2187 for x86 and x64 Based Systems

http://www.microsoft.com/downloads/details.aspx?FamilyID=9C9AB140-BDEE-44DF-B7A3-E6849297754A&displaylang=en

If you have multi instance with larg memory and awe enabled servers...it is advisable to use Max Server Memory instead of leaving to handled by sql.

MohammedU
Microsoft SQL Server MVP

humbleDBA Hall of Fame Points: 3540 More actions · Answer 5

Actually, in SQL2000, if you don't set max memory to the same value of what you want the instance to take, when using AWE, my understanding is that SQL Server will take all but 128 MB. Also, min memory value is ignored, I believe, but good practice to set same. SQL2005 I think is different in that it does use min memory value, so you can make it more dynamic in its actions.

Your DBA is perfectly right in saying that you should set max memory for each instance so that the sum of the max memory for all instances is slightly less than the physical memory that resides on that server (whether its a 1/3 for each is a decision for you DBA/Business of course) You then have the issue of ensuring that the memory available on the failover passive node is set similar, AND also takes into account all 3 nodes, so that if in the unlikely event that you had multiple failovers to the passive node there would be adequate memory for the instances from each node.

HTH

Rgds iwg

amira SSChasing Mays Points: 628 More actions · Answer 6

Point taken and the issue is resolved (it was getting stock on Full Text Search resource), since the DBA were not using that functionality, we removed it and everything worked.

By the way, it was not our DBAs who suggested setting Max value, it was the system team - To that end, I am still not clear where the responsibility border, so we just drag on

humbleDBA Hall of Fame Points: 3540 More actions · Answer 7

Ahhh, sorry amira, misread the bit where you mention '...DBA asking for this...'

Alway a tetchy subject on who has control on what area of the systems setup - and I don't suppose it will change anytime soon.

Glad you go it all sorted.

Rgds iwg