Speaking to our Infrastructure Manager the other day, he mentioned how they found looking at CPU per core helpful because recently there was a problem with 1 core being locked onto, causing CPU issues.
Would you expect SQL Response to show your CPU usage per core, or are you happy with a combined total? Do you specifically assign CPU to a SQL Server instance? Going further, do you ever wish to see how much CPU a particular process is using per core? What other CPU metrics are essential to you?
As always, we’re grateful to hear your thoughts…
4 Comments
I think that if any core is being used at a large % difference over any other that is tasked to SQL Server then it would be a reason for investigation but if the whole CPU with high utilisation is more of a worry. The latter is more of a potential server stopper.
To answer “what is essential?” – the whole CPU with persistent high usage.
Per core should be an option -or better, select cores as a group.
We may need to be exploring issues with instances assigned to specific cores. Finding which cores and processes are ‘runaway’ would help in diagnosing problems.
Would you expect SQL Response to show your CPU usage per core, or are you happy with a combined total?
My Answer: the possibility to analyze CPU utilization per core is very welcome.
Do you specifically assign CPU to a SQL Server instance?
My Answer: talking about exclusive SQL Server boxes, usually all CPU/core are assigned to SQL Server, but talking about non-exclusive boxes is a best practice to analyze how many CPU/core SQL Server can use. Some months ago I need to unassigned 2 cores from SQL Server to be able to troubleshoot high CPU utilization.
Going further, do you ever wish to see how much CPU a particular process is using per core?
My Answer: I don’t think so, but a metric to display a core that has the lower rate usage comparing to others in the same box, could be helpful.
What other CPU metrics are essential to you?
My Answer: basic counters, and thinking about a fine tuning we can use Thread State and Thread Wait Reason to understand better the CPU bottleneck
Many thanks for your comments. Our first concerns will of course be total and SQL Server CPU usage, but it’s very useful to hear your opinions on ‘per core’ and other metrics – we’ll investigate all of these suggestions…
Thanks,
Adam