This article describes the steps to troubleshoot high CPU utilization in SQL Server. If you are experiencing high CPU utilization in SQL Server, you can implement the steps mentioned below to troubleshoot the issue.
Suppose your monitoring system has informed you that the CPU utilization on your SQL Server is at 90% or 100%, and you're left wondering what to do. If this sounds familiar, don't worry. This blog post helps walk you through finding and solving the problem by providing steps on how to resolve SQL Server high CPU performance.
The common reasons for high CPU in SQL Server are as below.
High logical reads
Out of order statistics
Statistics with a lower threshold
Parameter sensitive plans
Missing Indexes
Poorly designed queries
Increase in workload
Verify that process(SQL Server) is causing high CPU usage
The first step is to identify whether a high CPU on the server is caused by the SQL Server process or any other process.
Method 1: Task Manager
You can RDP to the SQL server and launch the task manager. On the process tab of task manager, check the high CPU-consuming process.
As shown below, the SQL Server Windows NT – 64 Bit consumes most of the CPU resources.
Method 2: Ring Buffer
You can query sys.dm_os_ring_buffers and sys.dm_os_sys_info dynamic management views to check the CPU utilization related to SQL Server process and other processes. Take the script from GitHub https://github.com/rajendragp/RajendraScripts/blob/master/CPUUsagewithRingBuffer.sql.
As highlighted below, SQL Server is consuming most of the CPU resources.
Method 3: PowerShell
The following PowerShell script collects the counter data every 60 seconds.
cls
$serverName = "WindowsSQL" #enterserver name
$Counters = @(
("\\$serverName" +"\Process(sqlservr*)\% User Time"),
("\\$serverName" +"\Process(sqlservr*)\% Privileged Time")
)
Get-Counter -Counter $Counters -MaxSamples 30 | ForEach {
$_.CounterSamples | ForEach {
[pscustomobject]@{
TimeStamp = $_.TimeStamp
Path = $_.Path
Value = ([Math]::Round($_.CookedValue, 3))
}
Start-Sleep -s 2
}
}If %User Time >90%: The SQL Server process is causing high CPU usage.
If % Privileged Time >90%: The OS or other application processes are causing high CPU usage.
My SQL Server has a high %user time value that shows SQL Server consuming high CPU.
Method 4: Performance Monitor Counters
You can also use perfmon with the following counters to check the CPU utilization of SQL Server and other processes.
Counter: Process/%User Time, % Privileged Time
Instance: sqlservr
Identify the queries contributing to CPU usage
Once you have identified that the Sqlservr process is causing high CPU usage, we need to identify the queries responsible for it.
Method 1: SP_WhoISActive stored procedure:
We are using Adam Machanic's comprehensive activity monitoring stored procedure to monitor currently running processes. Here, you get the following valuable columns.
The task duration column (dd:hh:mm: ss.sss) shows for how long the query has been running
The sql_text column gives the SQL query running under the session_id.
Login_name: The login running the query
CPU: The CPU assigned for the query
tempdb_allocations: The tempDB allocations for the session_id
blocking_session_id: If the process is blocked with other processes, you can find the culprit SP ID.
If you run the SP with the additional parameter @get_plans=1, it gives the execution plan of the query as well.
Exec sp_whoisactive @get_plans=1
Method 2: Dynamic Management Views
You can also run the https://github.com/rajendragp/RajendraScripts/blob/master/IdentifyQueriesContributingCPU.sql query to identify the queries causing high CPU
Update statistics
Once we have identified the problematic query, you can run the updated statistics for the involved tables. To update the statistics, you can use the UPDATE STATISTICS command.
For example, the following query performs update statistics for all indexes on [Sales].[SalesOrderDetail] table.
UPDATE STATISTICS Sales.SalesOrderDetail;
GOYou often run the Update Statistics for the larger tables, but SQL Server takes only a small sampling percent. In this case, you can check the sample percent with the following script.
SELECT stats.name AS StatisticsName,
OBJECT_SCHEMA_NAME(stats.object_id) AS SchemaName,
OBJECT_NAME(stats.object_id) AS TableName,
[rows] AS [Rows],
last_updated AS LastUpdated,
rows_sampled as [NumberofSamplingrows], modification_counter,
(rows_sampled * 100)/rows AS SamplePercent,
persisted_sample_percent PersistedSamplePercent
FROM sys.stats
INNER JOIN sys.stats_columns sc
ON stats.stats_id = sc.stats_id AND stats.object_id = sc.object_id
INNER JOIN sys.all_columns ac
ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(stats.object_id, stats.stats_id) dsp
WHERE OBJECT_NAME(stats.object_id)='EmployeeDepartmentHistory'Look at the following columns in the output
o Rows: Total number of rows in the table
o NumberofSampleRows: Number of sampling rows
o SamplePercent
o Modification Counter: Number of updates performed after the last stats update
The following DemoTable has a 49% sampling percent for the total 421201 rows.
Therefore, you can run the UPDATE STATISTICS WITH FULL SCAN to update statistics with 100% sampling percent. It helps SQL Server to build a cost-optimized execution plan.
Add missing indexes
If you are still getting high CPU utilization for SQL Server, you can evaluate the missing index recommendations. The following query lists high CPU queries with at least one missing index warning in the execution plan.
We do not need to create all indexes recommended by SQL Server. It does not consider the existing indexes.
The following query generates the index creation script, and you can consider the indexes with the highest improvement measure. Always create indexes in a non-prod database and see if it is helpful in the production environment.
Disable heavy tracing
If you log heavily for XEvent or SQL Profiler trace, it might cause CPU issues. The following query lists all active XEvent or Server traces.
Review these active XEvent or Server traces and see if you need them. Stop any unwanted running traces.
Investigate parameter-sensitive issues
Sometimes, the parameter-sensitive stored procedure or the query might cause high CPU usage. You can do the following to investigate these issues.
Use the RECOMPILE query hint for each query execution.
Recompile stored procedure using sp_recompile procedure.
Drop execution plan of a specific query or procedure using DBCC FREEPROCCACHE(plan_handle). The following query generates the query plan referencing the table Person.Person
SELECT text, 'DBCC FREEPROCCACHE (0x' + CONVERT(VARCHAR (512), plan_handle, 2) + ')' AS dbcc_freeproc_command FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%person.person%'Use the OPTIMIZE FOR query hint that overrides the actual parameter value by using a parameter value that's good enough for most parameter value possibilities. If you are unsure about the optimized value, you can use OPTIMIZE FOR UNKNOWN query hint.
You can use the DISABLE_PARAMETER_SNIFFING query hint to disable parameter sniffing.
Scale-up SQL Server
You might need to scale up to SQL Server resources by adding more CPUs based on the workload requirement. The following query helps find the number of queries that have exceeded a certain threshold of average and maximum CPU consumption per execution(exceeds 200 ms) and have run many times(1000 times) on the system.
DECLARE @cputime_threshold_microsec INT = 200*1000
DECLARE @execution_count INT = 1000
SELECT
qs.total_worker_time/1000 total_cpu_time_ms,
qs.max_worker_time/1000 max_cpu_time_ms,
(qs.total_worker_time/1000)/execution_count average_cpu_time_ms,
qs.execution_count,
q.[text]
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
WHERE (qs.total_worker_time/execution_count > @cputime_threshold_microsec
OR qs.max_worker_time > @cputime_threshold_microsec )
AND execution_count > @execution_count
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE)In this article we have discussed some of the common SQL Server high CPU issues. We also discussed how to identify and troubleshoot the SQL Server high CPU issues. If you are facing high CPU issues in SQL Server, then this article will be helpful for you.
,
