Troubleshoot SQL Server high CPU issue

We can use SQL query or task manager or perfmon to determine the issue.
Once we have identified that SQL Server process is consuming CPU then we have to next find which inside SQL Server process is consuming this CPU.
For this we need to remember that CPU consumes time in 2 modes.

                         1. Kernal Mode

                         2. User Mode

These 2 modes can be seen using performance monitor by monitoring "%Privileged time"  and "% User Time" counters under the "Process" node.
Remember that "%Privileged time" is not based on 100%.It is based on number of processors.If you see 200 for sqlserver.exe and the system has 8 CPU then CPU consumed by sqlserver.exe is 200 out of 800 (only 25%).

If "% Privileged Time" value is more than 30% then it's generally caused by faulty drivers or anti-virus software. In such situations make sure the BIOS and filter drives are up to date and then try disabling the anti-virus software temporarily to see the change.


If "% User Time" is high them there is something consuming of SQL Server.
There are several known patterns which can be caused high CPU for processes running in SQL Server including

 Some of the most common causes for High CPU in SQL Server are

1 . Query executing causing CPU spike ( In general caused by optimizer picking bad plan)

2. High Compiles and Recompiles ( In general stats change , schema change , temp tables , recompiled all the user defined SP's etc)


3. System threads spiking CPU ( In general ghost clean up , lazy writer and resouce monitor will be caused).

4. Running many traces.

1 . Query executing causing CPU spike :

In general query execution takes long time due to outdated statistics , lack of indexes, server configurations , distributed queries etc .

Run the below query to find the list of currently running queries order by CPU.

SELECT r.session_id
 ,st.TEXT AS batch_text
 ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
   (
    CASE 
     WHEN r.statement_end_offset = - 1
      THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
     ELSE r.statement_end_offset
     END
    ) - r.statement_start_offset
   ) / 2 + 1) AS statement_text
 ,qp.query_plan AS 'XML Plan'
 ,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

Now figure out whether it is singe query or stored procedure causing CPU spike.

1. If the stats are up to date then estimated rows and estimated execution will be approximately same in the execution plan. If there is huge difference then stats are outdated and required update.

2. Rebuild or re-organize the indexes and also create if the indexes are not available.

3. If update statistics or rebuilding the indexes doesn't help you bringing down the CPU then tune the query one by one.

3. If the procedure is causing the CPU spike then

a. Use SET NOCOUNT ON to disable no of effected rows message. It is required only to test or debug the code.

b. Use schema name with the object name if multiple schemas exist in the database. This will helpful in directly finding the compiled plan instead of searching for the object in other schema. This process of searching schema for an object leads to COMPILE lock on SP and decreases the SP's performance. So always its better to refer the objects with the qualified name in the SP.

c. Do not use the prefix "sp_" in the stored procedure name . If you use then it will search in the master database. Searching in the master database causes extra over head and also there are changes to get wrong resulyt if the same SP found in the master database.

d. Use IF EXISTS (SELECT 1) instead of (SELECT * ) to check the existence of a record in another table. Hence EXIST will use True or False.

e. If the query which is spiking linked server query try changing the security of linked server to ensure liked server user has ddl_admin or dba/sysadmin on the remote server.

f. Try to avoid using the SQL Server cursors when ever possible and use while loop to process the records one by one.

g. Keep the transaction as short as possible - The length of transaction affects blocking and deadlocking.Exclusive lock is not released until the end of transaction. For faster execution and less blocking the transaction should be kept as short as possible.


h. Use Try-Catch for error handling it will help full to easily debug and fix  the issues in case of big portion of code.

2. If the system thread is consuming most of the CPU

If none of the SQL queries are consuming majority of the cpu then we can identify if the back ground threads is consuming the majority of CPU by looking at sysprocesses output for background threads.

Select * from sys.sysprocesses where spid<51


Check if you are hitting any of the known issues such as resource monitor may consume high CPU (hot fixes available ) or ghost clean up task uses 100% of the CPU on the idle system in SQL Server 2008 or SQL Server 2005.


Ghost Clean up - Assume you remove some records from a database table which is running in SQL server 2005 or 2008 then the database engine marks these records as ghost records. Then SQL server service starts a Ghost clean up task to complete the removal of the ghost records from the table. In this scenario SQL server uses 100 % of the CPU.


Use the below query to get historical data to find out the query which was cause CPU issue earlier. I have specified top 50 queries which you can also increase it.

SELECT TOP 50 sum(qs.total_worker_time) AS total_cpu_time,
 sum(qs.execution_count) AS total_execution_count,
 count(*) AS number_of_statements,
 qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY sum(qs.total_worker_time) DESC

High Compiles and recompiles will also cause high CPU.

Apart from this SQL Server that uses .NET framework can cause high.