Australia Finance

Jan 17 2018

How to Monitor SQL Server CPU Usage and get auto alerts

#sql #dba,dba,sqldba,sql #server,sql #2005,sql #2008,backup,restore,logshipping,mirroring,cluster,clustering,replication,database,data #file,log #file,mdf,ldf,execution #plan,profiler,ssms,ssrs,ssis,olap,cube,table,index,page,dmv,dbcc,cache,memory,storage,disk,server,stored #procedure,checkpoint,transaction #log,checkdb,perfmon,ddl #events,trigger,login,user,dbmail,db #admin,policy,resource #governer,audit,high #availability,script,sql #agent,jobs,sql #azure,sql #cloud


#

26

There are some condition times when SQL Server CPU utilization exceeds threshold value (ex 80% or more) and stay constant even till 100% for a undefined duration. A DBA do not get notified automatically about performance degrade until its reported by application team or service desk. This usually happens when all in sudden unpredicted application workload goes to SQL Server for processing; but at good number of times, its because of some T-SQL Queries are taking longer than expected or because of bad execution plan or blocking. There may be more reason to add, but ultimately high CPU utilization on production systems is going to hurt performance.

Given below is a modified script that you can convert into stored procedure and call from SQL Server agent job, scheduled for every 5 minutes or more to check CPU utilization on a critical database server. This script uses sys.dm_os_ring_buffers to identify CPU utilization from last 5 minutes and if average CPU utilization goes beyond 80% then send an alert email along with top 50 CPU consuming SQL sessions and details.

Comments (26)

we can use process explorer tools to find which thread cost most CPU.
then runing
select sql.text
from sys.dm_exec_request qt
cross apply sys.dm_exec_sql_text(qt.sql_handle) sql
where session_id=
(select stasks.session_id
from sys.dm_os_tasks as stasks
inner join sys.dm_os_thread as sthreads
on stasks.worker_address=sthreads.worker_address
where stasks.session_id is not null and sthreds.os_thread_id=?(find in process explorer)

owais said on 13-04-2011

Hey Bro,
Thanks. for a wonderful script.

but i am getting an arithmetic overflow error while executing the script on one of my server.


Written by admin


Leave a Reply

Your email address will not be published. Required fields are marked *