If you find that you are running into a memory bottleneck, and assuming you have the money to spend, SQL Server 2000/2005 Enterprise Edition can support up to 64GB of RAM. How much RAM SQL Server Enterprise Edition can use depends on which version of Windows 2000/2003 you are using and how much RAM your server can support.
Normally, 32-bit CPUs, such as the Pentium family of processors, can only support up to 4GB of RAM because of its limited address space. To get around this limitation, SQL Server 2000.2005 Enterprise Edition supports a feature called AWE (Address Windowing Extensions), that allows up to 64GB of RAM to be addressed.
Assuming you configure the appropriate hardware and software, AWE support needs to be turned on. To turn AWE support on, you must change the “awe enabled” advanced SQL Server 2000/2005 option from 0 to 1. For example, to turn on AWE support:
SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE
GO
SP_CONFIGURE ‘awe enabled’, 1
RECONFIGURE
GO
Once AWE support has been turned on, SQL Server’s dynamic memory is turned off. This means that when AWE support is turned on, that all of the RAM in the server, with the exception of about 128MB, will be dedicated for use by SQL Server. For most dedicated SQL Servers, 128MB may be enough for the operating system to successfully run. But if you are running additional software on your server, you may have to tell SQL Server not to claim all but about 128MB of RAM. To do this, you can use SQL Server’s “max server memory” configuration option. For example:
SP_CONFIGURE ‘max server memory’, 4096
RECONFIGURE
GO
In the above example, we are telling SQL Server to only use 4GB of RAM, leaving any other RAM available in the server free for other applications.
Assuming you have the budget, adding large amounts of RAM to your server can greatly speed up many databases. [2000, 2005] Updated 5-18-2004
*****
For best I/O performance, use the fastest disk drives you can get in your disk arrays. Currently, the fastest SCSI disk drives operate at 15,000 rpm and higher. Also, don’t mix disk drives in an array that have different rpm speeds. They all should be the same speed for best performance. [6.5, 7.0, 2000, 2005] Updated 1-3-2005
*****
Many vendors are recommending SAN or NAS storage devices for storing large quantities of SQL Server data. A SAN (storage area network) system is an external storage system that allows multiple computer systems to access the same storage. The fiber controller inside the external storage system is able to take requests for different logical volumes from different HBAs (Host Bus Adapters). A NAS (network attached storage) is similar, however, unlike the SAN system where the storage is connected via a fiber channel connection, a NAS system is accessed via a network connection.
Of the two choices, a SAN is a much better choice than a NAS. The problem with NAS is that it is IP-based and data must travel over your network, which is limited by the top speed of the network, and other shared network traffic. This can greatly increase I/O latency, harming performance. If you need large disk capacity, a SAN is the reasonable choice. [7.0, 2000, 2005] Updated 1-3-2005 See this article for more information on using SANs with SQL Server.
*****
From a performance perspective, it is better to have more smaller SCSI disk drives in an array than having fewer larger SCSI disk drives. Let’s say that you need about 100GB of hard disk space in a RAID 5 array. There are several ways you can configure such an array, some of which offer more performance than others. Some configurations include:
· 13 – 9GB Drives
· 7 – 18GB Drives
· 4 – 36GB Drives
Each of the above configurations will provide about the same amount of storage space, but the more drives there are in the array, the faster the I/O will be (assuming that the controllers can handle all of the I/O traffic). This is because more drives offer more read/write heads that all can be working simultaneously, which speeds disk reads and writes. [6.5, 7.0, 2000, 2005] Updated 1-3-2005