How does Hyper-Threading work and when to use it on Microsoft SQL Server

Hi Guys,

So the question is when to use Hyper-Threading on a SQL box, and what impact it has. To do this you first need to understand the CPU architectures, so to do this I compared the Intel Core i5 processors with the Intel i7's because the i7's have hyper-threading whereas the i5's don't. That's pretty much the only difference between the 2 if you didn't know.

Most people automatically assume that bigger is better, but welcome to the SQL world where software actually makes a difference, and can save a company millions on hardware costs if you just knew how to configure your software in the right way.

So let's start with the CPU. Fist off, download CPU-Z for yourself to get the information about your processor that you're running. http://www.cpuid.com/softwares/cpu-z.html, the link should be on the right-hand side.

So if you look at the above screen shots of my laptop, you'll see that I have 2 cores, and with hyper-threading enabled, it gives me 4 threads (Logical CPU's), if hyper-threading was disabled it would only give me 2 threads. When hyper-threading is enabled, and it increases your Threads, then it assigns some of your L3 cache to those threads, so if I have 4Mb of L3 Cache, it will distribute it among each of the threads, so every thread will get 1Mb of L3 Cache to use, but with hyper-threading disabled, it will give me 2 Threads, with 2Mb of Cache each.See my diagram below.

The reason why this is important to know is that when you compare the differences in OLTP (Online Transaction Processing) databases, and data warehouses / marts in the BI space, you want more threads to process your real-time incoming data by having Hyper-Threading enabled. BI systems require larger data volumes to be processed less frequently, so in that case, it would make sense to have less threads, but give those threads more L3 Cache to process the larger volumes, in which case it will make sense to disable Hyper-Threading, but only if you're doing less frequent loads, you will have to weigh it up and do some tests for yourself when it comes to Real-Time data warehouses.

Hope this will help to clear things up for people that are still debating about which one is better to use when you're developing or working with BI systems, or OLTP systems.

Kind Regards
Carel Greaves