Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is hyper-threading still relevant for SQL Server? Expand / Collapse
Author
Message
Posted Saturday, January 22, 2011 11:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Today @ 4:24 AM
Points: 569, Visits: 1,025
Comments posted to this topic are about the item Is hyper-threading still relevant for SQL Server?
Post #1051983
Posted Sunday, January 23, 2011 2:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 2:37 AM
Points: 48, Visits: 137
The main problem with HT is that the physical core shares registers and processor cache with the "hyper" (virtual) core. This often gives a full context shift with flush when the physical core and the virtual core are working.
SQL Server is one of the most efficient applications on the Windows operating system, and is hurt bad by full context shifts on the core. Like when HT is enabled.
This problem gets worse when the SQL Server is increasing its CPU usage.


/Niels Grove-Rasmussen
Post #1052045
Posted Sunday, January 23, 2011 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:20 AM
Points: 50, Visits: 289
My experience broadly echoes Glenn's recommendation; off for OLAP, on for OLTP. But, as you mention in the article no two workloads are ever the same.

These sorts of scenarios are perfect candidates for comparative testing using RML tools. Any critical system upgrade (hardware, firmware, service pack etc) should be subjected to a test run using a baseline workload, captured from a live system and replayed using OStress. While you’re at it, do a test run with HT enabled and disabled and see what effect it has on your particular system.
Post #1052053
Posted Monday, January 24, 2011 12:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
Small correction:
AMD doesn't implement Hyper-Threading, it implements Two Strong Threads.

Intel combats [the expense of one thread per core] with Hyper-Threading, which allows each physical core to work on two threads. Over-provisioning is assumed, meaning you rely on under-utilization to extract additional performance from each core. This is a relatively inexpensive technology. But it’s also quite limited in the benefits it offers. Some workloads don’t see any speed-up from Hyper-Threading. Others barely crack double-digit performance gains.


[Click on the image for a larger view.]

AMD is trying to define a third approach to threading it calls Two Strong Threads. Whereas Hyper-Threading only duplicates architectural states, the Bulldozer design shares the front-end (fetch/decode) and back-end of the core (through a shared L2 cache), but duplicates integer schedulers and execution pipelines, offering dedicated hardware to each of two threads.
Post #1052202
Posted Monday, January 24, 2011 12:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 5:29 AM
Points: 1, Visits: 16
I work with a business system and it's mainly OLTP but alot of long running adhoc queries goes on. So it's more like a combination with OLAP. Unfortunatley I never get the technician to turn off HT, but I measure SQL waits and in a rather large production environment I tried with MAXDOP 0 and MAXDOP set to the actual physical cores. And the last option gives the least amount waits. Even tried setting MAXDOP values between 0 and actual physical cores, but physical cores, still the best. Well, that's my experience.
Post #1052204
Posted Monday, January 24, 2011 12:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:41 AM
Points: 17, Visits: 13
"and so two threads can be executed simultaneously"

And that's the mistake most people make when it comes to hyperthreading. The two threads on the logical cores don't run simultaneously, but alternately. Each logical core is basically only a set of registers.

The plus in hyperthreading is twofold. First the OS need to do less context switching because more CPU threads are available and context switching is expensive. Second when on thread has to wait for cache, I/O or other things the other thread can utilize the physical core. Maximum gain i've seen is 120%-130% CPU usage on the two cores.

Hyperthreading is OK in a low utilization environment with many threads competing for CPU. In a high CPU environment (such as OLAP) it can even start to work against you as funny scheduling artefacts start to occur. I've seen HT machines decrease performance compared to the same machine with HT turned off with heavy workloads. Even on Intels newest CPU's.

Another thing to watch is that the CPU high water mark is not 100%, but 60%-65% with HT, and you don't know exactly where it is. With CPU target normally 70% or below, this becomes 45% or below with HT turned on.

HT is good stuff on certain workloads and bad news on others. Be sure to benchmark HT on and off for your particular workload.
Post #1052207
Posted Monday, January 24, 2011 4:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: Administrators
Last Login: Today @ 4:24 AM
Points: 569, Visits: 1,025
Many thanks for the feedback so far, and especially to h.berg for correcting my misunderstanding with regard to thread execution.

It's interesting that you mention "...less context switching because more CPU threads are available..." because the increase in the number of threads actually seems quite small. E.g. a quad-core processor with and without HT will have 288 and 256 worker threads respectively (on 32-bit) -- so actually significantly fewer threads per scheduler. Jonathan Kehayias has written quite extensively on this topic, and it's resulting "thread contention" that, as you observe, can cause you trouble if you have HT enabled and a lot of long-running queries in your workload.

Cheers,
Tony.
Post #1052269
Posted Monday, January 24, 2011 4:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:41 AM
Points: 17, Visits: 13
What I mentioned were CPU threads, not OS threads. Scheduling a different OS thread on a CPU thread requires a context switch for the given logical core aka CPU thread. a context switch is an expensive operation, the old OS thread state has to be saved and the new OS thread state has to be restored.

The more CPU threads are available, the less often the OS has to switch a different OS thread on a CPU thread and thus the fewer context switches there are.

On a system with many OS threads that have a low CPU demand per OS thread, like a web server or an OLTP database with many short queries, this is a large part of the reason why an HT CPU outperforms a non-HT CPU with the same number of physical cores.
Post #1052274
Posted Tuesday, May 10, 2011 7:10 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:42 PM
Points: 870, Visits: 2,402
I apologize for coming in late, but I've got a nice new server sitting around with modern Nehalem CPU's and scores of GB of RAM, and a limited time window to benchmark Hyperthreading against non-hyperthreading, and I'd like to ask if anyone has a highly (up to 64+ thread) parallel test script available.

If there are some good samples available which can exploit dozens of cores/threads, I'll be happy to run them both with and (hopefully) without hyperthreading, to get some actual numbers on the difference.
Post #1106588
Posted Wednesday, May 11, 2011 1:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:35 AM
Points: 166, Visits: 1,056
I don't have a script for you, but I can give you a really, really big recommendation to enable HT.
Post #1106665
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse