July 10, 2010 at 7:37 pm
I need a query that checks my overall CPU utilization on server and kick of one SQL Agent Job if overall CPU utilization is over 95% , can any body help me to get the correct query/dmv please?
Thanks!!
July 10, 2010 at 8:18 pm
got from one of blog
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(1)
--SQLProcessUtilization AS [SQL Server Process CPU Utilization],
-- SystemIdle AS [System Idle Process],
-- 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
SQLProcessUtilization+(100 - SystemIdle - SQLProcessUtilization) AS [OverallCPU Utilizaton]
-- ,
-- DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC;
July 11, 2010 at 8:58 am
You can also create a performance alert: http://www.databasejournal.com/features/mssql/article.php/1498781/SQL-Server-Agent---Creating-Performance-Condition-Alerts-on-Database-Server-Objects.htm
If you take something from a blog, please give credit to the source. Someone else wrote this, so ensure they get the credit for doing the work.
One issue with this, and you want to be careful, is that simple queries, log backups, etc. can kick the CPU to 100%. You typically don't want alerts when this happens. What you want is an alert if this is sustained for some time, like more than 5 minutes, not each incident.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 11, 2010 at 10:39 am
pretty cool ,thanks i was missing alert part....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply