System and Storage Configuration for SQL Server

By : Joe Chang Apr 04, 2006

Printer friendly

A proper, technically correct system and storage sizing assessment of a SQL Server system and storage configuration needs to consider a broad range of CPU, memory and IO usage analysis. The cost of this assessment might be $10-20K with proper accounting for personnel time, depending on the availability of an existing production environment from which forward projections can be made, or whether this has to be done entirely from theoretical models and a database prototype. Now, should it turn out that a $20K server and storage system is determined to be overkill to meet the final requirements, it should be asked whether it makes sense to conduct a rigorous technical assessment. Fortunately, a reasonable set of system configurations can be arrived at without heavy technical analysis, driven mostly by the cost structure of the Windows operating system, SQL Server licensing, and the server platform. In cases where a large system with more than four processor sockets is required, it still does make sense to conduct a proper technical analysis.

Management Summary

Upper management people frequently do not have time to internalize the detailed rational for each system configuration decision and only want a quick summary. The simple picture and answer, without the detailed underlying rational is presented below. In case it is not already apparent, the general theme is prolific IO bandwidth and random IOPS capability. In theory, the realizable sequential bandwidth over 2 internal SCSI and 8 external SCSI channels is 2.5GB/sec, but this has not been verified.

The table below is a quick summary of system configuration options for systems using the Intel E7230, E7520 and E8500 chipsets. The configurations are based on U320 SCSI storage technology. Over the next year, expect SCSI to be replaced by a combination of SAS and SATA as the technology of choice in a wide range of server and storage systems.

*A single x8 slots can be configured as two x4 slots or converted to 2 PCI-X busses.

A brief description of software, system, memory and storage choices are described below.

• Software: Windows Server 2003 64-bit and SQL Server 2005 64-bit are preferred over 32-bit versions, including SQL Server 2000, if full 64-bit operation is supported by other required components. Some performance tuning may be required due to changes in behavior from SQL Server 2000 to 2005 and from 32-bit to 64-bit versions.
• System: any suitable 1, 2 or 4 socket server system with dual core processors. Even if it turns out that a big NUMA system is required, just rotate the 1-4 socket system to a QA function. Consider that a single socket dual-core system today is comparable to a 4-way Xeon MP 2.0GHz system of the 2002 vintage.
• Memory: fill available DIMM sockets with 1GB or 2GB DIMMs, unless it is otherwise known that the 4GB DIMM is required. Chose the 2GB module if the price is favorable relative to the 1GB module. Memory requirements may influence system selection. It can be more effective to buy a four socket system with more DIMM sockets even if only 2 processor sockets are actually desired, especially if this avoids the need to configure 4GB memory modules.
• Storage: Distribute load across 4-10 or more IO channels and 16-60+ disk drives. Any of the U320 SCSI, FC or SAS interfaces can meet most performance requirements with proper distribution. The disk drives of choice for early 2006 are the 15K 36GB drive or the 10K 72-74GB drive. Do not let a moron convince you that the big 146GB 15K or 300GB 10K drives meets capacity requirements at lower cost. That is not the point with regard to performance. For SFF 2.5” SAS drives, either the 36 or 72GB 10K are acceptable.

One final point is of critical importance. Test the performance of the final configuration. What a disk configuration should be able to do and can actually do are not the same thing.

Software Versions

For practical purposes, the software licensing fee structure helps in narrowing the recommended set of system configurations. The table below shows the Windows Server 2003 R2 processor and memory support by edition for both 32 and 64-bit versions.

The table below shows SQL Server 2000 processor and memory support by edition. The SQL Server 2000 64-bit version is for Itanium only.

The table below shows SQL Server 2005 processor and memory support by edition for 32-bit and 64-bit versions.

The main point of the above is that the older software products restricted the ability to use memory. At the time of product launch, the restrictions were reasonable. The 3GB and AWE where limited to the Enterprise Editions, which was not unreasonable in 2000, when 4GB memory was expensive and required an expensive system as well. Since then 8-16GB memory is not unreasonable for the Standard Edition price point, but the lack new releases meant the only option was SQL Server 2000 Enterprise Edition. With the release of SQL Server 2005, memory restrictions have been lifted in Standard Edition. The OS does have memory restrictions, but these are more relaxed, especially for the 64-bit versions, and the price step to Enterprise Edition is not as steep.

In any case, the preferred solution is the 64-bit version of Windows Server 2003. Either Standard or Enterprise Editions will fit most needs. SQL Server 2005 is strongly favored over SQL Server 2000 because of the increased memory capability, more so for the Standard Edition. The 64-bit version has unimpeded access to >4GB memory compared with 32-bit versions, which must use AWE.