System and Storage Configuration for SQL Server

Platforms

For the very large majority of needs today, the 1, 2 and 4 socket systems are more than adequate. Most sites buy either the 2 or 4 socket system. However, given the capability of microprocessors today and the prevalent availability of multi-core processors, serious consideration should also be given to the single socket system when appropriate. Vendors are reluctant to recommend this system over higher margin dual socket systems.

The table below summarizes the processor options. Dual core processors are currently available for all option except for Itanium, for which dual core should be available in mid or late 2006. The single core processors are usually available at slightly higher frequency than the comparable dual core versions. The software licensing costs will strongly favor the dual core option except for code that must execute in a serialized manner.

The AMD Opteron processor probably has the higher performance for a broad range of SQL Server operations in the early 2006 time frame. The Opteron also does exceptionally well where serialized memory accesses are required. The Intel Pentium D, Xeon and Xeon MP processor line is somewhat behind at this point in time, mostly due to thermal limitations, capping the top operating frequency far below the maximum that could be supported by the transistor switching rate. The Pentium 4 / Xeon family does have the best table scan performance of all processor lines. The Hyper-Threading feature in this line does improve performance in high network round-trip volume applications. Unfortunately, HT does not improve performance uniformly for other operations, and may even cause performance degradation in some operations, so the use of HT requires careful analysis. HT does improve compression performance by 50% per core, which is very impressive. This characteristic benefits the database backup and restore operations when using third party backup compression software such as Quest (formerly Imceda) LiteSpeed.

The Intel Core Duo and its predecessor, Pentium M are 32-bit only processors, but have excellent performance and very low power consumption. These are most suited to 32-bit only environments, single socket systems, in high density or other power constrained environments. The Itanium 2 line is currently lagging in the 2-4 socket system space because of the delay in releasing a dual-core version and that the current 130nm Madison processor is competing against 90nm Opteron and Xeon processors. However, in the high-end scale-up environments, Itanium systems warrant serious consideration for Windows and SQL Server based solutions. Unisys offers a high-end product line based on the Xeon MP processor. Prior to the introduction of 64-bit capability in the Xeon MP line, there were serious limitations in the use 32-bit Xeon MP processors in large NUMA systems, mostly due to the limited 4GB address space. It is unclear today how the market for systems with more than four sockets will split between Itanium, Xeon MP and possible Opteron contenders, now that full 64-bit capability is available in all processor lines.

Below is a simplified representation of a single socket system based on the Intel E7230 chipset. The IO capability of this desktop derived chipset is quite impressive. There is one x8 PCI-Express port that is bridged into 2 PCI-X busses. The first at 100MHz has 2 slots and the second at 133MHz with 1 slot. Alternative configurations include a single x8 PCI-E port, two x4 PCI-E ports or one x4 PCI-E and one PCI-X bus. In general, PCI-E configurations should be preferred if PCI-E adapters are available. The DMI port connects the memory controller to the ICH7 with the same bandwidth as one x4 PCI-E port. The ICH7 has 4 SATA ports at 3.0Gbit/sec, 2 PCI-E x1 ports for gigabit Ethernet and one x4 PCI-E port.

The E7230 chipset should have no problems driving 4 internal SATA drives plus 2 RAID controllers and 4 SCSI channels. However, a single RAID controller with 2 SCSI channels should be adequate for this single socket system. It could be mentioned that the E7230 is derived from the 9XX desktop chipset line, which actually has one x16 PCI-E port for graphics and one DMI port, which is essentially a PCI-E x4 port. There is no reason the x16 graphics port could not have been configured as two x8 ports, except that IO capability is already beyond the needs for most single socket systems.

The figure below is a representation of a two socket system based on the Intel E7520 chipset. The E7520 has three x8 PCI-E ports and one HI interface for low bandwidth legacy devices. Each x8 PCI has a very impressive 2Gbytes/sec nominal bandwidth in each direction. The actual sustainable bandwidth has not been verified.

As shown, one x8 PCI-E port is bridged into two PCI-X busses. Each PCI-X bus has an embedded PCI-X device in addition to the available open slots. Alternative configurations for the E7520 chipset include 3 x8 PCI-E ports, or 1 x8 PCI-E and four PCI-X busses, two each on two IO bridges. Each x8 PCI-E port can also be configured as two x4 ports. For the two socket system, 4-8 internal SCSI disks on the two embedded SCSI channels and two dual channel RAID controllers for a total of 2 internal and 4 external SCSI channels is a good combination.

The figure below shows a four socket system based on the Intel E8500 chipset. There are two independent processor busses, each capable of supporting two processor sockets for a total of four sockets. There are the four independent memory interfaces (IMI) that attach to the north bridge. Each IMI channel can be expanded with an external memory bridge (XMB) device into two DDR-II memory channels. In the IO subsystem, there are three x8 PCI-E ports, one x4 port and the legacy HI link for legacy IO devices. As with the other chipsets, each x8 port can be configured as two x4 ports. As shown below, one x8 port is bridged into two PCI-X busses, with embedded SCSI and GE. One x8 port is left as is, the third x8 port is configured as x4 ports for a configuration of one 1 x8 and three x4 PCI-E slots.

The disk configuration is 4-8 internal disks on the two embedded SCSI channels and four dual channel RAID controllers on each of the available PCI-E slots, for a total of 10 SCSI channels, 8 connected to four external dual-channel disk enclosures. The objective of this configuration is to achieve >2GB/sec sequential disk bandwidth. It is unclear whether the E8500 can actually sustain this bandwidth, but at least 1.5GB/sec has been verified.  

In any case, both the E7520 and E8500 have very impressive IO bandwidth capability. If there is any disappoint, it the continued use of the HI interface for legacy devices, considering that the much improved DMI interface is available on desktop chipsets. It could be that Intel did not want to risk a delay in launch of the E7520 and E8500 chipsets on non-essential new technology, but a follow chipset could have corrected this deficiency.

The AMD Opteron chipset has been verified to sustain 2.4GB/sec in disk bandwidth. (This was reported by Microsoft Research).