MSSQL : memory config and allocation (multiple instances)

[simas]

Good morning,
I am setting failover cluster (active-active, w2k SP4 as OS and SQL Server 2000 SP3 for RDMS) and had some thoughts/questions about memory configuration. Each of the cluster nodes has 4 Gb of RAM, my databases are 250-300 Gb Gb in size.

My goals
a) maximize performance of each of the nodes under normal, non-failover conditions.
b) make certain that in case of failover , second node is able to start.
I am willing to accept possibility of performance degradation as a trade-off for faster performance under normal scenario as long as the risks and issues involved are known and understood.

My options (that I see so far).
1) Enable /3 Gb switch on each node and do not set max server memory. This would give OS 1 Gb of RAM and leave 3 Gb to the SQL Server on each node. So each node can use 75% of total RAM.
Issues with this scenario - I am not sure how memory would be allocated/reallocated during the failover, how much would the second node receive and would it be able to start

2) Enable /AWE switch on each node and set max server memory setting at 3,5 Gb leaving 0.5 GB of RAM for the OS. It would allow >85% of total memory usage (and hopefully faster system) but I am not sure how it would fare in the failover.

3) Enable /3 Gb switch on each node and set max server memory at 1.5 Gb. So if failover does happen, each node would receive 1.5 Gb of RAM, 1 GB for the OS and everything would function. However, under normal conditions each node only uses 1.5 out of 4 Gb available - less than 40%.

4) Enable /AWE on each node and set max server memory to 1.75 - higher RAM utilization, not sure how it would perform in failover.

Right now I am running with /3GB switch and no manual max server memory value (meaning that SQL Server gets 3 Gb on each node).
I was told that SQL Server does have the ability to negotiate the use of RAM, if the other application also understands the same negotiating protocol. What I am interested in is what happens when second node fails and first node receives second virtual server to run. 3 Gb of memory is already allocated for first instance, how much would second istance get? how fast? Will first instance share? how will it share? I've heard that first instance will give up 'some memory' 'over time' but no specifics on how SQL Server engine handles it.

Any thoughts, advice, suggestions, choices made in similar situations,etc?

Thank you
Simeon

[tengiz]

1) In general, you should not use /3GB unless absolutely necessary, that is if you really experience virtual address space problems in SQL Server - you can see it in the errorlog when it happens.

2) In SQL Server 2000, AWE memory does not get the same treatment as normal memory with respect to this be-a-good-os-citizen thingy, therefore, in the case of A/A cluster you're going to have a problem with the memory configuation you describe. So, you should set manual server memory limits in order to prevent memory starvation.

[simas]

Thank you Tengiz,
Both nodes will be configured with max server memory option.
Any place (in books on in the MS documentation) to find out how sql sever manages 'being a good os citizen thingy' (i.e. after new instance of sql server came up and it requesting the memory)? Who decides how the memory is allocated (first process, second process, OS, etc) and how are priorities determined (first come, first served, etc)?

Simeon

[simas]

http://msdn.microsoft.com/library/defau ... a_0fub.asp

Found this in MSDN, answers my questions, Thanks to anyone who've helped!