There still seems to be a lot of fear, uncertainty and doubt, in the community, with regard to use of Hyper-threading (HT) with SQL Server workloads. HT is a technology designed to improve 'parallel execution' by presenting to the operating system two logical cores for each physical core. This means that instead of one scheduler per processor core you get two, and so two threads can be executed simultaneously. Early implementations of HT were fraught with problems, from a SQL Server perspective, and it was common for DBAs to simply disable it, across the board. This, taken with the advent of processors with high numbers of true, physical cores, would seem to make HT increasingly irrelevant. Nevertheless, the fact is that the new generation of processors from both AMD and Intel implement "new generation" HT and to ignore it completely is probably a mistake.

So, how and when should HT be used? It's certainly very easy to get confused. When I read in Glenn Berry's forthcoming SQL Server Hardware book, words to the effect that "as a general rule, Hyper-threading should be disabled for OLAP-style workloads, and enabled for OLTP-style workloads", I called him on it as it struck me as the wrong way round. Surely, it is OLAP-style workloads, characterized by complex, long-running queries, which will benefit most from parallelization?

Well, yes and no, and it depends on exactly what you mean by parallelization. It's certainly true that, for an OLAP workload, query execution performance will benefit from allowing the query optimizer to 'parallelize' individual queries. This is the process where it 'breaks down' a query, spreads its execution across multiple CPUs and threads, and then re-assembles the result. For an ideal OLAP workload, it would be typical to leave the MAXDOP setting at its default value of zero (unbounded), allowing the optimizer to spread the query execution across as many cores as are available.

Unfortunately, it turns out that this only works well when you have a high number of true, physical cores (such as offered by some of the new AMD Magny Cours processors). Complex, long-running queries simply do not run well on logical cores, and so enabling HT tends to have a detrimental impact on performance.

In contrast, an OLTP workload is, or should be, characterized by a large number of short transactions, which the optimizer will not parallelize as there would be no performance benefit; in fact, for an ideal OLTP workload, it would be common to restrict any single query to using one processor core, by setting MAXDOP to 1. However, this doesn't mean that OLTP workloads won't benefit from HT! The performance of a typical, short OLTP query is not significantly affected by running on a logical core, as opposed to physical core, so by enabling HT for an OLTP workload we can benefit from 'parallelization' in the sense that more cores are available to process more separate queries, in a given time. This improves capacity and scalability, without negatively impacting the performance of individual queries.

Of course, in the real world, workloads stray from the "ideal" with alarming regularity. In such cases, it's certainly sensible to limit, via MAXDOP, the number of cores that can be used for parallelizing individual queries to no more than half the number of true, physical cores.

I'd love to hear from readers with experience of using HT with SQL Server workloads. Do you generally agree that HT teds to be detrimental to OLAP-style workloads and beneficial to OLTP?

Cheers,

Tony.