asked on

Tracking Down High CPU Utilization on SQL Server 2008

We have a SQL 2008 Server that will max out the processor several times per day. Task Manager clearly shows that SQL server is the program using all of the resources but we do not know exactly which DB, Job or Querry inside SQL is actually the culprit and our SQL knowledge is fairly basic.

How can we determine the function of our SQL server is actually consuming all of the CPU at different times throughout the day?

Thanks!

ASKER CERTIFIED SOLUTION

edtechdba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EvilPostIt

The below will show you what has been taking high CPU within the instance. Although this is under the caveat that it still reside in the procedure cache of SQL Server.

select * from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle)st
order by total_worker_time desc

Open in new window

Alternatively you could run SQL profiler to see what queries are generating high CPU.

Anthony Perkins

I would highly recommend Adam Machanic's sp_whoisactive script:
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

This will show you exactly what is running in a digestible format.

Tony Giangreco

It help if you take the following steps to determine what's happening when the high utilization happens:

1. Setup logging for all user sql executions and include the time-stamp.
2. Modify your any virus scan schedule so it runs late and not during your backup window
3. Run disk management and make sure the hard drive is optimized
4. Open task manager and check the swap file size
5. Check the event logs and resolve issues that may be impacting performance.
6. Run the many best practices wizards Microsoft has to optimize the server.

Hope this helps!

jonnidip

I would also consider monitoring your instance with a profiler (in SSMS --> Tools --> SQL Server Profiler).
You will be able to know what are (if any) the queries that are consuming so much cpu.
I would start it without db filter, but with (for example) duration > 100.
I think in a short time you can find what is really hitting the cpu peak...

Regards.