Skip to main content

Capture SQL Processes through Email after a CPU Breach of X Percentage

There are many times, I felt the need to capture those processes running and consuming CPU on an SQL Server. It comes to a boiling point when that dearth is elevated to make me feel doing a labor while the CPU utilization has peaked due to SQL Server. As a result, my first question to any SQL Server expert was the same, as to why there is no direct way to capture the detail. As a result, my learning lead me to WMI, a treasure for an admin in automation mind. (Trivia: Windows Task Manager works on the same WMI principle used in this code). This is a POLL based CPU Usage reporting, as there is no direct triggering mechanism provided by Windows by any other means to use them appropriately. 

 Below query can be put into an SQL Agent job, with a Powershell step, which can be called every 1m (Job completion takes ~53sec per my test). This job mails you the most CPU consuming processes on a server whenever CPU usage breaches 90%(Can be changed per your need).

 If you need precise Utilization details the Powershell script below should be put into a loop to keep executing as long as needed. 

 PS: Note that this is specific to Servers that has mostly SQL Server running. If you are running many applications besides SQL Server, then its probably not for you, as SQL might not be the sole CPU consumer. 

 Code:


 $CPU=Get-WmiObject Win32_PerfFormattedData_PerfOS_Processor | Select-Object Name, PercentProcessorTime | Where {$_.Name -eq "_Total"}  
 #Replace the Percentage value that you like to use  
 if($CPU.PercentProcessorTime -ge "90")  
 {  
 $CPU.PercentProcessorTime  
 #SQL Instance Name should be replaced with a valid Instance Name  
 Invoke-Sqlcmd -ServerInstance "SQL INSTANCE NAME HERE" -Query "select * into #tmp from sys.sysprocesses  
 go  
 waitfor delay '00:00:01'  
 -- wait for a few seconds before running the next statement  
 select t1.cpu - t2.cpu as cpu_diff,  
     t1.physical_io - t2.physical_io as io_diff, db_name(t1.dbid) as 'Database',  
     [Individual Query] = SUBSTRING (qt.text,er.statement_start_offset/2,(  
     CASE WHEN er.statement_end_offset = -1  
     THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  
  ELSE er.statement_end_offset END - er.statement_start_offset)/2)  
  ,[Parent Query] = qt.text, er.command, er.wait_time, er.last_wait_Type, er.wait_resource,er.scheduler_id,er.reads,er.writes,er.logical_reads,  
     er.granted_query_memory, t1.login_time,t1.last_batch,t1.status,t1.program_name, t1.loginame  
  into #temp from sys.sysprocesses t1 inner join #tmp t2 on t1.spid = t2.spid   
  inner join sys.dm_exec_requests er ON t1.spid = er.session_id  
  OUTER APPLY sys.dm_exec_sql_text(er.sql_handle)as qt  
  where t1.spid = t2.spid  
   and t1.login_time = t2.login_time  
   and t1.ecid = t2.ecid  
   and (t1.cpu - t2.cpu ) > 0  
 order by cpu_diff desc  
 go  
 drop table #tmp  
 --select * from #temp  
 --drop table #temp  
 go  
 DECLARE @xml NVARCHAR(MAX)  
 DECLARE @body NVARCHAR(MAX)  
 SET @xml = CAST(( SELECT [cpu_diff] AS 'td','',[io_diff] AS 'td','',  
     [Database] AS 'td','', [Individual Query] AS 'td','',[Parent Query] AS 'td','',  
   [command] AS 'td','',[wait_time] AS 'td','',[last_wait_Type] AS 'td','',[wait_resource] AS 'td','',[scheduler_id] AS 'td','',  
   [reads] AS 'td','',[writes] AS 'td','',[logical_reads] AS 'td','',[granted_query_memory] AS 'td','',[login_time] AS 'td','',  
   [last_batch] AS 'td','',[status] AS 'td','',[program_name] AS 'td','',[loginame] AS 'td',''  
 FROM #Temp  
 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))  
 SET @body ='<html><body><H3>CPU Utilization at Current Time</H3>  
 <table border = 1>   
 <tr>  
 <th> cpu_diff </th> <th> io_diff </th> <th> Database </th> <th> Individual Query </th><th> Parent Query </th><th> command </th>  
 <th> wait_time </th><th> last_wait_Type </th><th> wait_resource </th><th> scheduler_id </th><th> reads </th><th> writes </th>  
 <th> logical_reads </th><th> granted_query_memory </th><th> login_time </th>  
 <th> last_batch </th><th> status </th><th> program_name </th><th> loginame </th></tr>'    
 SET @body = @body + @xml +'</table></body></html>'  
 --select @body  
 EXEC msdb.dbo.sp_send_dbmail  
 --Change the Mail Profile Name thats appropriate  
 @profile_name = 'SMTP MAIL PROFILE HERE', -- replace with your SQL Database Mail Profile   
 @body = @body,  
 @body_format ='HTML',  
 @recipients = 'EMAIL HERE', -- replace with your email address  
 @subject = 'CPU Utilization at Current Time' ;  
 DROP TABLE #Temp"   
 #write-host "TRUE"  
 exit  
 }  
 Else  
 {  
 $CPU.PercentProcessorTime  
 #write-host "False"  
 exit  
 }

Labels:

Comments

Post a Comment

Popular posts from this blog

Digging into a long running job

Revisiting the blogger to finally start again blogging about the SQL Server after a long long time.  This post is on analyzing and digging into what is causing a job significant time to complete. This will be a basic level of approach and I wish, I could have done better. But, its was enough in my case to get to a conclusion, with the knowledge I equip.   Issue : A job that's scheduled to run every three hours, was running for more than 48hours. Average job run time, 1.5hrs.  Analysis : First and foremost thing to do, is to find out on what session id is the job running on the instance. You may use the below query to fetch the session id of the job from the job name. declare @Var1 varchar(36) select @Var1=substring(REPLACE(CAST(job_id AS VARCHAR(36)),'-',''),17,16) from sysjobs where name =' JOB_NAME ' select * from sys.sysprocesses where program_name like '%'+@Var1+'%' You may get a single row or multiple rows with ...
Post a Comment
Read more

Deferred prepare could not be completed

This is usually an error that is encountered while querying a remote database on a different server instance which says OLEDB provider "SQLNCLI" for linked server "ABCD" returned message "Deffered prepare could not be completed" Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. This is the way I could rectify, though this is not the lone mistake that gives this error every time. Just set the default to the database that is being querying to the user through which the Linked server is connecting to the database.
4 comments
Read more

Incorrect SET Options with Filtered Indexes

Rebuild Index|Create Index|DML on a Table with Filtered Index fails with the error: The following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934).  The step failed. The Operation above might succeed when its through SSMS. But, would fail when the operation is from a SQL Agent Job. This is because, SQL Agent by default, do not have the SET Options enabled (ON). They are OFF. Reason: The following are the SET Options that are needed while operation on a table with a filtered index. Required SET Options for Filtered Indexes The SET options in the Required Value column are required whenever any of the following conditions occur: Create a filtered index. INSERT, UPDATE, DELETE, or MERGE operation modifies the data in a...
Post a Comment
Read more