HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Cpu% usage alert is not working for 'alert if counter=raises above'

Submitted by: @import:stackexchange-dba··
0
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.

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:

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;
GO


I 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;
GO


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:

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;
GO
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;
GO
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)
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.