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.
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%.
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.
Best Answer
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