debugsqlMinor
Cpu% usage alert is not working for 'alert if counter=raises above'
Viewed 0 times
counteraboveworkingusageforraisescpunotalert
Problem
I am thinking to create an alert for Cpu usage when it crosses certain value. So i configured one alert for this and attached those screen shots here
But the above alert is not working mail is not triggering when it crosses 50%. Is any thing wrongly i configured?
And same thing i have tested with Alert if counter='falls below' then mail was triggered and it worked perfectly.
Please help me in this any thing i want to change.
But the above alert is not working mail is not triggering when it crosses 50%. Is any thing wrongly i configured?
And same thing i have tested with Alert if counter='falls below' then mail was triggered and it worked perfectly.
Please help me in this any thing i want to change.
Solution
I am only able to get SQL Server 2012 to fire an alert based upon the "Workload Group Stats -> CPU Usage %" if I enable a dummy Resource Governor classifier function that classifies all connections into the default Resource Governor group.
I used the following classifier function, with the associated code to enable that function:
I created an alert using this code, that alerts whenever CPU usage rises above 10%:
I then used this query to force CPU usage in a session that is connected to SQL Server after the classifier function has been enabled, and the Resource Governor has been reconfigured:
This query shows the status of the alert, as well as when it was last fired:
I used the following classifier function, with the associated code to enable that function:
USE master;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
RETURN N'default'; --every session is assigned to the "default" pool.
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GOI created an alert using this code, that alerts whenever CPU usage rises above 10%:
EXEC msdb.dbo.sp_add_alert @name=N'CPUUsage'
, @enabled=1
, @delay_between_responses=0
, @include_event_description_in=1
, @performance_condition=N'Workload Group Stats|CPU usage %|default|>|0.1'
, @job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'CPUUsage'
, @operator_name=N'Max'
, @notification_method = 1;
GOI then used this query to force CPU usage in a session that is connected to SQL Server after the classifier function has been enabled, and the Resource Governor has been reconfigured:
SELECT CRYPT_GEN_RANDOM(10)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v6(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v7(num)This query shows the status of the alert, as well as when it was last fired:
SELECT AlertName = sa.name
, Enabled = sa.enabled
, LastOccurrence = CASE WHEN sa.last_occurrence_date > 0
THEN dbo.agent_datetime(sa.last_occurrence_date, sa.last_occurrence_time)
ELSE NULL
END
, OccurrenceCount = sa.occurrence_count
, PerformanceCondition = sa.performance_condition
FROM dbo.sysalerts sa
WHERE sa.name = 'CPUUsage';Code Snippets
USE master;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
RETURN N'default'; --every session is assigned to the "default" pool.
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GOEXEC msdb.dbo.sp_add_alert @name=N'CPUUsage'
, @enabled=1
, @delay_between_responses=0
, @include_event_description_in=1
, @performance_condition=N'Workload Group Stats|CPU usage %|default|>|0.1'
, @job_id=N'00000000-0000-0000-0000-000000000000';
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'CPUUsage'
, @operator_name=N'Max'
, @notification_method = 1;
GOSELECT CRYPT_GEN_RANDOM(10)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v6(num)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v7(num)SELECT AlertName = sa.name
, Enabled = sa.enabled
, LastOccurrence = CASE WHEN sa.last_occurrence_date > 0
THEN dbo.agent_datetime(sa.last_occurrence_date, sa.last_occurrence_time)
ELSE NULL
END
, OccurrenceCount = sa.occurrence_count
, PerformanceCondition = sa.performance_condition
FROM dbo.sysalerts sa
WHERE sa.name = 'CPUUsage';Context
StackExchange Database Administrators Q#181954, answer score: 3
Revisions (0)
No revisions yet.