CPU on SQL server

• sqlfriend

  SSC Guru

  Points: 52792

  More actions

  April 15, 2013 at 10:25 am

  #394588

  Our .net application runs slow from a couple of weeks ago, it is using the SQL database, I see the CPU usage on SQL server sometimes goes up to 96% above, how can I troubleshooting this to see what is using the CPU?

  Thanks

• Neeraj Dwivedi

  SSCertifiable

  Points: 6768

  More actions

  April 16, 2013 at 1:59 pm

  #1607033

  Use Activity monitor to see what is going on when you see CPU jumped to 96%.

  Also I would recommend using Adam Machanic sp_whoisactive, to see what is going on.

  You can download code from here.

  http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 23, 2013 at 6:59 am

  #1609030

  sp_WhoIsActive is a great tool for seeing what is happening at a particular moment. You can also have it capture its results to a table and schedule it which is nice. Glenn Berry has some great diagnostic queries that inspect the plan cache to show you which queries in a particular database have used the most CPU, I/O, memory, etc. They are great for finding those problem queries.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato