Sql-server – Hyperthreading vs. SQL Server & PostgreSQL

In the SQLOS a scheduler is created for each logical processor that SQL Server sees. With hyperthreading enabled this equates to double the schedulers. One of the purposes for the SQLOS is to minimize and prevent context switching from occuring which is why only one scheduler is created for each logical processor. Once the SQLOS creates the schedulers, the total number of workers is divided amongst the schedulers. The SQLOS implements a form of cooperative scheduling in that the workers yield the scheduler as it requires unavailable resources, or reaches its execution quantum allowing other workers to execute on the scheduler. This keeps context switching to a minimum since the scheduler is doing the work and they are bound one for one.

Understanding that background, hyperthreading somewhat works opposite of how SQLOS is specifically designed to function. Specifically, parallelism can be problematic with hyperthreading and can result in high CXPACKET waits since SQLOS may try to run a query at DOP 8 on what is reality a DOP 4 system. If your CPU utilization is low, you might not notice, but the higher your CPU utilization goes the more problematic it could become. I recently had a discussion on twitter regarding this, and the consensus was "It Depends" as to whether or not it would help or hurt.

If you have a lot of signal waits on your server, but you have low CPU utilization, you may see benefit enabling hyperthreading which will double your internal schedulers and spread the workers out more which means they won't wait to execute in the runnable queue as long. However if your workload utilized parallelism heavily you get heavy CXPACKET waits in sys.dm_os_wait_stats, you might look at disabling hyperthreading to see if it reduces your waits.

I've only read about this topic a handful of times. I found an article at www.vmguy.com here that sums up the consensus on this HT issue (direct from the article):

There are pros and cons to using HT in ESX.

Pros

• Better co-scheduling of SMP VM’s

  Hyperthreading provides more CPU contexts and because of this, SMP VM’s can be scheduled to run in scenarios which would not have enough CPU contexts without Hyperthreading.

• Typical applications see performance improvement in the 0-20% range (the same as non-virtualized workloads).

Cons

• Processor resources are shared with Hyperthreading enabled

Processor resources are shared such as the L2 and L3 caches. This means that the two threads running on the same processor compete for the same resources if they both have high demand for them. This can, in turn, degrade performance.

All things considered, it is difficult to generalize the performance impact of Hyperthreading. It is highly dependant on the workload of the VM. One additional point is that you can always utilize the CPU min and max values on a per-VM or Resource Pool basis to reserve certain amounts of CPU for your most critical workloads. As with the majority of performance items I enounter, test, test, test. Try out the workloads and see what works the best on the hardware you have available.

Again, this is directly from the article. I'm not certain that HT is worth it as I too use AMD Opterons so I can't speak from experience.