Steps to troubleshoot high CPU utilization in SQL Server

Updated: Mar 29

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.


SQL Server high CPU Task Manager

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.

Check SQL Server CPU usage using Ring Buffer


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.

PowerShell Code

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.

SP_WhoISActive stored procedure:

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

Check Execution Plan using SP_WHOISACTIVE

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

Dynamic Management Views


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;  
GO

You 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


Update statistics

The following DemoTable has a 49% sampling percent for the total 421201 rows.

Check Samping percentage

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.

https://github.com/rajendragp/RajendraScripts/blob/master/ExecutionPlanWithMissingIndex.sql

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.

https://github.com/rajendragp/RajendraScripts/blob/master/CreateMissingIndex.sql


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.

https://github.com/rajendragp/RajendraScripts/blob/master/IdentifyXEventTrace.sql

Review these active XEvent or Server traces and see if you need them. Stop any unwanted running traces.

Disable heavy tracing
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.

,




10,543 views0 comments

Recent Posts

See All