Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  SQL Server Tips by Gama and Naughter

Applications of XP_CPUUSAGE

Wouldn't it be great if SQL Server would send a warning if the CPU was running at 100% for a certain time?
This is not only possible but also very useful in the real world.

Example: Job that verifies the CPU usage every 5 minutes, if it is >90% then switch to 1 minute, after 10 minutes send a warning to the SysAdmin.

For this test, a very simple job is required with the following parameters:

Job name “Check CPU usage”

The Step Command code is as follows:

DECLARE @CPUUsage int
EXEC master..XP_CPUUSAGE 500, @CPUUsage OUTPUT
IF @CPUUsage>90
BEGIN
IF OBJECT_ID('tempdb..tableCPUusage') IS NULL
CREATE TABLE tempdb..tableCPUusage(CPUusage int, CheckTime datetime)
INSERT tempdb..tableCPUusage(CPUusage, CheckTime) VALUES(@CPUUsage, getdate())
DECLARE @avgCPUusage int, @t1 datetime, @t2 datetime, @t10 datetime
SELECT @avgCPUusage=AVG(CPUusage) FROM

 See code depot for full script  

SET @t1=(SELECT TOP 1 CheckTime FROM
(SELECT TOP 10 CheckTime, CPUusage
FROM dbo.tableCPUusage
ORDER BY CheckTime DESC)t)

SET @t2=(SELECT TOP 1 CheckTime FROM
(SELECT TOP 2 CheckTime, CPUusage
FROM dbo.tableCPUusage
ORDER BY CheckTime DESC)t ORDER BY CheckTime ASC)

SET @t10=(SELECT TOP 1 CheckTime FROM
(SELECT TOP 10 CheckTime, CPUusage
FROM dbo.tableCPUusage
ORDER BY CheckTime DESC)t ORDER BY CheckTime ASC)

IF DATEDIFF(n, @t2, @t1)=5
EXEC msdb..sp_update_jobschedule @job_name ='Check CPU usage', @name ='Check CPU sched', @freq_subday_interval =1

IF DATEDIFF(n, @t2, @t10)=10
BEGIN
DECLARE @TempBody varchar(8000)
SET @TempBody='Please check server, CPU usage is '+@avgCPUusage +'%.'
EXEC master..XP_SMTPSENDMAIL @From='SQL server', @To='SysAdmin@bigcompany.com', @Subject='CPU overload', @Body=@TempBody
See code depot for full script   @freq_subday_interval =5
END
END


The code first checks the CPU usage, if it is over 90% then it will check for the table that will store the percentage of CPU usage and the time when it was analyzed. If the table does not exist then it will be created.

The current CPU usage and time are stored in the table. Four variables are declared, one for storing the average CPU usage, and three for storing the time from the most recently inserted record, the record before that one and the tenth more recent record. If the most recent record and the previous one are 5 minutes apart, then the frequency of executing the job will change and the job will execute every minute. If the most recent record and the tenth more recent one are 10 minutes apart, then an email will be sent to the SysAdmin and the job will execute every 5 minutes, instead of 1.


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm  

   

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter