28

Related to: Current wisdom on SQL Server and Hyperthreading

Recently we upgraded our Windows 2008 R2 database server from an X5470 to a X5560. The theory is both CPUs have very similar performance, if anything the X5560 is slightly faster.

However, SQL Server 2008 R2 performance has been pretty bad over the last day or so and CPU usage has been pretty high.

Page life expectancy is massive, we are getting almost 100% cache hit for the pages, so memory is not a problem.

When I ran:

SELECT * FROM sys.dm_os_wait_stats 
order by signal_wait_time_ms desc

I got:

wait_type                                                    waiting_tasks_count  wait_time_ms         max_wait_time_ms     signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
XE_TIMER_EVENT                                               115166               2799125790           30165                2799125065
REQUEST_FOR_DEADLOCK_SEARCH                                  559393               2799053973           5180                 2799053973
SOS_SCHEDULER_YIELD                                          152289883            189948844            960                  189756877
CXPACKET                                                     234638389            2383701040           141334               118796827
SLEEP_TASK                                                   170743505            1525669557           1406                 76485386
LATCH_EX                                                     97301008             810738519            1107                 55093884
LOGMGR_QUEUE                                                 16525384             2798527632           20751319             4083713
WRITELOG                                                     16850119             18328365             1193                 2367880
PAGELATCH_EX                                                 13254618             8524515              11263                1670113
ASYNC_NETWORK_IO                                             23954146             6981220              7110                 1475699

(10 row(s) affected)

I also ran

-- Isolate top waits for server instance since last restart or statistics clear
WITH Waits AS (
   SELECT 
        wait_type, 
        wait_time_ms / 1000. AS [wait_time_s],
        100. * wait_time_ms / SUM(wait_time_ms) OVER() AS [pct],
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS [rn]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE',
    'SLEEP_TASK','SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH',
    'BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE',
    'FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN'))

SELECT W1.wait_type, 
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold

And got

wait_type           wait_time_s     pct  running_pct
CXPACKET              554821.66   65.82        65.82
LATCH_EX              184123.16   21.84        87.66
SOS_SCHEDULER_YIELD    37541.17    4.45        92.11
PAGEIOLATCH_SH         19018.53    2.26        94.37
FT_IFTSHC_MUTEX        14306.05    1.70        96.07

That shows huge amounts of time synchronizing queries involving parallelism (high CXPACKET). Additionally, anecdotally many of these problem queries are being executed on multiple cores (we have no MAXDOP hints anywhere in our code)

The server has not been under load for more than a day or so. We are experiencing a large amount of variance with query executions, typically many queries appear to be slower that they were on our previous DB server and CPU is really high.

Will disabling Hyperthreading help at reducing our CPU usage and increase throughput?

9
  • 1
  • Keep in mind that CXPACKET doesn't mean that there is a lot of time waiting for processes to be merged together. CXPACKET means that the thread is waiting for another thread to finish its processing. You need to look at a specific query that has a thread in the CXPACKET wait and see what other threads are waiting on besides CXPACKET. It is usually IO or network. In the output above you are waiting on latches and being descheduled. Some query needs to be tuned, or you need to see why the latch's are being taken.
    – mrdenny
    Oct 25, 2010 at 17:11
  • In our case, CXPACKET was high as the other threads were just reading excessively from cache (20 million logical reads per query). Our case, again, was a bad anti-semijoin with a partitioned table which was only 700K rows.
    – ozamora
    Oct 25, 2010 at 19:24
  • @mrdenny, yeah the high latch wait time is concerning we are investigating it at the moment. Oct 25, 2010 at 23:20
  • 1

8 Answers 8

10

