User`s guide

network communication will not affect SQL Server. Most cards use the first processor, although

some Intel cards do use the last processor.

You must restart the SQL Server service for the changes to take effect.

If Hyper-threading is enabled, the list of processors will normally start at 0 through the number

of physical cores and repeat to cover the Hyper-thread-created “processors.” To unlink the first

core from SQL in a four-CPU Hyper-threaded system (eight processors according to the OS),

clear the check boxes next to CPU 0 and CPU 4.

NOTE This is the preferred logical processor enumeration sequence recommended to BIOS

vendors by Intel as part of its Netburst architecture. ABIOS that utilizes this preferred sequence

would show the two threads of the first Hyper-threaded CPU as logical CPU’s 0 and 1. To verify

which algorithm is used, check with the BIOS vendor or compare SQL Server processor affinity

options with and without Hyper-threading enabled.

6. Click OK to save the settings.

SQL Server IO Configuration

IT organizations do not analyze the technical drivers behind Disk IO subsystems, SQL Server installations

can result in configurations that have RAID 5 arrays, which are not preferred for SQL Server because of a

compromise between write performance and data redundancy. The more redundant a system is, the more

work is required to write data.

Because SQL Server is extremely disk-write intensive, performance suffers when SQL is configured with

RAID 5. Understanding the RAID levels can help SQL DBAs configure the disk IO subsystem in the most

efficient manner.

RAID 0. “Striping Without Parity”. In this configuration, each block of data is written to each disk in

the array in a “round robin” fashion, which means each disk in the array holds only a portion of the

total data written. Depending on the array configuration, this drastically improves read performance

because data can be read in small parallel chunks. This method also provides improved write

performance because data can be written in parallel. However, time is required to break the data into

the “stripe” that will be written. Because no fault-tolerance exists in this model, when a drive fails in the

array, the entire array fails. A minimum of 2 drives is required for RAID 0, and the resulting size of the

array is calculated by adding the sizes of the drives together.

RAID 1. “Disk Mirroring” or “Disk Duplexing”, which means mirroring on a single channel, duplexing

when multiple channels are used. In this configuration, each bit of data that is written to a single disk is

duplicated on the second disk in the array. RAID 1 is limited to 2 physical disks, which means the array

is capable of increasing the read performance. In a duplexed environment, the performance is

theoretically doubled, while at the same time providing fault tolerance in case a drive fails. Write

performance is not affected by RAID 1. Only 2 drives can participate in a RAID 1 array, and the size of

the array is the same as a single disk.

RAID 5. “Disk Striping with Parity”. As with RAID 1, data is written to each disk in the array in a

“round robin” fashion, but an additional block of data written as “parity” also exists. This parity

information can be used to rebuild the array in case of a disk failure. RAID 5 is the most popular RAID

configuration in data centers and represents an effective compromise between read performance and

fault tolerance. Because time is required to calculate the parity stripe, write performance is not as good

as RAID 0. A minimum of 3 disks is required for RAID 5. The size of the array is calculated by taking

the added size of the total disks and subtracting the size of 1 disk. For example, 80GB + 80GB + 80GB is

equal to the total array size of 160GB.

Configure SQL Server for VCM

VMware, Inc.