Hyper-threading (HT), an Intel technology, allows a single core to leverage unused resources within its architecture. With HT enabled, a single core appears to the operating system as two cores. Typically, this can improve performance and speed. The general consensus in the SQL community seems to be to leave (HT) turned on—unless you see evidence that suggests otherwise. In this post, we’ll review some key considerations to help you understand those “otherwise” circumstances, specifically on a physical SQL box.
The question of whether or not HT should be turned off is worth thorough testing in your environment. There may be technical considerations unique to your infrastructure—for example, your bios or chipset may not support HT. How well different generations of CPUs, hyper-visors, and your unique SQL workload will perform will vary. HT may increase the load on your storage, so if it’s already a bottleneck, you may see little improvement—in fact, your CXPacket waits may see a corresponding increase and you may see performance degradation. When implementing major changes to your environment, it’s vital to test, benchmark, and compare your results.
The kCura infrastructure engineering team has a test-bed that we call The Grid, and we wanted to see for ourselves if our Dell 710, with 2 Intel X5670 processors (6 cores each, 12 hyper-threaded), would perform better with HT on or off.
For our test, we used an internally built utility to run several queries simultaneously and record runtimes (accurate to about a microsecond), testing a moderate load on the system.
Our utility doesn’t scale past a dozen queries or so, but since we set our maximum degree of parallelization to 6, we weren’t that worried about it.
You could also put together a test and run it in Relativity. A little research into your own audit record table—AuditRecord_PrimaryPartition in each workspace database, Action = 28—will allow you to pull back some of your long running search queries. In Relativity 8.1, you can use Looking Glass to pull back long running queries across all workspaces on a single SQL instance. Take a look at the query plan in management studio. Does it parallelize? If it does, then it is a good subject for your test.
Note: For a quick overview of how to check if a plan parallelizes, look for this icon on the plan. The yellow circle with two arrows indicates parallelism.
You can also use kCura’s VARSCAT script to find LRQs in a single workspace. Once you have found a few candidates, try running two long running queries simultaneously from the Advanced Search screen. Try it with and without HT enabled. Most systems require HT to be disabled/enabled in the BIOS. As you run this test, it’s noteworthy that, when you run a search query in Relativity, some behind-the-scenes queries are run and will put additional load on the system.
On our system, we made the following observations when we disabled HT:
Response time increased by approximately 85%, to 35.8 seconds.
• SQL CPU increased by 8 percentage points on average, to 88%.
• Spikes to 100% were more common than before, and lasted longer.
• Processor queue length grew by a comparable amount.
• The SQL server data drive experienced heavier load, with a disk queue averaging 16 throughout the test. Other disks were almost idle throughout the test.
• Probably due to the slowdown in SQL response times (and corresponding workflow slowdown), CPU utilization dropped by 7 percentage points, to 28% on each web server.
Conclusions:
• At least for the queries Relativity executes, it appears that enabling HT on SQL is a good thing.
• When upgrading storage, concentrate on faster storage for the MDF file of this workspace first.
• Turning HT on may help to alleviate performance degradation that is a direct result of being underpowered on CPU.
These results depend heavily on the environment, and benchmarks will be different for your test. Testing is the best way to make a solid determination. It’s noteworthy that there are no additional licensing costs for running these tests in your environment—Microsoft accounts for HT cores in licensing SQL.
Also, if you are virtualizing SQL, this PDF from VMWare helps explain some of the nuances. The HT discussion starts on the bottom of page 20 and there are a couple of good takeaways.
1. VMware balances across logical CPUs.
2. It is all about the workload. Small, discreet requests for processor time, in large volumes, will perform well with HT enabled. In SQL, this can translate into performance boosts when there are lots of waits and latches, or a high volume of discreet transactions.
3. When there are a lot of very long running threads, HT will not offer much improvement, so understanding your workload is critical.
4. A key metric to watch will be transactions per second.
If HT is disabled in your system, the final consideration when enabling HT is to consider the number of physical cores per NUMA node. Generally, you don’t want to set MAXDOP higher than that.
For more information, this article offers a great explanation of how HT and SQL interoperate. As always, feel free to contact us if you have any questions about optimizing your Relativity infrastructure.