I still feel that testing your specific workload, as per the original answer, is the only way to be sure. It's not an ideal answer when you're trying to tune a production system (so I'd ask if it was possible to get an identical testbed in systems where both performance and availability really matter) but it's the only one I'm really comfortable with.

We can talk about the theory of whether or not Hyperthreading should hurt or improve things in general (I find it to be more likely to hurt than help on servers so for a "generic" deployment I'd probably disable it), but there is only one way to see for sure if it's going to make a difference in your specific case, and that is try it and see.

7
  • 3
    Note I did not downvote, we need all the help we can get, however we would like to avoid stabs in the dark on a production system. I want to make sure we gathered enough diagnostics prior to making the call on playing with this setting. Oct 25, 2010 at 6:28
  • 3
    I'm sure you do want to avoid 'playing' with a production system, in an ideal world we'd all have test environments identical to production for that reason. I agree with not wanting to change production on speculate. However, I stand by my answer: Testing specific workloads is an important part of any deployment and anyone who tells you different is a charlatan. For me, all signs point to hyperthreading being a problem here but we can talk about things all day and all night and there's still only going to be one way to know for sure.
    – Rob Moir
    Oct 25, 2010 at 6:33
  • 5
    Upvote here - I agree with the answer. General answer is: Turn Hyperthreading off. More specific answer is: It depends on the specifics and MUST BE TESTED.
    – TomTom
    Oct 25, 2010 at 7:01
  • 1
    Oddly enough, I think this is the best answer to accept, mucking around with maxdop settings can lead to lots of trouble, nehalem cpus are much faster than the core based xeons even at mildly slower clock speeds, I find the l2 cached arguments a bit of a red herring cause the l3 cache is so much bigger. As an addendum see: blog.stackoverflow.com/2010/10/database-upgrade , if anybody is seeing more than 20% percent hit / gain ... its probably not due to HT. Oct 30, 2010 at 10:04
  • I've had the opposite experience to @TomTom and @Robert. I've found that HT on is usually 10-15% better than off. The occasion where turning it off improves performance has been rare indeed. Jan 7, 2011 at 14:10
12

I agree that

  • at best the recommendation is "try HyperThreading on your workload and see what happens". We are doing this right now as I type, and.. it ain't good!
  • you should probably always start with HyperThreading disabled, as that is safest

It looks like we should be tuning two things:

  1. MAXDOP (Maximum Degrees of Parallelism). Everything I read indicates that having this unbounded is probably a bad idea, and the Microsoft documentation says:

    Setting this option [MAXDOP] to a larger value [than 8] often causes unwanted resource consumption and performance degradation.

    anything higher than 8 is not generally recommended .. so I set it to 4 for now. It was zero (unbounded) initially.

  2. Cost Threshold for Parallelism. Apparently the default of 5 here is considered a pretty low default according to a few SQL MVP posts I've found -- we can tune it up to reduce how much parallelism is even attempted by the scheduler.

But honestly these feel like workarounds; I think the true solution for our workload (full-text index heavy) is to disable HT.

7
  • 4
    MAXDOP also cause issues with HT as it might try to execute two threads on the same CPU if you have say, 8 cores and 16 threads, and your maxdop is set to 10. Generally 1 MAXDOP per logical processor should be the max. And executing two threads on the same CPU for the same process is kind of pointless. Oct 25, 2010 at 10:28
  • 2
    @Farseeker that only happens if you don't have a HyperThreading-aware operating system. Windows newer than 2000 is aware of it. Oct 26, 2010 at 16:48
  • its worth noting that these maxdop overrides were causing only trouble. default was just fine for us Oct 30, 2010 at 9:54
  • 2
    Standard version of SQL Server maxes out at MAXDOP of 4 anyways when left unbounded. Need Enterprise to go higher than that. We've had some workloads that have gone fastest with MAXDOP of 1 (non-HT box, running multiple 8 core AMDs)... Jan 7, 2011 at 14:13
  • 1
    @Brian Knoblauch - I know this over a year later, but I ran across this "Standard version of SQL Server maxes out at MAXDOP of 4 anyways when left unbounded" any chance you can point me towards some documentation. We are currently talking of using MAXDOP at work but not sure what to set it at. This basically means 4 is the same as unbound correct? Jan 17, 2013 at 14:35
9

Anandtech found that with the pure read load, it hurt a little, and with a write heavy load, it was a bit of a win. I've not seen anything to make me think it is going to get you a hit much worse than -5%, or a win much better than 15%. Note what with a Atom, it is a huge win, but that is a very odd cpu.

All you changed was the cpu? You went from 12MB cache and 4 threads, so 3MB of cache per thread, to 8 MB of cache, and 8 threads, so 1MB per thread. Now, that is oversimplifying, but I bet that is what is killing you, you used to run queries in cache, and now run them from RAM because they need more than 1MB but less than 3MB. Turning off HT will probably help, but I'd go back to the old CPU. Turn off HT, and you get 2MB per thread, but if your workload thrashes with that much, it will not help. It may well be that the old 12MB cache cpu is hugely faster for your workload.

I would try turning HT off, and see if that is an improvement, but I suspect that cache is king for your work load, and you may well need to go back to the 12 MB chip.

2
  • 3
    The L2 cache per core observation is a massive oversimplification, since the CPU is one full generation ahead (Nehalem/Core i7 vs Core 2 Quad class). Oct 25, 2010 at 8:07
  • @Jess, @Ronald, and Nehalem has little L2 cache. The bulk is L3 which is shared across cores. Oct 26, 2010 at 16:51
7

Hyperthreading is, at best, just a way of abstracting task switching away from the operating system and placing it on-die, with direct access to the L1 and L2 cache, which makes task switching a crapload faster.

Testing with VMWare have indicated that disabling HT made no discernable difference under standard load, and a 5% increase under heavy load, due to the fact that ESXi is smart enough to know the difference between the "real" thread and the "fake" thread (there's a lot more to it than that, but that's in laymens terms). SQL Server 2005 isn't quite that smart, but it combined with an up-to-date operating system there should be little advantage to disabling HT.

All that said, I agree with Ronald that it's most likely going to be your L2 cache. A 33% drop in cache size is substantial, and when we spec our SQL Servers we always go for cache over raw clock speed every time.

3
  • Can you set affinity externally so that the right 4 cores get ignored by SQL? Oct 25, 2010 at 11:41
  • 3
    Generally you'd set affinity to each other CPU thread, but just as long as the MAXDOP is set correctly I see no reason to be setting affinity at all. With HT though the first thread to get hit on a CPU becomes the "main" thread, and the 2nd thread is the "HT" thread. There's no real "main" and "ht" threads though, because it's whichever one got there first, and then when they task switch, the order is reversed. Oct 25, 2010 at 20:10
  • Nehalem-based CPUs have VERY, VERY LITTLE L2 cache, most of it L3 shared. Oct 26, 2010 at 16:50
7

Based on my experience, HT was making I/O operations take forever on my of my active nodes on a Windows 2008 R2 Cluster (running SQL Server 2008 R2). An interesting fact was that it was neither reflected in the wait stats nor in the pssdiag I ran for Microsoft support.

The way I noticed low I/O was just by watching the OS counters for physical disk. As Sam pointed out, I wrote about it here and here

If you do NOT experience I/O problems and are CPU bound I suggest you start this way:

Pinpoint which processes and T-SQL blocks are causing the most CPU utilization. In our experience, after we fixed the problem with I/O (by turning HT off) we identified code that was performing horribly in 2008 R2 and doing fine in 2005. I wrote about it here.

While under high load, run Adam Machanic's sp_whoisactive. You can download it from here. We were experiencing a very high CPU utilization due to the excessive amount of logical reads (20 million per query) due to a really bad plan. Our processes were performing anti-semi joins with tables that were partitioned.

My next recommendation is to run profiler to identify a set of T-SQL code that are both high in CPU and I/O logical reads.

With the steps above we were able to tune the offending processes and go from 85% sustained CPU utilization to almost nil.

Good Luck and please feel free to drop me a line if you find a fix as I would like to add the case to my blog.

Thanks

Oscar

7
  • 1
    +1 for the profiler, saved me many a time once a trouble spot has been identified Oct 25, 2010 at 20:13
  • +1 thanks for all your suggestions, tuning our SQL to a reasonable level is a total nightmare, we depend on fulltext quite heavily for our dealings with tags, quite often we are looking for a lists of items in particular tags so we grab the whole set and filter it down. For example, getting a list of questions with tags [x] and [y] ordered by date involves pulling massive amounts of data from the fulltext and then a massive join. Oct 25, 2010 at 23:04
  • Understood. Grab one sample and run it with statistics IO ON and see if you can pinpoint any table with the most logical reads. Again, we were doing just fine in 2005 and really bad in 2008 R2. If you just find high CPU utilization and have a high CXPACKET wait, try first by increasing the Cost Threshold for parallelism to 10, 15 or even 20.
    – ozamora
    Oct 26, 2010 at 0:27
  • If nothing else helps out, offline the DB, turn off HT, and go from there. Good luck
    – ozamora
    Oct 26, 2010 at 0:28
  • sp_whoisactive is a pretty awesome tool, love the way the queries are clickable Oct 27, 2010 at 3:56
2

Unfortunately, I don't think you are going to get any more definitive answer than "try turning hyperthreading off and see if that helps".

Despite the helpful answer from Jonathan in my original thread (which you linked in your question), I was never able to get any definitive evidence about the impact of HT on the specific servers I was investigating. In my case, the servers were already scheduled for replacement, so we simply let those replacements "take care of the issue" so to speak.

My advice:

Try a server-level MAX Degree of Parallelism setting of 1. Parallelism on SQL is most useful for larger, longer running queries anyway, and your load (I assume) consists of a massively high number of smaller queries anyway. This should entirely eliminate CXPACKET waits. This could make certain individual queries run slightly longer, but should allow more "throughput" of total queries on the server.

I've had good results doing this on OLTP servers. Other kinds of servers (reporting servers, processing servers, data warehousing) definitely need the MAXDOP set higher.

And just to be clear, this setting would still allow SQL to use multiple threads for each individual table in a JOIN, so you're not really eliminating parallelism entirely.

At least worth a try, since this setting change takes effect immediately and doesn't even require you to restart the SQL service: http://msdn.microsoft.com/en-us/library/ms181007.aspx
This means you could switch it back immediately if things started going to hell.

Turning off hyperthreading in the BIOS would require a full server reboot, so is a bit more risky.

2

Whether HT is good or bad is hard to pin down.

It really does depend on the server load pattern based on experience and reading. That is, when it affects performance it does so badly: otherwise you don't notice it.

The theory I read was that the threads share cache which means under adverse conditions each thread can overwrite the other thread's cache. If you don't have much parallelism, or your load is many short queries, then it may not affect you.

I've tried with MAXDOP and processor affinity (back in my last real DBA role on SQL Server 2000) but could never find anything conclusive: but only for my shop at that time.

As a quick test, you can set processor affinity to use only physical cores (the lower numbers) and see what happens.

However, at most you lose half your cores. Nowadays that may not matter compared to what I was playing with a few years ago when it was 2 vs 4 or 4 vs 8. Now it's 8 vs 16 or 16 vs 32.

Edit: A test by Slava Oks

7
  • are the cores 0-3 physical and 4-7 logical? Is that how it works? We couldn't tell, and I couldn't figure out any tool to let me know.. Oct 25, 2010 at 22:59
  • 2
    @Jeff Atwood: I'll find more later. I have read it somewhere.... For now: support.microsoft.com/kb/322385
    – gbn
    Oct 26, 2010 at 4:47
  • That KB article pretty much sums it up.
    – pauska
    Oct 26, 2010 at 10:44
  • Although that KB article does contain some useful info, it doesn't seem to directly answer Jeff's question of how exactly the logical processors are mapped to physical ones. My brain fried about half way through, but hopefully this INTEL article should give you what you need to figure out the mapping: software.intel.com/en-us/articles/… also see software.intel.com/en-us/blogs/2009/12/21/… with its associated links.
    – BradC
    Oct 26, 2010 at 15:02
  • @Jeff Atwood, @BradC: Lordy, hard to find. See this: it relies on Intel recommedations. SQL Server will use underlying Windows enumeration download.microsoft.com/download/5/7/7/….
    – gbn
    Oct 26, 2010 at 15:54
0

For the record, we also had unexpectedly bad performance after a server upgrade. It turned out to be due to issues with the BIOS and CPU power saving. The default setting on the server (HP) was to ignore the OS control of CPU speed and use its own algorithm. Changing this to OS control, and updating the BIOS, resulted in significant improvements. There were some release notes (can't find them now) that there was a BIOS bug that was locking the CPU at the lowest performance state.

https://serverfault.com/a/196329/6390

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .