Troubleshooting High CPU Utilization in SQL Server

USE DBATOOLS -- change to your own repository database
GO

drop table if exists dbo.CPUCounter, dbo.WhoisActiveResult;
go
 
CREATE TABLE dbo.CPUCounter (
  ServerName varchar(128)
, CounterInstance varchar(30) -- '_total' or 'sqlservr'
, CounterPath varchar(500)
, CookedValue decimal(8,3)
, LogDate datetime default getdate()
, ID int identity primary key
, BatchID int
);
go
CREATE TABLE dbo.WhoisActiveResult ( [collection_time] datetime 
,[dd hh:mm:ss.mss] varchar(20),[session_id] smallint  
,[sql_text] xml 
,[login_name] nvarchar(128),[wait_info] nvarchar(4000) 
,[CPU] varchar(30) ,[CPU_delta] varchar(30) 
,[blocking_session_id] int ,[reads] varchar(30) ,[reads_delta] varchar(30) 
,[writes] varchar(30) ,[writes_delta] varchar(30) ,[physical_reads] varchar(30) 
,[physical_reads_delta] varchar(30) ,[open_tran_count] varchar(30) 
,[percent_complete] varchar(30) ,[host_name] nvarchar(128) ,[database_name] nvarchar(128) 
,[program_name] nvarchar(128) ,[login_time] datetime , LogDateTime datetime default getdate() 
, ServerName varchar(128) ---- the server where these sessions are occurring
, id int identity primary key); 

#NOSQLPS
#part 1, collect CPU perf counters from monitored servers
Import-Module SqlServer;
 
$central_svr ='CentralSvr';  #  pls change the name for your server
$central_db = 'dbatools'; #central repository db
$svr_list = ‘svr_1', ‘svr_2', ‘svr_3'; # this is the list of the monitored servers
#we assume default sql instance ‘\Process(sqlserver)' in this case, you may need to change to your named sql instance when necessary
$counters = '\Process(_total)\% Processor Time', '\Process(sqlservr)\% Processor Time'; #perf counter names, default sql instance is assumed here
 
$cpu_threshold = 0.85; #we can set a low value for test purpose
$interval = 5; # report cpu every 5 min
 
try {
    $rslt = get-counter -Counter $counters -ComputerName $svr_list -SampleInterval 5  | 
    Select-Object  -ExpandPropert countersamples #-ComputerName uw2psql14
 
    $dt = new-object System.Data.DataTable;
    $col = New-Object System.Data.DataColumn('ServerName', [System.String]);
    $dt.Columns.add($col);
 
    $col = New-Object System.Data.DataColumn('CounterInstance', [System.String]);
    $dt.Columns.add($col);
 
    $col = New-Object System.Data.DataColumn('CounterPath', [System.String]);
    $dt.Columns.add($col);
 
    $col = New-Object System.Data.DataColumn('CookedValue', [System.Decimal]);
    $dt.Columns.add($col);
 
    foreach ($r in $rslt)
    {
      $dr = $dt.NewRow();
  
      $i = $r.path.IndexOf('\process');
      $dr.ServerName = $r.path.Substring(2, $i-2);
      $dr.CounterInstance = $r.InstanceName;
      $dr.CounterPath = $r.Path;
      $dr.CookedValue = $r.CookedValue;
      $dt.Rows.add($dr);
    }
    Write-SqlTableData -ServerInstance $central_svr -DatabaseName $central_db -SchemaName dbo -TableName CPUCounter -InputData $dt -TrustServerCertificate;
    $qry = @"
    update t
    set batchid = (select max(isnull(batchid,0))+1 from dbo.CPUCounter)
    from dbo.CPUCounter t
    where batchid is null;
"@
invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query $qry -TrustServerCertificate;
}
catch {
    write-error $_;
}

#part 2 –- find whether CPU has reached the defined threshold
$qry = @"
declare @cpu_threshold decimal(5,2)=$cpu_threshold; -- threshold above which an alert will be sent 
declare @interval int = 5; -- interval (minutes) that is used to evaluate the
declare @batchid int, @diff_minutes int, @cnt int;
 
select  @batchid= max(batchid) 
from dbo.CPUCounter;
 
select  @diff_minutes = datediff(minute, min(logdate), max(logdate)), @cnt = count(distinct batchid)
from dbo.cpucounter
where batchid >= (@batchid - @interval);
drop table if exists #t;
create table #t (servername varchar(128), perc decimal(6,2));
 
