3

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 enter image description here

enter image description 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.

  • Are you sure the CPU usage is rising above 50%? What if you try 5%? – Max Vernon Jul 27 '17 at 20:57
  • @MaxVernon Even i tried with 5% and ran one expensive query which makes cpu to grow more than 10% continuously for 2 minutes still i didn't get any mail. But it's working when alert if counter sets to falls below. Please help me if any thing wrong i did. – Tharunkumar Reddy Jul 28 '17 at 5:11
3

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';
  • It's really a great answer. But unfortunately i am using standard edition. So can't able to reconfigure resource governor is there any other way to do this in standard edition? Can you please suggest? – Tharunkumar Reddy Jul 31 '17 at 4:53
0

Under general check enable (First creenshot) and under response check execute job and then choose 'syspolicy_purge_history ([Uncategorized (Local)])' from the list (second screenshot)

0
  1. Under General check Enable (First screenshot)
  2. Under General Instance drop down select Default.

After changing the above 2 configuration settings, it will work fine and you will get the notification.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.