We had a customer whose SQL Server just seemed "sluggish". PerfMon wasn't terrible but user response time was atrocious. We checked everything and did due diligence and couldn't find anything definitively, you'll have to trust me.
In a fit of panic I started looking at everything in PerfMon trying to spot SOMETHING, ANYTHING that was out of the ordinary. It didn't take long. Under the Processor Object I saw that C1 Transitions/sec were around ~80K/NUMA node. %C1 Transition Time was average ~90%. I'm not a hardware guy, nor do I play one on TV. I did NOT know what a C1 Transition was.
So I started googling. I found nothing definitely on the first few pages so I went directly to HP and Dell for guidance.
“… C-State will lower overall power consumption for processors that spend significant portions of time in the idle state. However, this increases latency when processor operation resumes.”
What this means is that anything which requires a path to the CPU will have added latency when the CPU has to transition out of C(x). This means lower response times. And my numbers show SHS sitting in C1 Transitions (moving in to/out of sleep) at 80-90% of TOTAL PROCESSOR TIME. When the NUMA node/core is in C1 sleep and an interrupt is received (this occurs ~ 80K/sec) it needs at least one tick to get out of C1 before responding to the interrupt and doing real work.
C1 is a HALT state and it stops the CPU main internal clocks. http://www.hardwaresecrets.com/article/Everything-You-Need-to-Know-About-the-CPU-C-States-Power-Saving-Modes/611/2 . HP recommends specifically that it be set to NO C-States for optimal performance.
Yeah, yeah, I know. HP sucks. Dell rocks. So what does Dell think? http://i.dell.com/sites/content/business/solutions/whitepapers/en/Documents/11g-optimal-bios-settings-poweredge.pdf ...
Figure 10 shows that enabling C-states results in MPI latency increase of up to 20% for InfiniBand and 12% for gigabit Ethernet communication. For this reason, enabling C-states may not be appropriate for latency-dependent HPC workloads, such as real-time trading applications used in financial services.
After we adjusted these setting at the BIOS level almost all customer complaints went away.
sql server data architecture