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.

Some 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)

INSERT INTO @t
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)
BEGIN
  select top 1 @spid=spid from @t
  set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')'

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

    update #dbcc 
    set spid=@spid 
    where spid=0
    
  END TRY
  BEGIN CATCH
  END CATCH


  delete from @t where spid=@spid

END


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)