I had a client ask whether or not SQL Server could take advantage of all 32 logical processors if they enabled hyper-threading on their server. They were running SQL Server 2012 Standard Edition on a server with 4-sockets, each would 4 cores. As in nearly all things related to SQL Server, the answer is “it depends”.

The compute capacity of SQL Server 2012 Standard Edition (and later versions as of this writing) is the lesser of 4 sockets or 16 cores regardless of the number of logical processors. With hyper-threading enabled and running SQL Server on bare metal, the number of logical processors doubles from 16 to 32. SQL Server 2012 SE can use all 32 of these logical processors because the compute limit is based on physical sockets/cores, not logical processors.

However, SQL Server 2012 SE can use only 16 of the 32 logical processors when running virtualized on the same physical server. This is because the physical processor architecture isn’t exposed to the guest OS. Logical processors available to the virtualization host are mapped to guest VMs and these appear as virtual sockets and cores to both the OS and SQL Server with no notion of hyper-threading. The end result is that SQL Server 2012 SE will use no more than 16 logical processors when virtualized with or without hyper-threading enabled.

Other Hyper-threading Considerations
Regardless of the SQL Server edition, one should enable hyper-threading only if the OS and SQL Server can utilize the additional logical processors. This practice guarantees logical processors and physical cores are one and the same, providing the best performance possible. When additional logical processors provided by hyper-threading can be used, the SQL Server performance benefits, or even detriments, are very work-load dependent. The only way to accurately ascertain HT performance is with your actual production workload.

My past experience is that OLTP workloads benefit the most from hyper-threading. In the early days of HT technology (before Windows and SQL Server were HT-aware), I observed a 15-20% performance improvement for an OLTP workload. Unfortunately, reporting workload performance could worsen with HT enabled. Slava Oks’s blog post may explain why.

The world has changed a lot since then. SQL Server is now both HT and NUMA aware, with improved locking primitives and memory-optimized structures available that avoid locking entirely. With current SQL Server versions running bare-metal on modern hardware, I suggest you enable HT unless your actual workload experience dictates otherwise. Premature optimization is evil; don’t disable HT preemptively without justification with your own workload or you may be wasting free CPU cycles. Importantly, limit MAXDOP per this support article.

See Linchi’s Shea’s post and also this one for reporting workload tests with and without HT enabled. Also, see Joe Chang’s post. The first graph is especially interesting in that it shows an actual production OLTP workload running with and without HT. The remainder of the post shows results of the TPC-H benchmark tests, a synthetic decision support (reporting) workload.

Again, your workload is unique so only you can ascertain if HT is actually beneficial.