SQL Server 2008 High CPU usage

Question

We have a Windows 2008 R2 Server with a SQL Server 2008 in it. That server has multiple .net sites that have SQL server databases on it.

We are currently experiencing an average CPU usage of 95%, and SQL Server is responsible for most of that usage.

I would like to identify which site is responsible for this so we can either optmize it or move it to another server. But I didnt find any direct way of looking this.

I've been looking if I could find:

• The database that is getting the most CPU intensive queries
• The process that is responsible for CPU intensive queries

One thing that also complicates the things is that we have multiple sites and "crons" using the same database. So once I identify the database I would also need to get some hints of which site/cron is responsible for it.

I would really appreciate any help on this as this issue is making our sites really slow...

Thanks

Answer 1

Alternatively you can use the Activity monitor to view the health status of your servers. From there you will be able to single out long running queries, any locks such as row lock, table lock etc etc

http://www.mssqltips.com/sqlservertip/1917/performance-analysis-using-sql-server-2008-activity-monitor-tool/