alert if cpu is over 85% for 1 min continuous

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

alert if cpu is over 85% for 1 min continuous

Author

Topic

avipenina
Starting Member

44 Posts

Posted - 2008-05-26 : 09:35:31

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-26 : 10:15:41

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

avipenina
Starting Member

44 Posts

Posted - 2008-05-26 : 10:29:14

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

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-26 : 19:37:23

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

avipenina
Starting Member

44 Posts

Posted - 2008-05-27 : 01:38:54

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?

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-27 : 09:49:02

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

avipenina
Starting Member

44 Posts

Posted - 2008-05-27 : 10:31:20

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?

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-28 : 13:36:00

It would be normal sp with soem parameters.

Manoj
MCP, MCTS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-28 : 13:42:05

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

mdubey
Posting Yak Master

133 Posts

Posted - 2008-05-29 : 11:47:14

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-29 : 12:54:00

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

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-02 : 09:53:53

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

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-02 : 10:29:25

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-03 : 14:44:39

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

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-04 : 16:47:13

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-04 : 16:48:38

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources