SQL High CPU troubleshooting checklist

1. From Windows task manager check the overall CPU utilization. Collect the details of number of logical processors present on the box.

2. Select * from sys.dm_os_sys_info; (DMV)

3. SELECT * FROM sys.dm_os_performance_counters WHERE counter_name = 'CPU%'

4. If the High CPU is causing by process other than SQL Server process (sqlservr.exe) engage the team which takes care of that process.

5. Open Perfmon and add the below counters:

Process (sqlservr):

% Privileged Time

% Processor Time

% User Time

5.  Run the SQL Standard report to get the list of Top CPU queries:

Right Click on the instance, go to reports> Standard reports

6. Check if SQL System threads are consuming high CPU:

select * from sys.sysprocesses where cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'

Ghost cleanup thread >>>> Check if the user deleted large number of rows

Lazy Writer thread >>>>>>> Check if any memory pressure on the server

Resource Monitor thread >> Check if any memory pressure on the server

Step-1: Identify SQL Instance

Determine if high CPU condition is caused by SQL or Windows. If it is SQL and your server is hosting multiple SQL Instances identify the instance responsible for high CPU.

Open task manager –> goto Performance tab –> click on Open Resource Monitor –> click on CPU tab and click on CPU column to arrange in descending order.

Now check the box against sqlservr.exe process and click on Services below to identify the SQL instance

Step-2: Identify SQL Kernel or User

Open Performance Monitor and add the below counters:

• Processor: % Privileged Time – Percentage of time processor spends on execution of Microsoft Windows kernel commands such as OS activity. (If more than 30% involve Windows Admins)
• Process (sqlservr): % Privileged Time – the sum of processor time on each processor for all threads of the process (SQL Kernel)
•  Processor: % User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server

Step-3: Caused by SQL Kernel

· Divide SQL Server %Privileged Time by number of logical processors to understand if the usage is due to SQL Kernal. If this value is more than 30% involve the Windows Admins. (Process (sqlservr): % Privileged Time / No of logical cpus)

· Potential causes could be antivirus faulty drivers or encryption services.

Step-4: Caused by User

SQL Background Threads:

• Run the below query to find if SQL threads are taking up CPU

SELECT * FROM sys.sysprocesses
WHERE cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'
Ghost clean up thread  –  Check if the user deleted large number of rows
Lazy Writer thread – Check if any memory pressure on the server
Resource Monitor thread – Check if any memory pressure on the server

Query Execution:

• Use SP_WHO3 ( from Brenozar’s tool kit) identify the SPID and check with user executing the query to KILL the process. (You can achieve the same using Adam Mechanic’s SP_WHOISACTIVE)
• If the CPU condition is stable now and you need to find the root cause then run the below query to find the top CPU intensive queries.

SELECT GETDATE() AS "RunTime", st.text AS batch, SUBSTRING(st.text,statement_start_offset / 2+1
,((CASE WHEN a.statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE a.statement_end_offset END)  - a.statement_start_offset) / 2+1)  AS current_statement
, qp.query_plan, a.* 
FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS qp
ORDER BY CPU_time DESC

• Open execution plan and check for operators with high cost
• Check indexes being used and number of rows estimated
• Index Scans / multiple scans
• Operators with high very high rowcount
• Make sure statistics are updated on the referenced tables.
• Check for any Index recommendations in the execution plan
• Check for Implicit Conversions in the execution plan tool tips as below. Implicit Conversion can cause HIGH CPU condition

Incorrect Configurations:

Check the following configurations are according to Microsoft Best Practice recommendations:

• Affinity Mask
• Cost Threshold for Parallelism
• Max Degree of Parallelism )
• Max Worker Threads
• Optimize for ad-hoc workloads
• Priority boost

 I am going to share some of the important Performance Counters for monitoring the SQL Server Performance.

Memory Information Counters: Using this script, you can find the total amount of physical memory on the computer.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'

GO

Page Information Counters:

Using this script, you can find that how long pages stay in the buffer cache in seconds. If the value is more significant, SQL Server does not read from disk, and it serves the request from the buffer only.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page life expectancy'

GO

It shows the value of the number of physical database page reads that are issued per second.
Around 80 per second is normal, but if it is above, you may get a timeout in your application.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'page reads/sec'

GO

It shows the number of requests to find a page in the buffer pool.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'Page lookups/sec'

GO

User Connection Counter:

It shows the number of different users that are connected to the SQL Server.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'User Connections'

GO

Buffer Information Counters:

A greater value indicates that more significant number of requests are satisfied with the data cache and SQL Server is getting queries data from the memory instead of disk.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer Cache Hit Ratio'

GO

CPU Counters:

Using this script, you can find the usage of CPU in percentage.

SELECT *FROM sys.dm_os_performance_counters

WHERE counter_name = 'CPU usage %'

GO