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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » HOw to get Automatic Email notification for...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Ivan Mohapatra

Posted Monday, February 11, 2013 4:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 104, Visits: 371

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

Post #1418310

opc.three

Posted Monday, February 11, 2013 7:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1418411

deep_kkumar

Posted Monday, February 11, 2013 11:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 23, 2013 6:43 AM
Points: 94, Visits: 565

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

Post #1418581

Ivan Mohapatra

Posted Tuesday, February 12, 2013 1:06 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 104, Visits: 371

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........

Post #1418780

opc.three

Posted Tuesday, February 12, 2013 7:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1418981

Ivan Mohapatra

Posted Wednesday, February 13, 2013 12:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 104, Visits: 371

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.....

Post #1419316

« Prev Topic | Next Topic »

Permissions