8

BACKGROUND I've recently been looking into some fairly high CXPacket wait times recently which has had me using SQL Sentry to monitor processor activity pretty closely.

One thing that I've noticed as a result is that we have massive spikes in context switching. Below is a 5 minute sample but this pattern is very common throughout the day.

Context

As you can see it spikes pretty regularly. Now my understanding of this would lead me to believe that this would be the result of CPU pressure. However during that time barely get over 60%.

Processor

After some research this led me to believe that this is happening as a result of hyper threading. I know I'd read earlier some of the dangers of hyper threading. However that was written a looooong time ago.

To make a long story short. Is hyper threading likely to be the culprit for this spikes in context switching? Is it possible that context switching is negatively impacting my parallel queries? Should I disable hyper threading in my environment?

UPDATE although this specific thing is happening in my environment, the question at it's core is more universal. How impactful can high levels of context switching be on parallel queries? Can hyper threading cause this sort of issue?

Ultimately most of what I find on the internet suggests that hyper threading and SQL Server are not good friends, however most it that info is extremely dated.

My System There were a lot of configuration questions so I'll address those here so they can be ruled out. We have the power settings on performance at both the OS an bio level. Our Maxdop is set to 8 and the cost threshold for parallelism is 25. We have 32 logical cores and 16 physical. Also this is for the most part a data warehouse load scenario.

  • 11
    Unless your hardware is really old, first I would consider lowering MAXDOP and/or raising cost threshold for parallelism. It may be that you have way too many shorter/smaller queries that are going parallel. (And I think disabling HT might not have the positive impact you expect.) – Aaron Bertrand Apr 20 '16 at 20:10
  • 1
    What is your power plan set to balanced as opposed to high performance? – Kin Shah Apr 20 '16 at 21:16
  • To piggyback on @aaronbertand 's comment. Current Maxdop is 8 for 32 logical cores. I'll have to double check the cost threshold as I don't recall the number off hand. – Zane Apr 20 '16 at 21:56
  • @kin high performance is set currently. – Zane Apr 20 '16 at 21:57
  • 1
    @Zane in windows or in the BIOS? – Tom V Apr 21 '16 at 5:10
1

Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem.

If the server hosts a data warehouse or reporting type of database that receives a low volume of queries but processes large amounts of data, parallelism can substantially reduce the time it takes to execute those queries. In contrast, however, if the server hosts an OLTP database that has a lot of small queries and transactions, then parallelism can kill throughput and negatively impact performance.

Whenever possible, it is best to isolate and troubleshoot the underlying wait type, since this will lead to overall system throughput improvements. Again, the CXPACKET waits are simply a symptom of a problem in most cases, not the actual problem

The sys.dm_os_latch_stats DMV contains information about the specific latch waits that have occurred in the instance, and if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking during query execution, and reducing the 'max degree of parallelism' sp_configure option may be required to resolve the problems.

This TechNet Magazine article is old but it does say to try turning off hyperthreading if you are exceeding the 5000 per sec per processor:

Optimizing SQL Server CPU Performance by Zach Nichter

  • I'm not suggesting that cxpacket waits are a sign of a problem. What I want to know is if hyper threading can cause these spikes in context switches, and if those switches can negatively impact parallel queries – Zane Apr 20 '16 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.