Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Is hyper-threading still relevant for SQL Server?

By Tony Davis,

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?



Total article views: 506 | Views in the last 30 days: 3
Related Articles




How to "benchmark"/simulate parallell execution of a query

How to "benchmark"/simulate parallell execution of a query


ERROR : The query processor could not start the necessary thread resources for parallel query execution.

ERROR : The query processor could not start the necessary thread resources for parallel query execut...


Parallel Data Warehouse (PDW) benefits made simple

I have heard people say that the Parallel Data Warehouse (PDW) is Microsoft’s best kept secret.  So ...


Parallelism in Simple Queries

Parallelism in Simple Queries

database weekly    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones