Resolving High CPU usage in SQL Server

This week, I would like to share a really quick tip on resolving high CPU usage in SQL Server. Usually, when the high CPU is reported by the monitoring system in place the very first thing we do is to check if it is SQL Server process that is causing high CPU. In case, it is SQL Server process that is causing high CPU we can then go further troubleshooting and resolving high CPU usage.

Resolving High CPU usage in SQL ServerSome of the reasons for High CPU usage:

  • There is (are) costly queries
  • System is running low on CPU or it’s under power
  • The Power Option is set to power saving or balance mode
  • Non-SQL processes
These are some of the lists that one should quickly go through, and in case SQL Server process is causing an issue. If we will go check the processes we will surely have one or more costly queries running that time. Let’s see how to fix high CPU utilization in SQL Server.

Resolving High CPU usage in SQL Server

Step 1: Execute below query and it will list the CPU hungry queries with number of sessions and have bad plan
Disclaimer: I am not sure for the origin of this script/query.
This query is used in our team to identify and resolve high CPU issue

--define the temptables that will hold intermediary results
IF OBJECT_ID('tempdb..#dbcc') IS NOT NULL
    DROP TABLE #dbcc

create table #dbcc(c1 varchar(15), c2 int, c3 varchar(255),spid int default 0)

IF OBJECT_ID('tempdb..#cpugroups') IS NOT NULL
    DROP TABLE #cpugroups

create table #cpugroups (sql_handle binary(20), sql_text nvarchar(50),total_cpu bigint,total_io bigint,total_sessions int, total_threads int)

--take the SPID groups that are running same code (NOT statement)
insert into #cpugroups
select top 10 sql_handle,substring((select text from fn_get_sql(sql_handle)),1,50), SUM(CPU) TotalCPUForGroup, SUM(physical_io) TotalIOForGroup, COUNT(distinct spid) TotalNoOfSessions,COUNT(*) TotalNoOfThreads
from master..sysprocesses (nolock) 
where spid>50 and status<>'sleeping'
and sql_handle<>0x0 and spid<>@@spid
group by sql_handle 
order by TotalCPUForGroup desc

declare @sql nvarchar(max)
declare @t table (spid int)

SELECT DISTINCT spid FROM master..sysprocesses WHERE spid>50 and sql_handle in (select sql_handle from #cpugroups)

declare @spid int

WHILE EXISTS(select * from @t)
  select top 1 @spid=spid from @t
  set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')'

  --try to retrieve the original command for all SPIDs
    INSERT INTO #dbcc(c1, c2, c3)
    EXEC (@sql)

    update #dbcc 
    set spid=@spid 
    where spid=0

  delete from @t where spid=@spid


select * from #cpugroups
select c3 [sql_text], count(*) NoOfSessionsRunning from #dbcc group by c3 order by 2 desc
select * from #dbcc
Step 2: Decide which query is more costly, take your judgment Step 3: Remove the bad execution plan from the cache. Once you determine the bad execution plan, get the sql_handle from the list and execute statement
DBCC FREEPROCCACHE (plan_handle_id_goes_here)
Step 4: You will notice that the CPU is now coming back to normal, this is one of the simplest methods of resolving High CPU usage in SQL Server among many and in case there are costly queries. Let me know if you like this quick tip, in case you find it interesting you may want to check some more troubleshooting tips here. photo credit: lungstruck PC Meter – Top View via photopin (license)

Leave a Reply

Your email address will not be published. Required fields are marked *

Hemantgiri Goswami

Hemantgiri Goswami

Hemantgiri is a seasoned SQL Server Consultant with demonstrated history for close to 21 years. He is a published author specializing in High Availability and Disaster Recovery area. He was awarded Most Valuable Professional by Microsoft 4 times. He is a regular speaker at events in Surat. Hemantgiri is founder and leader of the SQLPASS Chapter for Surat.