August 13, 2014 at 1:49 pm
Hi,
I'm setting up an SQL Server Agent Alert in a Microsoft SQL Server 2008 (SP2) - 10.0.4064.0 (X64) (Build 6002: Service Pack 2) (VM), running in a Windows Server 2008 x64 Std SP2.
I want to be alerted when the cpu usage reache 80%, but the alert was never triggered, so I changed it to 10%, just for test, but still never triggered.
My Server is exclusive for SQL Server. Normally the CPU utilization is around 30% under normal conditions.
These are the alerts:
USE [msdb]
GO
/****** Object: Alert [CPU] Script Date: 08/13/2014 16:42:36 ******/
EXEC msdb.dbo.sp_add_alert @name=N'CPU',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Workload Group Stats|CPU usage %|internal|>|0.1',
@job_id=N'0653338d-8841-40b1-94c7-2f68fa54b782'
GO
USE [msdb]
GO
/****** Object: Alert [CPU2] Script Date: 08/13/2014 16:43:12 ******/
EXEC msdb.dbo.sp_add_alert @name=N'CPU2',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Resource Pool Stats|CPU usage %|internal|>|0.1',
@job_id=N'0653338d-8841-40b1-94c7-2f68fa54b782'
GO
Can anyone point me my mistake?
Tks,
LCR.
August 14, 2014 at 1:30 am
They bot relate to Resource Governor (http://msdn.microsoft.com/en-us/library/ms190382.aspx). If you dont have RG configured you wont get any alerts.
If you want to monitor the actual CPU usage you will want to write a WMI query, or look at something like this (http://www.sqlservercentral.com/Forums/Topic611107-146-1.aspx) and get it to send an alert if its over your thresholds.
August 22, 2014 at 12:56 pm
Thaks for the tip.
I changed the alert for WMI, like shown bellow, and works great!
EXEC msdb.dbo.sp_add_alert @name=N'SQL Performance CPU alert WMI',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=120,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\ROOT\CIMV2',
@wmi_query=N'SELECT * from __InstanceModificationEvent WITHIN 2 WHERE TargetInstance ISA ''Win32_Processor'' and TargetInstance.LoadPercentage > 90'
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply