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.