SQLTeam.com | Weblogs | Forums

CPU usage of the server is going high. How to handle the situation?


#1

CPU usage of the server is going high. How to handle the situation? What to check and how to troubleshoot.


#2

See what's running and using the most CPU:

select top 100
DB_NAME(dbid), objectid, cpu_time,
total_elapsed_time, logical_reads, [text]
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
where status <> 'background'
and dbid <> 32767
order by 3 desc

This will help if the problem is currently happening. Otherwise, setup sp_WhoIsActive (free tool) and store the data in a table to analyze later. Run it every 30 seconds or whatever makes sense. Logging it to a table: http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/