SQL Store procedures question

[bowwow]

Does anyone know of a way of monitoring the execution times of a Stored Procedure?
We need to be Alerted whenever a certain Stored Procedure has exceeded a certain time threshold...

Any help much appreciated

 

[mrdenny]

One way that I can think to do this would be to create a table which holds the SPID and start time of the procedure.

create table proc_monitor
(spid int not null,
StartTime datetime not null)
go

At the top and end of your proc put in this code.

create procedure ...
as
insert into proc_monitor
(spid, StartTime)
values
(@@SPID, getdate())
...
delete from proc_monitor
where spid = @@SPID
go

Then write a job that checks that table for entries which are long running and sends out an email if there are any. Have this run every minute.Something like this would work.

if exists (select * from proc_monitor where StartTime < dateadd(mi, -2, getdate())
   exec master.dbo.xp_sendmail 'admin', 'Procedures running to long'

There isn't anything builtin that can be triggered if the proc takes to long.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com