if @diff_minutes >= @interval-1 and @cnt >= @interval 
begin
   ; with c as (select servername, counterinstance, cnt=count(*), CV=avg(cookedvalue)
   from dbo.CPUCounter
   where batchid > (@batchid - @interval) and BatchID <= @batchid 
   group by servername, counterinstance )
   insert into #t (servername, perc)
   select c.servername, perc = c.cv/c2.cv 
   from c
   inner join c as c2
   on c.ServerName = c2.ServerName
   and c.CounterInstance = 'sqlservr' and c2.CounterInstance = '_total'
end
 
select Servername, Perc from #t
where Perc >= @cpu_threshold;
 
"@
$rslt = invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query $qry -OutputAs DataRows -TrustServerCertificate;
 
#part 3, if CPU threshold is reached, take a snapshot of the environment with sp_whoisactive
$qry2=@"
drop table if exists #whoisactiveresult;
 
CREATE TABLE #WhoisActiveResult ( [collection_time] datetime 
,[dd hh:mm:ss.mss] varchar(20) ,[session_id] smallint  
,[sql_text] xml 
,[login_name] nvarchar(128),[wait_info] nvarchar(4000) 
,[CPU] varchar(30) ,[CPU_delta] varchar(30) 
,[blocking_session_id] int ,[reads] varchar(30) ,[reads_delta] varchar(30) 
,[writes] varchar(30) ,[writes_delta] varchar(30) ,[physical_reads] varchar(30) 
,[physical_reads_delta] varchar(30) ,[open_tran _count] varchar(30) 
,[percent_complete] varchar(30) ,[host_name] nvarchar(128) ,[database_name] nvarchar(128) 
,[program_name] nvarchar(128) ,[login_time] datetime) 
 
EXEC SP_WHOISACTIVE --@HELP=1
  @not_filter='NT AUTHORITY\SYSTEM', @not_filter_type='login'
, @get_outer_command=1
, @output_column_list = '
[collection_time]
,[dd hh:mm:ss.mss]
,[session_id]
,[sql_text]
,[login_name]
,[wait_info]
,[CPU]
,[CPU_delta]
,[blocking_session_id]
,[reads]
,[reads_delta]
,[writes]
,[writes_delta]
,[physical_reads]
,[physical_reads_delta]
,[open_tran_count] 
,[percent_complete] 
,[host_name]
,[database_name]
,[program_name]
,[login_time]'
, @delta_interval = 2
, @destination_table='#whoisactiveresult';
 
SELECT * from #whoisactiveresult;
"@ 
 
#we will need to capture the sp_whoisactive and save to central repository
If ($rslt -ne $null) #some server has CPU perf exceeding the specified threshold
{
  Foreach ($r in $rslt)
  { 
    $svr_tmp = $r.Servername
    Write-Host "$svr_tmp" -ForegroundColor Green;
    $whoisactive = invoke-sqlcmd -ServerInstance $svr_tmp -Database master -Query $qry2 -TrustServerCertificate -OutputAs DataTables -MaxCharLength 65534; 
    if ($whoisactive -ne $null)
    {  
        Write-SqlTableData -ServerInstance $central_svr -DatabaseName $central_db -SchemaName dbo -TableName WhoisActiveResult -force  -InputData $whoisactive -TrustServerCertificate;
        invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query "update dbo.WhoisActiveResUlt set ServerName='$svr_tmp' where ServerName is null" -TrustServerCertificate;
    }#$whoisactive -ne $null
  }
  #we also get the current max(id) after the insert to dbo.Whoisactive
  $rd = invoke-sqlcmd -ServerInstance $central_svr -Database $central_db -Query "select starttime=convert(datetime, dateadd(minute, -$interval,max(collection_time)), 101),  endtime =convert(datetime, dateadd(second, 1, max(collection_time)), 101) from dbo.WhoisactiveResult with (nolock)" -OutputAs DataRows -TrustServerCertificate;
  $rid_st = $rd.starttime;
  $rid_et = $rd.endtime;
 
  #compose sql statements for alert email
    $cpu_perc = $cpu_threshold * 100;
    $qry ="
    exec MSDB.DBO.sp_send_dbmail @recipients='your-email'
    , @subject='High CPU Detected (above $cpu_perc%)  for the past [$interval] minutes'
    , @body = 'For details, please run the following query against $central_svr `n
 
select * from DBATOOLS.dbo.WhoisactiveResult 
where collection_time between ''$rid_st'' and ''$rid_et'' 
order by cast(replace(replace(cpu_delta, '','', ''''), '' '', '''') as int) desc;'
"
    invoke-sqlcmd -ServerInstance $central_svr -Database msdb -TrustServerCertificate -Query $qry;
}#if 

$cpu_threshold = 0.10; #we can set a low value for test purpose

$svr_list = ‘svr_1', ‘svr_2', ‘svr_3'; # this is the list of the monitored servers

Get-Counter -Counter '\Process(_total)\% Processor Time' -ComputerName "servername" # see an example below