How to Log and Monitor Your Server’s CPU Usage without Using Third Party Tools

In this article, we will introduce how to monitor CPU usage on SQL Servers using TSQL Script. With the script, you can log server’s CPU usage. You can also set email alerts.

Why it is So Important to Monitor CPU Usage as a DBA

Checking the resource utilization would be the primary step that a DBA would do when they are called for a Performance trouble shooting. High CPU usage on SQL Server will have a huge impact on the SQL’s performance and on the CPU. A CPU’s lifespan would be reduced if it always performs at full capacity for a longer period. After a complete analysis, even though a change in hardware is necessary, a CPU cannot be increased instantaneously.

Third Party Tools are not Ideal Solutions

Though there are several third party tools to monitor CPU usage, they might either be costlier or complicated or time consuming to install, set up and configure.

DIY is Easier than You Think

You can now log and monitor your CPU usage by merging Transact-SQL and Windows Management Instrumentation Command-line. With this set up you can easily read, log and send an alert email whenever a SQL Server’s CPU jumps beyond threshold value.  Nevertheless please make sure that you have enabled cmdshell and DBmail in your SQL instance before using this script. Do update place holders in this script with correct server name, threshold value for CPU usage, DBmail profile name and recipient list.

/* Enabling cmdshell in your SQL instance */
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
RECONFIGURE;  
GO 
/* Declaring Variables and creating necessary tables to capture CPU Usage */
declare @cpuusage as integer, @v_subject as varchar(1000)
Create table CPUtable  (c1 varchar(100))
create table CPUtable2 (c1 integer)
if not exists (select * from sysobjects where name='CPUlog' and xtype='U')
create table CPUlog (Log_Time datetime,CPU_percentage int);
/* Using the Windows Management Instrumentation Command-line tool to read the CPU usage */
insert into CPUtable
exec master.dbo.xp_cmdshell 'wmic cpu get loadpercentage'
delete from CPUtable
where c1 like '%LoadPercentage%'
delete from CPUtable
where c1 is NULL
delete from CPUtable where ISNUMERIC(c1) = 1
update CPUtable
set c1 = REPLACE(c1,' ','')
update CPUtable
set c1 = replace(REPLACE(c1,CHAR(13), ''), CHAR(10), '')
insert Into CPUtable2
select cast (c1 as int) from CPUtable
insert into CPUlog (CPU_percentage)
select AVG(c1) from CPUtable2
set @cpuusage = (select AVG(c1) from CPUtable2)
/* In this script, i have used 80% as threshold value. You can change it as per your requirement */
/* You can also modify the subject line here */
set @v_subject = 'Alert ! High CPU Usage on <YOUR SERVER NAME>. Current CPU% --> ' + convert(varchar,@cpuusage)
if @cpuusage > 80 begin
EXEC msdb.dbo.sp_send_dbmail 
  @profile_name='<DBMAIL PROFILE NAME>',
  @recipients='RECIPIENT LIST. SEPARATE MULTIPLE RECIPIENTS BY ;',
  @subject= @v_subject,
  @body='Please check CPU Usage'
end
update CPUlog
set Log_Time  = GETDATE()
where Log_Time IS NULL
select * from CPUlog
drop table CPUtable
drop table CPUtable2

Let us break this script into two parts for you to understand better. In the first part, we are using WMIC and cmdshell to read the server’s CPU Usage and log it in a Table. In the second and final part, SQL’s DBmail is used to send an alert email. You can schedule this script as a job from SQL Agent.

The script will send a simple alert email as shown here.

If you check the log table you can see the date, time and the CPU usage.

High CPU Usage will Cause Problems

High CPU usage will make your server slow down or even shut down. Whenever you receive a CPU high-usage alert email, you should check your server immediately and fix the issue. If your server has powered off and your database files are corrupt, then you have to perform a SQL file recovery to solve the issue.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook corruption and excel recovery software products. For more information visit www.datanumen.com