Node XX has failed and Node YY takes over:
Node XX has SQL Server AA running on it and is using 3 Gigs of RAM. On other side, Node YY has SQL Server BB running on it, which is using 3 Gigs of RAM, and 1 Gig of RAM is occupied by operating system resources. So Node YY has 4 Gigs of free memory left. In this case, this failover will work out smoothly. There are sufficient amount of memory resources available on the failover Node.
Memory Capping
There can be certain situations where you want to limit how much RAM SQL server uses. This can be achieved by Memory Capping. We can cap the maximum memory usage threshold by using the sp_configure “max server memory” option.
In above failover scenario, let’s discuss that how Memory Capping would help where Node X has failed and Y is takes over. If we cap the memory on SQL Server A and SQL Server B to 3 Gigs, then the failover would be successful.
Memory capping helps where you have many small instances running on one box and you want to control the memory usage on each of them according to their criticality.
Important Factors When Configuring Memory
With a clustered SQL Server configuration, the DBA should consider using additional memory and high speed CPU resources.
-
Use Task Manager, or Performance Monitor counters, to analyze that how much memory your operating system needs in a typical day-to-day cycle.
-
SQL Server 2000 and standard services together use over 100 MB of memory as a baseline.
-
User connections consume about 24 KB each.
-
While the minimum memory for query execution is one MB of RAM, the average query may require two to four MB of RAM.
Memory Switches
Memory switches play an important role in memory configuration between the operating system and SQL Server. Let’s look at an example of a server with 4 Gigs of memory. If you do not specify any memory switches, SQL Server will be able to access only 2 Gigs by default out of 4 GB memory on the server. We can specify a /3GB switch in Boot.ini to tell the SQL Server to make use of 3 Gigs of RAM out of the base 4 Gigs. If you don’t specify this option, it will be waste of 1 Gig of RAM.
If your total RAM exceeds 16 Gigs of RAM and the AWE option is enabled, then your operating system itself needs 2 Gigs of memory to manage the 16 Gigs of AWE memory. Configuring the /3GB switch in Boot.ini would not help in this scenario. Following is an example of enabling the /3GB switch in boot.ini file.
multi(0)disk(0)rdisk(0)partition(2)WINNT=”Windows 2000 Advanced Server” /3GB
AWE Memory Configuration – Advanced Windowing Extensions
A Pentium Family 32-bit CPU can access only 4 GB of RAM due to it’s limited address space. To overcome this limitation, SQL Server 2000 supports a feature called AWE.
AWE Memory Configuration is useful only when your RAM size exceeds 4 Gigs. If you are using SQL Server 2000 Enterprise Edition and your RAM does not exceed 4 Gigs, then you do not require doing any AWE configuration. AWE enables the system to access more memory then 4 Gigs. SQL 2000 Enterprise Edition is AWE enabled and can use up to 8 Gigs of memory if AWE enabled. If the operating system is Windows 2000 Datacenter Server, then SQL Server 2000 Enterprise can use up to 64 Gigs of RAM.
To enable the AWE memory management, execute the following command.
sp_configure ‘show advanced options’, 1
reconfigure
go
sp_configure ‘awe enabled’, 1
reconfigure
go
There is a caution about using AWE enabled memory management. It occupies all RAM available on the box, leaving only 128 MB for the operating system. This means as soon the the SQL Server service is started, it will eat-up all memory available on the system. This is called Static Memory Management. Microsoft has overcome this limitation in SQL Server 2005 by introducing dynamic memory management with AWE enabled. Setup and administration can be complex if AWE configuration is used with multiple Instances.
As we know that the default AWE configuration on SQL Server 2000 consumes the entire box’s memory, leaving only 128 MB for operating system, we can overcome this situation by useing the Memory Capping option, and define a max server memory.
sp_configure ‘max server memory’, 6144
reconfigure
go
Above configuration tells SQL Server to use only 6 Gigs of RAM and leave the remaining memory for operating system or other applications resources on the box.
PAE Memory Configuration
Windows 2000 Advanced Server and Windows 2000 Datacenter Server support memory in excess of 4 Gigs of RAM by way of the Intel Physical Addressing Extension (PAE) specification. Windows 2000 Advanced Server is limited to 8 GB, and Windows 2000 Datacenter Server is limited to 64 GB. You can enable PAE in the Boot.ini file by adding a /PAE switch in boot.ini to the corresponding line to your operating system. Following is an example of enabling the PAE switch in boot.ini file.
multi(0)disk(0)rdisk(0)partition(2)WINNT=”Windows 2000 Advanced Server” /PAE