Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 164

Hyper-Threading
The most commonly asked question about Hyper-Threading and SQL Server is, ''Should I enable it
or disable it?'' This is a difficult question and the best answer of course is to test it yourself using your
workload and your hardware to see if it gives you a performance increase or even a performance decrease .
This is the cause of all the concern; it may actually impact your SQL Server's performance depending
on your usage and workload. Before you decide whether to use it on your server, have a look at what
Hyper-Threading actually is and what the theoretical performance benefits are.

First of all let's define what a thread is; a thread is a unit of execution that runs on a CPU that can only
execute one thread at a time. However, a CPU is very good at switching between threads very quickly to
share its power, giving the illusion of simultaneous execution.

Hyper-Threading is a technology found only on Intel processors, that tries to make more efficient use
of the CPU by duplicating the architectural state to provide two logical CPUs. This allows more than 1
thread to be scheduled and enables simultaneous execution when the threads require different processor
functions. The effect of this is that you will see two logical CPUs to which the operating system can
assign different threads. However, it is only the architectural state that is duplicated, not the physical
execution engine. Figure 6-10 shows a 4 dual-core system with Hyper-Threading enabled, giving 16
logical processors.

Socket

0 1 2 3

Logical CPU

CPU Core

Figure 6-10

The first step when considering Hyper-Threading is to understand the maximum performance bene-
fit that you might get. Intel's documentation reveals that the maximum theoretical performance gain
from Hyper-Threading is 30 percent. Many people running with Hyper-Threading enabled for the first
time expect to see double the performance because the operating system shows two processors. You
should understand that Hyper-Threading is only ever going to give you a maximum of 1.3 times non-
Hyper-Threaded performance and in practice it will be closer to 1.1 to 1.15 times. This knowledge helps
put any decision about enabling it back into perspective.

There are a couple of places where Hyper-Threading won't provide any benefit. In any workload
where the code is running a tight loop entirely from cache, Hyper-Threading won't provide any benefit,
because there is only a single execution engine. This can show a performance decrease as the operating
system tries to schedule activity on a CPU that isn't physically there.

Another place where Hyper-Threading can directly affect SQL Server performance is any time a parallel
plan might be chosen. One of the things a parallel plan does is to split the available work across the