I have read it about a thousand times in Books on line but never really understood why, or even really seen the impacts of the Memory management in a Cluster with SQL Server. Books on line well at least SQL Server 2000 says that if you use AWE Memory then you should not let SQL Server Dynamically manage your memory. I understand how the Min memory setting works and I have a good understanding of the Max memory setting. However, I have seen clusters configured to both manage the memory and to have a defined min and max. I have never seen the difference and impact until this week, or at least I believe this could be part of the problem.
I was working on a cluster this week and the max memory was set at that huge 64 gig (I think) default. The server was a SQL Server 2005 cluster. Memory was being used to the max and there was a lot of paging occurring. The page file was set to 6 Gigs, 1.5 times the memory in the server. The interesting thing that I found was once the server started to hit that full 6 gig’s worth of Page file usage the server would fail over. After some discussion with the hosting companies SQL Server team I noticed the 64 Gig max memory setting. I talked to a MVP friend of mine and it was clear that this configuration could be what was causing the failovers. SQL Server thought it has more memory but the OS was not having any of that.
This weekend the server was upgraded to 8 Gigs of memory. So far no memory problems, however we still have not set the Max allotment, since we are waiting for the client to approve.