Demystifying Hardware - SQL Server 2012 Internals and Troubleshooting - page 43

Hyperthreading is Intel's marketing term for its simultaneous multi-threading architecture whereby
each physical processor core is split into two logical cores. The “simultaneous” term is a little mis-
leading, as you cannot actually have two threads running simultaneously on the two logical cores in
a single physical core of the same physical processor. What actually happens is that the threads run
alternately, with one working while the other one is idle.

Hyperthreading works quite well for desktop applications. The classic example is running a com-
plete anti-virus scan while the user is still able to work interactively with another application in the
foreground. Unfortunately, the initial implementation of hyperthreading on the Pentium 4 NetBurst
architecture did not work very well on many server workloads such as SQL Server. This was because
the L2 data cache for each physical core was shared between the two logical cores, which caused
performance issues because the L2 cache had to be constantly refreshed as the application context
switched between the two logical processors. This behavior was known as cache thrashing , and
it often led to a decrease in overall performance for SQL Server workloads. Another factor that
made this situation even worse was the very deep processor pipeline that was used in the Pentium 4
architecture, which made it even more costly when the data needed by the logical processor was not
found in the L2 cache.

Because of these factors, it became very common for database administrators to disable hyper-
threading for all SQL Server workloads, which is really a mistake. Different types of SQL Server
workloads react differently to having hyperthreading enabled, with OLTP workloads generally per-
forming better with hyperthreading enabled, and data warehouse workloads sometimes performing
worse with hyperthreading enabled. Before you decide whether to enable or disable hyperthreading,
test it both ways with your actual workload.

Modern Intel processors (Nehalem, Westmere, Sandy Bridge, and Ivy Bridge) seem to work much
better with hyperthreading because of larger L2 and L3 cache sizes, newer processor architectures,
and faster access to main memory. Because of this, we advise you to enable hyperthreading for SQL
Server, especially for OLTP workloads, unless you have done testing that actually shows a perfor-
mance decrease with your workload. It is signii cant that every single TPC-E OLTP benchmark
submission for these modern Intel processors has been done with hyperthreading enabled on the
database server, which is certainly intentional.

AMD Processors and Numbering
This section discusses AMD Opteron processor numbering. Advanced Micro Devices (AMD) has
various versions of the Opteron family that are meant for server use. When assessing AMD proces-
sors, it is very helpful to understand what the model numbers actually mean. Recent AMD Opteron
processors are identii ed by a four-character model number in the format ZYXX, where the Z char-
acter indicates the product series:

➤

1000 Series = 1-socket servers

➤

2000 Series = Up to 2-socket servers and workstations

➤

4000 Series = Up to 2-socket servers

➤

6000 Series = High performance 2- and 4-socket servers

➤

8000 Series = Up to 8-socket servers and workstations