5.6

SQL Server Processor Settings
In multiprocessor environments, you must configure the SQL Server use of the processors. To do this, you
reserve a processor by removing it from SQL Server, to be used for other functions such as the VCM
Collector service and Internet Information Services (IIS). Because IIS cannot make use of processor affinity
in multiprocessor machines, it uses them all equally.
The hyper-threading machine-level setting must be controlled through BIOS settings. The main
disadvantage of hyper-threading is that the two threads that run concurrently in one core share the same
cache. If these threads are performing calculations, they will not interfere with each other and will run
significantly faster than a single thread. If the threads are each working with a relatively large block of
data, such as processing a SQL query, their activities will step on each other’s cache, which can cause the
two threads to accomplish less work than could be accomplished by a single thread.
Configure the SQLServer Processor settings to set the maximum worker threads or boost the SQL Server
priority.
Procedure
1. Click Start.
2. Select All Programs > Microsoft SQL Server 2008 R2 > SQL Server Management Studio.
3. Right-click the SQL instance that you installed and select Properties.
4. In the navigation pane, select Processors.
5. In the Enable processors area, select Automatically set I/O affinity mask for all processors.
6. Select I/O Affinity for all processors in the Enable processors list.
7. Configure the following settings as needed for your configuration and restart the SQL Server service
for the changes to take effect.
n
To remove a processor from SQL Server and reserve it for the OS, uncheck the check box next to
the processor. Remove the processor that the network card will use so that network
communication does not affect SQL Server. Most network cards use the first processor, but some
Intel network cards use the last processor.
n
When hyperthreading is enabled, the processor list normally starts at 0 and lists the number of
physical cores, and then repeats to include the hyperthread-created processors. For example, to
unlink the first core from SQL in a four-CPU hyperthreaded system, which includes eight
processors according to the OS, clear the check boxes next to CPU 0 and CPU 4. This is the
preferred logical processor enumeration sequence recommended to BIOS vendors by Intel as part
of its Netburst architecture. A BIOS that uses this preferred sequence shows the two threads of the
first Hyper-threaded CPU as logical CPU 0 and 1. To confirm which algorithm is used, verify with
the BIOS vendor or compare the SQL Server processor affinity options with and without
hyperthreading enabled.
8. 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, the more
work it takes to write data.
vCenter Configuration Manager Advanced Installation Guide
123
VMware, Inc.