CPU Usage showing 100 Percent

CPU Usage showing 100 Percent

Troubleshooting CPU Usage

This article describes how to troubleshoot the CPU usage issue.

Troubleshooting generally involves the use of a series of steps to isolate and determine the cause. Some of the possible causes include:

* Blocking.
* System resource contention.
* A particular set of queries or stored procedures with long execution times.

- Check for Blocking: Run the command exec sp_who system stored procedure, to see if blocking is occurring.

This output will contain a blk column wherein you need to check the output for any non-zero entries that indicates that blocking is occurring.

Run this procedure periodically to check for blockings, if any.

- System resource contention: Try using various monitoring tool to determine if it’s a system resource issue, such as:

* Try downloading Process Explorer, looking at the threads for sqlservr.exe and figure out who is consuming all of the CPU time?

- Long SQL statements require a large amount of CPU to processes regardless of the actual record amount being fetched.

- I would also look in the SQL Server error log, to see if there are any messages and of course also make sure with sp_who2 that there are no active processes.

Particularly, would watch the CPUTime column, to see if there is any suspect.

- I would try executing the following query to dig out some more clues:

SELECT *

FROM sys.dm_exec_requests a

OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

WHERE session_id > 50

and session_id <> @@spid



- If nothing is currently running on the server, then Open the SQL Profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output).

Profiling should help identify the bottleneck.
we need to look for rows which have a high CPU value.

* RPC: Completed (Under stored procedures)
* SQL: BatchCompleted (Under TSQL)