HOw to get Automatic Email notification for High CPU Utilization >80 % with the query which is causing the high CPU usage

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    hi all,

    Do any one have any script by which i can get a automatic email notification when a HIGH CPU usage n >80 % occurs on the Sql server 2005 instance with the query which is causing the high CPU usage.

    thanks

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    I am not sure how you could alert based on one specific query causing 80% CPU use because SQL Server abstracts a lot of that but you could get a list of all the queries and their CPU use for analysis when the server goes above 80% CPU use. You could do this by setting up a WMI Alert in SQL Agent that would run a job when CPU reached 80% on the server. The job could capture all running queries. Be careful with this kind of approach though, it can make bad times worse depending on how aggressive your job is at pulling information from the server.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • deep_kkumar

    Hall of Fame

    Points: 3243

    Try third party tools........like sentry or nagios alerts.

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    hi,

    well as for now no 3rd party tool & WMI Alert can you pass me the script or any link through which i can get some idea........

  • Orlando Colamatteo

    SSC Guru

    Points: 182268

    Bing "wmi alert" to read about them for SQL Server Agent. Also lookup "ssis wmi event watcher" not because you need to use the object in ssis but because the msdn article has a sample WMI event query that will fire when CPU use goes above 80%.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    thanks look like i get what i was looking for........ will post the entire process step by step so that other can get the details and use it in there work.....

  • Jeff Moden

    SSC Guru

    Points: 993863

    Ivan Mohapatra - Wednesday, February 13, 2013 12:22 AM

    thanks look like i get what i was looking for........ will post the entire process step by step so that other can get the details and use it in there work.....

    We're just 3 months away from this comment being 5 years old... did you ever document this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply