–by Ginger Keys
Recently, a client of mine contacted me in a panic because CPU was running at 99-100% over an extended period of time, which was affecting everything on the machine.
We’ve all seen it at one time or another. And there are several reasons this could be happening. It could be SQL consuming most of the CPU, or it could be something outside of SQL. As a DBA, you don’t want to spend a lot of time troubleshooting high CPU usage if SQL isn’t the culprit. If the issue is caused by processes outside of SQL, you can take a high level look around, but should probably defer that to your Sys Admin or your IT team. If it is SQL that is causing the issues though, we need to dig in deeper.
This article discusses basic CPU troubleshooting. We will take a very ‘high level’ approach to a topic that can potentially get very in-depth and complex. The goal is not only to provide immediate relief but also to help prevent future stress to your CPU caused by SQL.
Is it SQL or System Processes causing high CPU Usage
The first step is to determine if it is SQL or not causing CPU pressure. There are several ways you can make this determination.
1. Task Manager/Resource Monitor
The quick way is to open Task Manager and/or Resource Monitor, and look to see if sqlservr.exe is the culprit. Be cautious when looking at task manager to troubleshoot, as this tool records data once per second. So task manager will record spikes in performance (which is normal), instead of averaging it out over a longer time span (which is a better indicator of stress).
2. Perfmon Counters
You can also determine if SQL is the culprit for high CPU usage by using Performance Monitor (Perfmon). Unlike task manager which records data once/second, you can adjust Perfmon to poll the data every 3-5 seconds to get a better indication of CPU stress. There are four counters you will want to look at:
3. Query DMVs
Another method to find out what is causing high CPU usage is to execute the following DMV:
–Get CPU Utilization % from SQL, NonSQL (System), and Idle
DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
SELECT TOP 10 record_id
,dateadd(ms, – 1 * (@ms_ticks_now – [timestamp]), GetDate()) AS EventTime
,100 – SystemIdle – SQL_CPU_Use AS NonSQL_CPU_Use
SELECT record.value(‘(./Record/@id)’, ‘int’) AS record_id
,record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)’, ‘int’) AS SystemIdle
,record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)’, ‘int’) AS SQL_CPU_Use
,convert(XML, record) AS Record
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE ‘%<SystemHealth>%’
) AS x
) AS y
ORDER BY record_id DESC
The query results break down CPU usage as SQL usage, NonSQL (or System) usage, and Idle. If the SQL_CPU_Use has predominantly higher percentages than NonSQL or Idle, then we know that SQL is causing the pressure on CPU.
System Process causing high CPU usage
If after your preliminary investigating, you discover that SQL is not causing CPU pressure, but rather it is a system issue, the problem could be any number of things. It’s good to have a general understanding of these other issues, but as a SQL DBA, you may not be able to spend a lot of time troubleshooting these. Some system, or non-SQL issues that could cause CPU stress are:
2. Virtual Environment
3. CPU Power Settings
4. Hardware & Software Interrupts – these should be looked at over a period of time, (at least 5 seconds per sample over 30 minutes or more), and should also be interpreted in relation to a baseline of what’s normal for your system
5. Antivirus Scans – temporarily disable anti-virus software to rule this out, or locate the antivirus process in task manager/resource monitor.
6. SvcHost.exe – this is a generic process name, and includes individual services that Windows uses to perform various tasks. There can be multiple occurrences of this process running on your computer at the same time.
SQL causing high CPU usage
If you determine that it is definitely SQL causing the CPU stress, you first need to find the problem and correct or stop it. After you provide some immediate relief to your system, you then need to troubleshoot the issue so that you can make necessary adjustments and prevent it from happening in the future.
First you want to find what in SQL is monopolizing the CPU. To do this, look at the currently executing queries along with the CPU usage. Run this script to identify the queries using the most CPU time:
,requests.wait_time / (1000.0) ‘wait_time(sec)’
,requests.total_elapsed_time / (1000.0) ‘total_elapsed_time(Sec)’
,Substring(sqltext.TEXT, (requests.statement_start_offset / 2) + 1,
WHEN – 1 THEN Datalength(sqltext.TEXT)
END – requests.statement_start_offset ) / 2
) + 1) AS statement_text
FROM sys.dm_exec_sessions AS sessions
INNER JOIN sys.dm_exec_requests AS requests ON requests.session_id = sessions.session_id
CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) AS sqltext
WHERE requests.session_id != @@SPID
ORDER BY requests.cpu_time DESC
When you identify the query consuming the most CPU time, you should be able to observe the TSQL command that SQL is processing, the user issuing the command, and determine whether it can be safely terminated or not.
This provides historical (from the time that the SQL instance started), cumulative data and records waits related to the workload of your server. We can use this DMV to investigate the most prevalent wait types over a period of time, and specifically see CPU waits (signal time) which might indicate CPU pressure. Since this data is cumulative, current waits might not be evident and adjacent samples should be differenced to determine actual resource and signal wait times. It is best to track these statistics over time and establish a baseline on which to compare and look for anomalies.
3. Activity Monitor
Using Activity Monitor in SSMS displays both DMVs above in an easy to read report, but can create a performance hit to run it…maybe not a good idea if your CPU is already under extreme stress.
4. Stored Proc / Query
— Get CPU utilization by query
SELECT TOP 10 st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC
Some of the steps you might need to take in order to improve performance for these queries are:
— Get CPU utilization by database
(SELECT DatabaseID, DB_Name(DatabaseID) AS [Database Name], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
WHERE attribute = N’dbid’) AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],
[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]
WHERE DatabaseID <> 32767 — ResourceDB
ORDER BY [CPU Rank] OPTION (RECOMPILE);
This information should be gathered over a period of time, to determine if it is a regular occurrence. If you see that a database is consistently using most of the CPU resources, you can
6. Excessive compilations/recompilations
When a query is submitted to SQL, it has to create or reuse a query plan. Creating a query plan can be very CPU intensive, so reusing them is easier on the CPU. Query plans may have to be recompiled if the object schema changes, or if the statistics change on the underlying table or database.
You can view these metrics through Perfmon using these counters:
The ideal value for Re-compilations per second is zero. And the ideal value for Compilations is less than 25% of SQLServer:SQL Statistics: Batch Requests/sec.
If your compilations or recompilations are at a higher than optimal number, consider the following:
7. SQL Server Configuration
Troubleshooting CPU issues can be a very painstaking process as there are multiple combinations of issues that can be causing stress. The methods outlined above provide a basic approach to troubleshooting CPU pressure. It could be hardware, software, system, SQL, web, network, and a host of other problems, as well as a complicated combination of these! As a DBA, your main concern is to find and correct any SQL related issues causing this pressure on the CPU.
If you would like assistance configuring, maintaining, or monitoring your SQL Server and instances please contact us at SQLRx@sqlrx.com. We would be happy to answer any question you have, and would be interested to hear about your SQL experiences as well!
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!