I've been reading and cramming lately on the subject of Performance tuning. Heavy CPU use comes up a lot in many of the articles that I read. It comes up as something to identify first during decision making time as far as how to go about to solve a particular performance problem many times. What method do we use to determine that CPU is stressed? All I know of is clicking on task manager and looking at performance, which according to me is fine because it varies from 0% to 28% throughout the day. Do you folks identify CPU stress in a different way? What do we call this, instead of saying "CPU Stress"? While we're at it, what's this yak thing I see all over everyone's user name? Isn't a yak an animal?
The main one to look for in diagnosing CPU pressure is SOS_SCHEDULER_YIELD. Paul gives guidelines on how much represents an overloaded CPU. If you don't see those levels then you're not likely CPU bound, even if you're at 50% CPU utilization all the time.
A high percentage SOS_SCHEDULER_YIELD does not always mean CPU pressure. Use perfmon counters to cross-check.
Assess response from sys.dm_os_schedulers . From BOL “Returns one row per scheduler in SQL Server where each scheduler is mapped to an individual processor. Use this view to monitor the condition of a scheduler or to identify runaway tasks”
How To Identify CPU Problem?
Posted - 12/11/2012 : 09:27:57
