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
Storage Configuration
Storage is a critical factor of any database system. All activities being performed by users is being stored ultimately on data storage devices. Storage disk array is a single point of failure which can bring down an entire cluster setup. To address the data availability, the data and logs should be placed on a fault tolerant external storage arrays which supports high volume of disk I/O with high speed performance. RAID storage arrays are most popular solutions in storage world; let’s take a look at the RAID options available.
RAID Configurations Overview – Redundant Array of Independent Disks
RAID provides a method of accessing multiple individual disks as if the array were one larger disk, spreading data access out over multiple disks, thereby reducing the risk of losing all data if one drive fails, and improving access time.
Data Placement
For optimum disk I/O performance, data and log devices, including tempdb, should be placed on separate disk arrays. In a large OLAP system the database resources are used differently than OLTP systems. If an OLAP system uses tempdb heavily, then moving it into a different drive, which contains multiple fast disks, would boost the I/O performance.
-
Data files – Data drives should use the RAID configuration of 0+1, also known as Disk-Striping + Disk-Mirroring; or 1+0, also known as Disk-Mirroring + Disk-Striping. 0+1 means that first the drives are striped as one set and then mirrored. 1+0 means that first drives are mirrored and then whole thing is striped. RAID level 0+1 give a little better performance while comparing to 1+0. RAID level 1+0 is proven to be better in terms of fault tolerance when compared to 0+1.
-
Log files – Log drives can be configured either with RAID 1, which is plain mirroring, or striped mirrors/mirrored stripes. Logs are important to protect, so choose the appropriate availability of the disk for your high availability plan.
-
Tempdb files – Microsoft recommends placing the tempdb on a fast I/O subsystem to get good performance. In this case, use RAID 0+1 disks for tempdb database.
Disk Configuration Dynamic / Basic
Dynamic Disk configuration is not supported in Windows Clustering. A disk initialized for dynamic storage is called a dynamic disk. It can hold simple volumes, spanned volumes, mirrored volumes, striped volumes, and RAID-5 volumes. With dynamic storage, you can perform disk and volume management without having to restart the operating system.
Basic Disk configuration uses normal partition tables supported by all versions of Windows, MS-DOS, and Windows NT. A disk initialized for basic storage is called a basic disk. It can hold primary partitions, extended partitions, and logical drives. Basic volumes include partitions and logical drives, as well as volumes created using Windows NT 4.0 or earlier, such as volume sets, stripe sets, mirror sets, and stripe sets with parity. In Windows 2000, these volumes are called spanned volumes, striped volumes, mirrored volumes, and RAID-5 volumes, respectively.