SQL DBA Guru

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. You will need to look for rows which have a high CPU value.

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