Hyperthreading, Turbo Boost, SQL Server and You

  • Article

Here are two topics that are tangentially related, Hyperthreading and Turbo Boost. Conversations beginning with one often lead to the other so here are my thoughts, comments, findings and resources on both.

Hyperthreading and SQL Server

I am asked about hyperthreading all the time. People have a lot of different experiences with hyperthreading and SQL Server.  Some folks think it is wonderful and others never, ever enable it on a SQL Server.  I have seen it help  performance and I have seen it hurt performance. 

Hyperthreading is the SQL Server poster child for "it depends".   

Hyperthreading is a technology that provides some additional processing power by dividing a single core into two logical processors.  The thinking is to push even more work through the physical CPU by having more threads ready and reducing the time the CPU spends waiting for a ready thread. In a workload where the physical processors are already saturated then implementing hyperthreading can actually reduce the workload. Workloads with high levels of contention can also experience poor performance using hyperthreading. 

How hyperthreading will behave in your environment is dependent upon;

  • Your workload
  • Windows Server OS version
  • SQL Server Version
  • Physical CPU chip/chipset
         including cache size

Most environments running multiple SQL Servers will be running on any number of physically different servers with different CPU configurations.  Your SQL Server workload should be tested with and without hyperthreading on the specific server upon which it will be running in production.  This is a case where unless the servers in the test environment are exactly like the servers in production, down to the CPU chipset and configuration, the testing will not be valid. Repeatable test plans should be constructed and executed to gather metrics to quantify the use or disabling of hyperthreading for your environment.

Here are a few broad guidelines on hyperthreading;

  • Turn off hyperthreading if the logical CPU count that would result exceeds the number of CPUs supported by your OS or other software.  Example: Windows Server 2008 R2 supports up to 64 logical  processors, If you have a 40 core server (4, 10 core processors) hyperthreading would result in 80 logical cores. This is 16 cores greater than the OS will support. At start up those 16 logical cores will not be initialized and will be idle.  That is 8 physical cores of processing power not in use.

 

  • Turn off hyperthreading for SQL Server 2005 (in most cases). Some of the synchronization primitives do not see hyperthreading for what it is and interpret it as a multi core processor and some soft NUMA memory behaviours may occur that impact performance. This may be seen when processes execute that scan through large sections of memory. They may not affect your workload though so quantify your choice with testing.

 

  • Test hyperthreading with your workload for SQL Server 2008, 2008 R2 and 2012.  Hyperthreading is fully supported within the SQLOS without issue, but the nature of your specific workload may create issues related to I/O or read in-efficiency.  The results may be seen as excessive CXPACKET waits that are due to the timing of true CPU threads vs.hyperthreading CPU threads.   High CXPACKET waits may be observed even when MAXDOP is set to 1. 

Power Options and Intel Turbo Boost Technology

When Intel first introduced performance scaling in their chips controlled by a power option in Windows many people were dismayed to find their SQL Server's CPU performance throttling down when Windows deemed the system "not busy".  The default Power Option for Windows is "Balanced" which allows the processor to save energy by lowering the core frequency below the base frequency saving energy and lowering CPU performance.  I came across some additional detail on this behavior and have provided it below. 

The following is an excerpt from Performance Tuning Guidelines for Windows Server 2008 R2 (https://msdn.microsoft.com/en-us/windows/hardware/gg463392.aspx) 

Today’s modern processors enable scaling of performance and power based on the current activity on the system. The different performance states are dynamically managed by Windows in conjunction with hardware and platform firmware to respond to varying workload requirements. . The 3 default power plans exposed by Windows provide varying tradeoffs of performance vs. power consumption. For example, if the High Performance power plan is selected, Windows places the system in the highest performance state and disables the dynamic scaling of performance in response to varying workload levels. 

Servers hosting SQL Server should always be configured for High Performance. (my comment) 

To change a power plan:

  1. Click on Start and then Control Panel
  2. From the list of displayed item under Control Panel click on Power Options, which takes you to Select a power plan page (If you do not see Power Options, type the word 'power' in the Search Control Panel box and then select Choose a power plan.) .
  3. By default, the option to change power plans is disabled. To enable this, click the Change settings that are currently unavailable link.
  4. Choose the High Performance option
  5. Close the Power Option window. 

Intel Turbo Boost Technology

Under some configurations and workloads, Intel® Turbo Boost technology enables higher performance through the availability of increased core frequency (overclocking). Intel® Turbo Boost technology automatically allows processor cores to run faster than the base operating frequency if the processor is operating below rated power, temperature, and current specification limits. 

Turbo Boost should be enabled for servers hosting SQL Server. (my comment)

Windows Server 2008 R2 Processor Performance Boost Policy

Intel Turbo Boost Technology is a feature that allows Intel processors to achieve additional performance when it is most useful (that is, at high system loads). However, this feature increases CPU core energy consumption, so Turbo Boost is configured based on the power policy that is in use and the specific processor implementation. Turbo Boost is enabled for High Performance power plans on all Intel processors and it is disabled for Power Saver power plans on all Intel processors.  TurboBoost is disabled on Balanced power plans for some Intel processors. For future processors, this default setting might change depending on the energy efficiency of such features. To enable or disable the Turbo Boost feature, you must configure the Processor Performance Boost Policy parameter. 

The Processor Performance Boost Policy is a percentage value from 0 to 100. The default value of this parameter is 49 percent on Balanced plans and 0 percent on Power Saver plans. Any value lower than 50 disables Turbo mode on some current Intel processors. To enable Turbo Mode, set this value to 50 or higher. 

The following commands set Processor Performance Boost Policy to 100 on the current power plan. Specify the policy by using a GUID string, as shown below:

Powercfg -setacvalueindex scheme_current sub_processor 45bcc044-d885-43e2-8605-ee0ec6e96b59 100

Powercfg -setactive scheme_current 

Note that you must run the powercfg -setactive command to enable the new settings. You do not need to reboot the server.

For more detail please review the relevant portions of Performance Tuning Guidelines for Windows Server 2008 R2 (https://msdn.microsoft.com/en-us/windows/hardware/gg463392.aspx). 

Resources:

Intel Whitepaper: Intel Turbo Boost Technology in Intel Core Nehalem Processors

https://download.intel.com/design/processor/applnots/320354.pdf?iid=tech_tb+paper 

Kevin Kline with some thoughts on hyperthreading on SQL Server 2005

The Perils of Hyperthreading on SQL Server

https://www.sqlmag.com/article/sql-server/the-perils-of-hyperthreading-on-sql-server 

 Slava Oks on hyperthreading on SQL Server 2000

Be aware: To Hyper or not to Hyper

https://blogs.msdn.com/b/slavao/archive/2005/11/12/492119.aspx