3

My question is an extension to this.

We have SQL Server Agent alerts(Deadlocks, Network IO etc) to monitor SQL Server performance. So i would like to write an alert for CPU increase percentage. Since We are using SQL Server-2012 standard edition we are not able to write an alert for it. So i have googled and found options like; we can raise an alert through Perfmon(Performance monitor). Please look below image. enter image description here

In alert task tab, "Run this task when alert is triggered" in this box i have given the path of .BAT file (which will run a query and send the current cpu expensive statements in a mail).

But alert is not firing when cpu percentage crosses certain limit. I guess the way i am configuring in perfmon is wrong for cpu alert. Can any one please suggest blogs or links for raising cpu alert from performance monitor? or Please suggest any other way to do this.

Basically, I am trying to call a batch file from performance monitor by using alerts. Please advice.

1

Have you tried the DMV sys.dm_os_performance_counters ? Using this view you can get several perf. counters, where you can sample by intervals to have an idea how your system is going.

This is the documentation by Microsoft: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql

Here you can find a few examples how to use it: http://www.sqlservercentral.com/articles/Performance+Tuning/71784/

And here is an example how to get your current CPU usage:

select perfCount.object_name
     , perfCount.counter_name
     , CASE 
            WHEN perfBase.cntr_value = 0
            THEN 0
            ELSE (CAST(perfCount.cntr_value AS FLOAT) / perfBase.cntr_value) * 100
      END AS cntr_Value
FROM
    (select * 
        from sys.dm_os_performance_counters
        where object_Name = 'SQLServer:Resource Pool Stats'
          and counter_name = 'CPU usage %' 
     ) perfCount
inner join (select * 
            from sys.dm_os_performance_counters
            where object_Name = 'SQLServer:Resource Pool Stats'
            and counter_name = 'CPU usage % base'
            ) perfBase
ON perfCount.Object_name = perfBase.object_name 

  • Hi, Thanks for the answer. But i would like to achieve the same functionality of sql server alerts(Cpu %) which will fire only when cpu crosses it's threshold. – Tharunkumar Reddy Oct 3 '17 at 10:03
  • Hi @Buddi, you can easily achieve that by creating a stored procedure + a scheduled job. The counters on the DMV are cumulative, so you'd need to create some routine to get the difference between some interval, and every time your job runs, you check if your value is above the specified threshold. But first, I think it's worth to create a baseline to understand the normal workload, and then you can set up a more accurate threshold. – Everton Barciela Oct 3 '17 at 10:14

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.