April 16, 2015 at 3:40 am
I have a unusual situation with a client of mine.
There setup is SQL 2012 sitting on VMWare. The original problem I was seeing was very high CPU wait time in queries and CPU was running around 75% spiking to 90 odd percent. I was seeing a high signal wait and cpu queues length of 4-6 the cpu ready performance counter of the VM Host was 0 so the host wasn't struggling to assign threads. My diagnosis was to up the number of vCPU's from 4 to 8.
This has had a dramatic effect on performance CPU Usage around 25 - 50%, no CPU queue, CPU Ready of Host still zero but the signal waits are still above the 10-15% which I usually strive for. They are running between 20-50%. Why are queries reporting they are waiting on cpu to be available when it appears there is capacity.
Has anyone got any ideas I'm not used to performance tuning SQL in VM enviornments so this has been a bit of a learning curve.
Other things I've done are to up the cpu shares for the SQL server to be double all other servers on host and allocate 4GHZ also done the same for memory with shares and allocated 40GB of memory.
April 16, 2015 at 9:31 am
A few questions:
1) How busy is the server?
In my experience it's not all that unusual for signal waits as a percentage to be unusually high if a server's idle, since the total wait time is also low.
If that's not the case (and I imagine it isn't, if utilization is hitting 50% on an 8 vCPU VM), then it could be helpful to see the overall wait stats picture. Perhaps you could take a snapshot of wait stats over the course of a few hours and post that here?
2) In VMWare, if you look at the CPU counters Demand and Usage in MHz, is there a big gap between the two?
I've run a few times into situations where the power management settings on a host resulted in VMs not getting the CPU they were requesting (many Dell servers come with a default of "Balanced" power management or equivalent, for example). The gap between Demand and Usage in MHz is an indication of an issue like this, but Ready will not be affected.
Checking the Latency CPU counter in VMWare is also helpful, since that's an aggregate of several CPU-related waits, and not just Ready.
These are the two big things that jump to mind right now. After some more coffee I may have more feedback.
Cheers!
April 16, 2015 at 12:42 pm
Have you checked the tempdb, congestion there is a possible cause.
😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply