THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: hyperthreading for OLTP queries

My previous post focuses on the performance impact of enabling hyperthreading (HT) on a machine with four Intel Westmere-EX processors on reporting queries. Let’s turn our attention to OLTP queries.

To oversimplify it, reporting queries are generally processed by scanning a large number of pages, whereas quick index seeks are the hallmark of OLTP queries.

The OLTP queries used to check out the hyperthreading impact are the two TPC-C read-only transactions (Order Status and Stock Level), slightly modified to work properly in the test environment. The need to modify these two TPC-C transactions is discussed in an early post. Briefly, it’s because in their original forms they don’t scale properly on this 80-cpu test machine running SQL Server 2008 R2. You can find the modified code of these two transactions in the attachment of this post.

The benchmark described in the same post is used to drive the tests whose results are reported here. If you want to find out more about the benchmark, please check out that post, so I won’t duplicate its description here.

Essentially, with this OLTP benchmark (or workload) I want to see how it behaves when I increase the load level by increasing the number of concurrent users, with and without hyperthreading on a DL580 G7 with four E7-4870 processors (10 cores per processor) and 264GB of RAM. The OS is Windows 2008 R2 Enterprise x64 Edition with SP2 and DBMS is SQL Server 2008 R2 Enterprise x64 Edition (10.50.1600). There is no intra-query parallelism; maxdop is set to 1 in all the cases.

Four test configurations are examined:

  • 40 core with HT. That is 80 logical cpus. HT is enabled in the BIOS.
  • 40 cores without HT. That is 40 logical cpus. HT is disabled in the BIOS.
  • 20 cores with HT. That is 40 logical cpus. HT is enabled in the BIOS.
  • 20 cores without HT. That is 20 logical cpus. HT is disabled in the BIOS.

The following chart shows the results when 200 simulated users are driving the workload concurrently. Note that with 200 users, all the processors on this test system are pushed to ~100%.

hyperthreding_OLTP_queries

Compared with the results for the reporting queries (see the previous post), the performance gain from enabling HT on the 40 cores is very much more marginal with this OLTP benchmark. We see an increase of about 5% from ~7900 batch requests per second to ~8300 batch requests per second. Enabling HT on the 20 cores produces an improvement of similar magnitude (about 7.5%).

Going from 20 cores with HT to 40 cores without HT, however, gives the OLTP queries a dramatic throughput gain of about 67% from ~4730 batch requests per second to ~7900 batch requests per second. We see similar behavior with the reporting queries. Knowing how HT works, I think it is intuitive we expect a big gain, whether or not it should be 67%.

So for both the reporting queries and the OLTP queries that I have tested, the hyperthreading results are positive. So far so good!

Published Friday, January 06, 2012 12:44 AM by Linchi Shea

Attachment(s): TPC-C-read-queries.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Paul White said:

Another good post Linchi, I am following this series with interest.  One small point: I wish you had used build 10.50.2500 (R2 SP1) rather than 10.50.1600 (R2 RTM).  I can't say whether it would have affected the results at all, I just have this general feeling that R2 RTM was not the best.

January 6, 2012 1:53 AM
 

Linchi Shea said:

Thanks, Paul, for pointing that out! I was a bit lazy in keeping up with the SPs and CUs. I'll repeat the tests with SP1 CU4 (2009.100.2796.0). But since you mentioned 10.50.2500, is there any specific reson I should stay with SP1 instead of SP1 CU4?

January 6, 2012 9:24 AM
 

GrumpyOldDBA said:

I apologise if I've missed this but was any parallelism enabled for the oltp test?  

I'm still a little surprised that HT doesn't show more gain, based upon your graph which shows HT makes little difference - which sort of doesn't match your comment about gaining 67% with HT or am missing something obvious?

Great work, thanks.

January 6, 2012 11:01 AM
 

Linchi Shea said:

GrumpyOldDBA;

There is no intra-query parallelism in these OLTP tests. maxdop is set to 1. I should have made that clear. [I updated the post and included the maxdop setting]

The 67% gain is from 20 cores with HT (thus 40 logical cpus) to 40 cores without HT (also 40 logical cpus). So the point is that at the OS and SQL level, you may see the same 40 cpus, their performance power is very different whether they are made up of physical cores or HT.

January 6, 2012 11:55 AM
 

Paul White said:

