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.
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