SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How To Identify CPU Problem?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/11/2012 :  09:27:57  Show Profile  Reply with Quote
Good morning all.

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?

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 12/11/2012 :  09:51:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
The mini-bible on waits: http://www.sqlskills.com/blogs/paul/post/wait-statistics-or-please-tell-me-where-it-hurts.aspx

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.

SQLTeam Yak History: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61012
Go to Top of Page

viperbyte
Posting Yak Master

USA
132 Posts

Posted - 12/13/2012 :  09:05:51  Show Profile  Reply with Quote
Thanks for the info.
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2075 Posts

Posted - 12/13/2012 :  09:38:34  Show Profile  Visit jackv's Homepage  Reply with Quote
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”



Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000