DetailPage-MSS-KB

Knowledge Base

Article ID: 322385 - Last Review: January 18, 2013 - Revision: 11.0

This article was previously published under Q322385

On This Page

INTRODUCTION

This article discusses SQL Server support in a hyper-threaded environment.

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:
http://www.microsoft.com/whdc/system/sysinternals/ht-windows.mspx (http://www.microsoft.com/whdc/system/sysinternals/ht-windows.mspx)

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) and later versions have 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 and later builds adjust and let 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:
http://www.microsoft.com/sql/howtobuy/default.mspx (http://www.microsoft.com/sql/howtobuy/default.mspx)

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
Collapse this tableExpand this table
LogicalPhysical
00
11
22
33
40
51
62
73
Here is another processor mapping sample.

Processor mapping table 2
Collapse this tableExpand this table
LogicalPhysical
00
10
21
31
42
52
63
73
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  (http://support.microsoft.com/kb/298402/ ) Understanding how to set the SQL Server I/O affinity option
Warning 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, about 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:
http://software.intel.com/en-us/blogs/2009/06/02/intel-hyper-threading-technology-your-questions-answered (http://software.intel.com/en-us/blogs/2009/06/02/intel-hyper-threading-technology-your-questions-answered )

http://developer.intel.com (http://developer.intel.com)


Max degree of parallelism (MAXDOP)

A hyper-threaded CPU introduces common CPU cache invalidation issues that physical multiprocessor implementations do not experience. The application workload can affect the performance gains and the common CPU cache behavior. We recommend that you test the application workload with several variations of the MAXDOP setting. Performance gains may be achieved by using a MAXDOP setting that is no more than the number of physical processors that are being used. If you are using NUMA, the MAXDOP setting should be set to no more than the number of CPUs in each NUMA node. For example, if the system is configured for two physical processors and four logical processors, the MAXDOP setting should be 2. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2023536  (http://support.microsoft.com/kb/2023536/ ) Recommendations and Guidelines for 'max degree of parallelism' configuration option

Applies to
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Express Edition with Advanced Services
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Express with Advanced Services
  • Microsoft SQL Server 2008 R2 Parallel Data Warehouse
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 Reporting Services
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2012 Business Intelligence
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbinfo KB322385
Delen
Extra ondersteuningsopties
Microsoft Community Support-forums
Neem rechtstreeks contact met ons op
Een door Microsoft gecertificeerde partner zoeken
Microsoft Store