SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 alert if cpu is over 85% for 1 min continuous
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

avipenina
Starting Member

44 Posts

Posted - 05/26/2008 :  09:35:31  Show Profile  Reply with Quote
Hi,

does anyone know if it is possible to set an alert like that ?
if cpu is over 85% and stay for more then 1min continuous over 85%then alert.

THX

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/26/2008 :  10:15:41  Show Profile  Reply with Quote
Here you go:
http://www.sqlservercentral.com/articles/Administering/sqlserveralerts/1435/
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 05/26/2008 :  10:29:14  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

Here you go:
http://www.sqlservercentral.com/articles/Administering/sqlserveralerts/1435/



i didn't understand on how to create the alert that can check if the cpu stay over XX% more then X time continuous
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/26/2008 :  19:37:23  Show Profile  Reply with Quote
You may need to create an user created stored Procedure and setup SQL mail in your server and scheduled a job for that which will run daily and evry@ 5 mints.

Manoj
MCP, MCTS
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 05/27/2008 :  01:38:54  Show Profile  Reply with Quote
quote:
Originally posted by mdubey

You may need to create an user created stored Procedure and setup SQL mail in your server and scheduled a job for that which will run daily and evry@ 5 mints.

Manoj
MCP, MCTS


but how exactly this SP can check if the CPU is over 80% and stay over 80% for 60sec sequentially?
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/27/2008 :  09:49:02  Show Profile  Reply with Quote
Actually you just need to create a stored proc and schedule a job to run that stored proc in every 1 mint.

Bassically you may need to run that Stored proc only one time and it will store in form of compiled and no need to run it again and again. Job will check for that stored proc and will send you the results in your email or pager(It depends on What you have configured for sending alert).

Manoj
MCP, MCTS
Go to Top of Page

avipenina
Starting Member

44 Posts

Posted - 05/27/2008 :  10:31:20  Show Profile  Reply with Quote
quote:
Originally posted by mdubey

Actually you just need to create a stored proc and schedule a job to run that stored proc in every 1 mint.

Bassically you may need to run that Stored proc only one time and it will store in form of compiled and no need to run it again and again. Job will check for that stored proc and will send you the results in your email or pager(It depends on What you have configured for sending alert).

Manoj
MCP, MCTS



how the proc should look like?
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/28/2008 :  13:36:00  Show Profile  Reply with Quote
It would be normal sp with soem parameters.

Manoj
MCP, MCTS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/28/2008 :  13:42:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by mdubey

It would be normal sp with soem parameters.

Manoj
MCP, MCTS



Manoj,

You asked me in email for an example of a bad a response and here it is.

avipenina,

If you'd like to do this inside SQL Server, then you'll need to create a .NET CLR stored procedure since .NET is able to query the Performance Monitor counters.

Although this blog does not show you how to capture the CPU information, it does show you how to get to the Performance Monitor counters and query them in T-SQL:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

Alternatively, you could schedule the alert in Performance Monitor, which would mean it's done outside of SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 05/29/2008 :  11:47:14  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

quote:
Originally posted by mdubey

It would be normal sp with soem parameters.

Manoj
MCP, MCTS



Manoj,

You asked me in email for an example of a bad a response and here it is.

avipenina,

If you'd like to do this inside SQL Server, then you'll need to create a .NET CLR stored procedure since .NET is able to query the Performance Monitor counters.

Although this blog does not show you how to capture the CPU information, it does show you how to get to the Performance Monitor counters and query them in T-SQL:
http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx

Alternatively, you could schedule the alert in Performance Monitor, which would mean it's done outside of SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx


As I understand about SQL server you should be able to write T-SQL code for CPU usage time and then create a job to scheduled to run and check for it.

Let me know is it right or wrong?

Manoj
MCP, MCTS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 05/29/2008 :  12:54:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
Manoj,

Show us the code then. avipenina asked you twice to show the code for it and you replied with silly and incorrect answers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 06/02/2008 :  09:53:53  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Manoj,

Show us the code then. avipenina asked you twice to show the code for it and you replied with silly and incorrect answers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

With the help of this Dynamic view you can do that. "sys.dm_os_ring_buffers".
Let me know, if you have any questions.

Manoj
MCP, MCTS
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 06/02/2008 :  10:29:25  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Manoj,

Show us the code then. avipenina asked you twice to show the code for it and you replied with silly and incorrect answers.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Check this too.....

sys.dm_exec_cached_plans,
sys.dm_exec_sql_text, (dm_exec_sql_text(sql_handle))
sys.dm_tran_locks ,
sys.dm_exec_sessions

--Top 5 CPU average CPU time

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESC

--Batch execution statistics

SELECT
s2.dbid,
s1.sql_handle,
( SELECT TOP 1 SUBSTRING(s2.text, statement_start_offset / 2, ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) AS sql_statement,
execution_count, plan_generation_num, last_execution_time,
total_worker_time, last_worker_time, min_worker_time, max_worker_time,
total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads,
total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes
FROM
sys.dm_exec_query_stats s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE
( s2.objectid is null )
ORDER BY
s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset


I hope so....

Manoj
MCP, MCTS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/03/2008 :  14:44:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
mdubey, those DMVs do not provide the information that has been requested by avipenina. Please show us how to get the CPU%, like Task Manager and Performance Monitor show, in a stored procedure. As mentioned in my post, it can be accomplished with CLR, so we would like to see how it can be accomplished without a CLR as you seem to be indicating.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

mdubey
Posting Yak Master

USA
133 Posts

Posted - 06/04/2008 :  16:47:13  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

mdubey, those DMVs do not provide the information that has been requested by avipenina. Please show us how to get the CPU%, like Task Manager and Performance Monitor show, in a stored procedure. As mentioned in my post, it can be accomplished with CLR, so we would like to see how it can be accomplished without a CLR as you seem to be indicating.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

`"http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx"

Manoj
MCP, MCTS
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37129 Posts

Posted - 06/04/2008 :  16:48:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
mdubey,

What would you like for us to look at in that link?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000