The only reason for mentioning SP1 was that some/many people prefer only to apply the CUs for specific issues.  Also, I guess SP1 is likely to remain the 'current latest service pack' longer than CU4 will remain 'the latest CU'...if you see what I mean.

Personally, I do try to keep my test instances up to the very latest CU (though I notice my R2 is only at SP1 CU3 right now!) so I'm happy whichever way to choose to go.  R2 RTM just bugs me :)

January 6, 2012 12:53 PM
 

Bob Jessie said:

Hyperthreading? Really? Who makes this stuff up?

January 7, 2012 8:40 AM
 

Yuri Budilov said:

Hi

we tried HT on a Nehalem server with 32 cores (64 HT CPU) with a large SAP Export/Import in July 2011. Which is a far more realistic high load test with a 7 TB SAP database and 512 GB of RAM. Sadly we had a lot of jobs crashing (just reading data, with maxdop enabled = 8, = #cores per each NUMA node) with internal dead-lock on SQL thread schedulers (we used SQL 2008 R2 X64 and default settings for # of worker threads). We submitted the dumps to Microsoft but there was no clear cut reason determined. So it was not the case of better performance but the case of crashed jobs under high load on large tables. So we turned off HT and the deadlocked thread-schedulers problems cleared (many others remained but its off-topic). I can *not* prove that HT was the culprit but what I would like to say that until Microsoft makes SQL Server fully HT-aware *by design* (like they with with by-design NUMA support for SQL 2005) I would NOT recommend using HT in production for large mission systems. If it works for you - its more by luck than by design. This is my 10 cents only. I am sure many will disagee.

January 7, 2012 6:18 PM
 

Linchi Shea said:

Yuri;

It would be interesting to determine whether your problems were more related to the number of processors regardles whether they were HT or not. In other words, would you still run into similar problems if you had 64 cores with no HT? I'm asking, but just wondering.

In this post, my focus is squarely on performance. And as I already mentioned, people should not extrapolate too much because the results were indeed specific to the test setup.

January 7, 2012 7:26 PM
 

Greg Linwood said:

Running a SAP Export/Import isn't very similar to running normal OLTP SAP workload, so I think it's a stretch advising against HT for OLTP generally based on results from an Export/Import.

Do you have any other basis for advising against HT with normal OLTP? fwiw, I don't have a strong opinion either way, I'm just surprised you'd make a general recommendation for OLTP configuration based on an Export/Import load testing.

Personally, I think the entire SQL Server multi-tasking architecture is a patched up, old school design. Why run a co-operative design at the SQLOS level when there's a pre-emptive layer in the host OS? SQL Server should really be embedded closer into Windows & multi-tasking should be done at one layer, in the way that SQL needs it done (eg, a Windows SQL edition where certain cores are assigned for direct management by SQLOS and a few for pre-emptive management by the host O/S). This would eliminate much un-necessary complexity in the SQLOS scheduling system & eliminate much context switching.

January 8, 2012 9:42 PM
 

Tim said:

This is probably because these queries are memory fetch intensive vs compute intensive.  If the problem is compute intensive you can expect larger gains (20-30%), if the query had some complicated arithmetic perhaps these numbers would increase a little.

January 9, 2012 1:53 PM
 

Linchi Shea said:

Knowing how Intel hyperthreading works, I'd agree with your assessment in that it's probably more likely to see a higher impact with a more compute intensive workload. But of course, more compute intensive workloads are not guaranteed to benefit more from hyperthreading. I think if we have to pick one between the two, I'd argue that OLTP queries are more memory fetch intenstive than compute intensive, not always of course but in general.

January 9, 2012 9:07 PM
 

Matthew said:

This HT article and discussion is very interesting. I'd like to thank all contributors publishing there thoughts and experiences.

With our Dynamics-NAV based ERP solution we've made the experience, that we've seen some installations where the request for data took a lot of time (more than 100 ms), though it was a simple "select * from Table with read uncommitted order by PrimaryKey" (* is something NAV unfortunately always does) on less than 100 rows. We tried everything to reduce the duration but nothing worked since we turned off HT. After that the duration for queries went down to zero.

I have really no tangible explanation for it, but I believe it has to do with the NUMA-Support and the distribution of worker and administrative threads (lock management, cache maintenance, ...) in the SQL kernel. HT is more something like a marketing gag than a technical innovation.

January 10, 2012 7:54 AM
 

Greg Linwood said:

Matthew, did you check whether Max Degree Of Parallelism was set on your server prior to turning off HT? Halfing the number of CPUs could reduce query parallelism issues if the query optimiser decides to choose serial plans instead. I have seen many instances of Dynamics-NAV servers suffering badly from query parallelism which, once turned off (MAXDOP set to 1) the overall query performance issues disappear, hence I'm wondering whether you investigated this avenue?

January 10, 2012 6:38 PM
 

Matthew said:

Hi Greg,

we always set DOP to 1. We tried it the other way round as well just to be sure. But in general, a DOP value of one reduces the amount of lockings a lot when you have simple queries like NAV produces. And with HT enabled, I assume that SQL Server is not really HT aware and doubles the number of worker threads hence they need to share a core and have to wait for execution. That fits to the bad query execution because it distributes parts of the execution plan to other worker threads which can't finish their work instantly. But as I said, it's just an assumption!

January 11, 2012 2:59 AM
 

Paul White said:

@Matthew Those assumptions are not correct.  SQL Server doubles the number of schedulers, not worker threads (though the maximum number of worker threads will increase a bit as a consequence).  See http://msdn.microsoft.com/en-us/library/ms187024.aspx (Books Online).

There is a great deal of good information about SQL Server's scheduling architecture online, and in great books like SQL Server 2008 Internals.  I would recommend the following to you:

SQL Server Batch or Task Scheduling (Books Online)

http://msdn.microsoft.com/en-us/library/ms189267(v=SQL.105).aspx

Inside the SQL Server 2000 User Mode Scheduler (Ken Henderson)

http://msdn.microsoft.com/en-us/library/aa175393(v=sql.80).aspx

Platform Layer for SQL Server (Slava Oks)

http://blogs.msdn.com/b/slavao/archive/2005/07/20/441058.aspx

@Greg: Given your comments about cooperative scheduling, I'd encourage you to read those links too!

Paul

January 11, 2012 4:41 AM
 

Greg Linwood said:

Matt, there are many bad things about SQL Server's task scheduling unfortunately..

Paul, thanks for the reading suggestions but I've read those already. Actually, Ken Henderson asked me to review various articles + chapters in his SQL 2000 Internals Guru's Guide & after much work on those reviews he listed me first in his acknowledgements in the Guru's Guide.

Ken was somebody who worked extensively with SQL Server in the field & his perspective was sometimes different from the architects who designed SQL Server. Architects tend to defend how designs are *supposed* to work whereas field engineers such as he & I end up dealing with the consequences of designs & hence end up questioning things.

Task scheduling is a great example of where Microsoft DBMS architects have gotten things TERRIBLY wrong in the past. The story about the Microsoft / Sybase partnership actually centered on this issue - if you follow it through, the case is very clear..

Originally, Sybase wanted to implement a design similar to the current SQLOS model (DBMS layer multi-tasking) but Microsoft was embarking into the commercial server OS market in the early 90s & needed SQL Server to be a killer app which would work closely with & promote WinNT, so they split from Sybase & implemented their idea - a monolithic multi-threaded DBMS which relied entirely on WinNT pre-emptive multi-tasking. This design remained in the 4x & 6x releases but never performed well & was ultimately replaced years later in SQL7 with the "User Mode Scheduler" (UMS) - basically a throwback to Sybase's original plan.

Clearly, Microsoft's DBMS architects got this decision very wrong & a lot of time was lost whilst they sheepishly circled back to Sybase's original design.

However, even after this first huge mistake there was another obvious problem with reverting to the Sybase design in the SQL7 release. Sybase's objective was originally to target multiple OS platforms (OS2, Unix, Windows etc) but Microsoft were only ever going to target Windows NT with SQL Server, so they didn't need a DBMS multi-tasking layer for the same reasons as Sybase. Instead, Microsoft could have / should have redesigned Windows NT microkernel architecture to support SQL Server (its killer app in those days) better.

Many who read this probably won't know that the O/S designers who architected WinNT used to design O/Ss with optional kernels when they worked at Digital Equipment many years earlier.. PDP & Vax O/S systems used to be available in either pre-emptive, co-operative or real-time kernels, so it was definitely possible to provide more focussed WinNT kernels for key apps such as SQL Server. Microsoft's WinNT OS team had done it all before & hence knew exactly how to go about it.

They apparently just thought that pre-emptive would work generically & as a result,  SQL 4x & 6x releases were performance disasters & the decision was eventually made to resdesign SQL Server (UMS / SQLOS) rather than Windows.

As a result, we now have an absurd design where the DBMS does application layer multi-tasking in a co-operative model, to avoid problems associated with the host OS pre-emptive multi-tasking, even though SQL Server runs only a single platform (WinNT). It is obviously not ideal, it is inefficient & is an artifact of bad choices made many years ago.

Throw Hypervisor based Virtualisation underneath the host O/S & you have an even more absurd third layer of un-co-ordinated task scheduling.

Reading articles is good & I've done my share of reading whilst working with SQL Server for nearly 20 years, however one shouldn't always just accept whatever is written. It's also important to question what's written & also how SQL Server is designed. Microsoft doesn't always get it right & needs feedback from the field - my point here is that this is even possible in the big picture stuff such as multi-tasking, which is at the core of what SQL Server does..

January 11, 2012 7:00 AM
 

Paul White said:

@Greg:

That's a rather negative review of history if you don't mind me saying so.  Naturally, it is easy to criticize but in the process it is easy to come off as dismissive of the efforts of the (conscientious and skilled) people involved in those decisions at the time.

There are always multiple competing factors (including commercial and legal ones) in play, and I just don't think it's very constructive to take a "oh, you don't want to do it like that, do it like this!" attitude.

There may be some technical merit in suggesting things like alternative kernels for SQL Server, Exchange, and so on, but I don't think the chances of that happening any time soon are very high, for a variety of fairly obvious reasons.

Paul

January 11, 2012 8:30 AM
 

Matthew said:

@Paul, you're correct. I should have used "scheduler" and not "worker thread". But it doesn't change the assumption in general.

Thanks for the reading suggestions. I know parts of it because I struggled with a horrible problem some month ago. It had to do with the cache mechanism. When analyzing it you get into the scheduling mechanism automatically. The HT experience was some time later. That's where my assumption comes from.

@Greg: Thank you for reminding me of the whole story, it's really an interesting history. I don't have such a vast knowledge about SQL Servers evolution and devolution like you, but in our company we have departments working with SQL Server in Microsoft environments (like me) and Sybase on IBMs iSeries (which I prefer ;)). This is always an inspiring dialogue ;)

