MORE INFORMATION
The core
design of SQL Server is fully functional in a hyper-threaded environment.
However, if you are using a hyper-threaded environment, we recommend that you
do the following:
- Run Microsoft SQL Server 2000 Service Pack 3 (SP3) or a later service pack.
- Install the latest security update.
The Microsoft Windows operating system makes the
logical hyper-thread CPUs appear as physical CPUs. Because SQL Server is
already highly scalable, the additional CPUs give SQL Server the ability to use
more processors.
For additional information about Microsoft Windows
support of hyper-threading, visit the following Microsoft Web site:
Note For the most benefit from hyper-threading, we recommend the
Windows Server 2003 environment for SQL Server 2000.
You must be aware
of the following things when you are considering a hyper-threaded deployment:
- Licensing
- Processor mapping
- Performance
- Intel's CPU counting utility
More information about each item follows.
Licensing
When hyper-threading is enabled, the Basic Input Output System
(BIOS) uses a logical to physical CPU ratio. Current implementations are using
2:1 ratios. This means that there are two (2) logical CPUs for each physical
CPU. These ratios may change in the future; however, Microsoft SQL Server 2000
Service Pack 3 (SP3) has additional licensing code that adjusts the licensing
restrictions to handle the ratio. For example, if the SQL Server product code
that you have installed permits a 4 CPU license at a 2:1 ratio, the SQL Server
2000 SP3 build adjusts and lets you use 8 CPUs.
When you install SQL
Server, you will use the physical CPU count and you let SQL Server handle the
ratio conversion. In contrast, when you set the
affinity mask option, you will use the logical CPU values because SQL Server is
using all the CPUs as if they were physical processors.
For more
licensing and supported configuration details, visit the following Microsoft
Web site:
Processor mapping
By default, SQL Server creates a logical User Mode Scheduling
(UMS) scheduler for each processor according to the ordinal processor order. In
hyper-threaded enabled environments, Microsoft Windows makes all the processors
available to the SQL Server process as if they are true physical processors. A
sample of logical and of physical processor mapping is shown in the following
table.
Processor mapping table 1
Here is another processor mapping sample.
Processor
mapping table 2
Either mapping is acceptable to SQL Server. However, be careful
not to create a situation where SQL Server is unexpectedly limited to a
physical CPU when it is expected to use multiple physical CPUs.
You
can use the SQL Server affinity mask option to change expected physical
processor usage. To do this, see Processor mapping table 1. If the affinity
mask is 00010001 and it is using processor 0 and processor 4, the logical
processors that are in use are mapped to the sample physical processor, #0.
This results in single physical processor use that supports both of the logical
processors instead of two physical processors. This situation will probably
decrease performance.
You can use a combination of the affinity mask
option and the IO Affinity Mask startup parameter to change the default
behavior. For more information about the affinity mask option or the IO
Affinity Mask startup parameter, see SQL Server Books Online.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
298402
Understanding how to set the SQL
Server I/O affinity option
Use caution when you establish the affinity mask. You can easily
configure the affinity mask incorrectly by using the same physical processor to
support two logical processors when you intended to use separate physical
processors.
Performance
The performance of hyper-threaded environments varies.
Conservative testing has shown 10 to 20 percent gains for SQL Server workloads,
but the application patterns have a significant affect. You might find that
some applications do not receive an increase in performance by taking advantage
of hyper-threading. If the physical processors are already saturated, using
logical processors can actually reduce the workload achieved.
For
example, applications that cause high levels of contention can cause decreased
performance in a hyper-threaded environment. We recommend that you test your
application thoroughly to make sure that a hyper-threaded environment provides
you the performance gain that you want versus the purchase of equivalent
physical CPUs. Hyper-threading can be very helpful but hyper-threading cannot
replace the full power of an additional physical CPU.
The third-party products that this
article discusses are manufactured by companies that are independent of
Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the
performance or reliability of these products.
The Intel Web site has some good information
and tutorials about hyper-threading (Intel NetBurst microarchitecture). For
more information about systems that support hyper-threading, visit the
following Intel Web sites:
Intel's CPU counting utility
Intel provides a CPU counting utility. CPUCount.exe is the
file name of the Intel utility program. The following is a sample of the output
from CPUCount.exe that identifies the mapping ratio:
CPU Counting Utility
Version 1.4
Copyright (C) 2001 Intel Corporation. All Rights Reserved
Hyper-threading technology enabled
Number of logical processors per physical processor: 2
Number of physical processors: 8