Thanks to all!

January 11, 2012 9:40 AM
 

Paul White said:

@Matthew: I see.  If I change 'worker thread' to scheduler, your statement now reads "... doubles the number of worker threads hence they need to share a core and have to wait for execution.".

My worry is that you are thinking of a situation where two worker threads (on different SQLOS schedulers) are competing for exclusive access to a single physical core.  This is not the case in general for two reasons:

(1) The OS will generally not schedule two threads on the same physical core (with two hyper-threads) if another, uncontended, physical core is available

(2) Even where all physical cores have at least one active thread, the second hardware thread on a single hyper-threaded physical core does not necessarily have to wait for the other hyper-thread to yield.  The best introductory explanation I have found to the issues involved here can be found at http://software.intel.com/en-us/articles/performance-insights-to-intel-hyper-threading-technology/

Paul

January 11, 2012 10:09 AM
 

Linchi Shea said:

Paul;

>(1) The OS will generally not schedule two threads on the same physical core (with two hyper-threads) if another, uncontended, physical core is available<

I read this in multiple places as well. But I wonder if you know how to actually check if this indeed the case. I'd love to confirm and see this behavior in action in my own tests. Maybe I haven't tried hard enough, but I have not found a way to verify this. Intuitively, the OS absolutely has to do this at the minimum to be HT aware.

January 11, 2012 10:49 AM
 

Paul White said:

@Linchi,

Yes, multiple places.  The most authoritative that comes to mind is Mark Russinovich's excellent Windows Internals Fifth Edition.  The detailed descriptions there put me off ever attempting to verify the behaviour on a real machine, but I know you like a challenge.  I imagine it would involve using some very low-level tools.

Paul

January 11, 2012 11:46 AM
 

Linchi Shea said:

This is in part a response to a comment by Paul White ( @SQL_Kiwi ) to my previous post on the performance

January 11, 2012 1:51 PM
 

Greg Linwood said:

Paul, I don't mind you saying whatever you like.

Yes, it's a negative story but it's also the truth & I pointed it out as an example of why you shouldn't just blindly read whatever is written or accept things as they are.

January 11, 2012 4:56 PM
 

Linchi Shea said:

Too many context switches per second are considered bad for your database performance. But how many is

January 12, 2012 12:23 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement