SQL Alert when stored procedure executes for too long

Question

I would like to set up a SQL Server 2008 Alert to notify me when any procedure executes for 1 second or longer (just an example).

Any ideas?

EDIT:

Okay, it seems this is not possible. But, just to nudge you in another direction, I know that there are statistics tables in the master database which hold compilation counts, number of calls and other various statistics. Could I maybe periodically query them and then report on it, somehow?

Answer 1

no there are no notifications for this. you'll have to set up a trace and poll it every now and then.

Answer 2

I'd like to add to Mladen Prajdic's correct answer and to improve upon kevchadders' answer from SQL Server Central. The solution I propose below uses DBMail instead of SQLMail (which is used by SQLServerCentral's solution by way of the xp_sendmail call). Essentially, SQLMail uses MAPI and is harder to setup and DBMail uses SMTP and is easier to setup. Here's more information about the difference between the two.

I could not get SQL Server Central's solution to work in SQL 2005 and the solution below has only been tested on my installation of SQL 2005 - YMMV based on version.

First, you'll need a new UDF, which will translate the job id into the process id for a JOIN:

CREATE FUNCTION dbo.udf_SysJobs_GetProcessid(@job_id uniqueidentifier)
RETURNS VARCHAR(8)
AS
BEGIN
    RETURN (substring(left(@job_id,8),7,2) +
    substring(left(@job_id,8),5,2) +
    substring(left(@job_id,8),3,2) +
    substring(left(@job_id,8),1,2))
END

And then the sproc:

CREATE PROC sp_check_job_running 
    @job_name char(50),
    @minutes_allowed int, 
    @person_to_notify varchar(50) 

AS 

DECLARE @minutes_running int, 
    @message_text varchar(255)

SELECT @minutes_running = isnull(DATEDIFF(mi, p.last_batch, getdate()), 0)
FROM master..sysprocesses p
JOIN msdb..sysjobs j ON dbo.udf_sysjobs_getprocessid(j.job_id) = substring(p.program_name,32,8)
WHERE j.name = @job_name

IF @minutes_running > @minutes_allowed 
    BEGIN
      SELECT @message_text = ('Job ' + UPPER(SUBSTRING(@job_name,1,LEN(@job_name))) + ' has been running for ' + SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5)))) + ' minutes, which is over the allowed run time of ' + SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5)))) + ' minutes.') 
      EXEC msdb.dbo.sp_send_dbmail
        @recipients = @person_to_notify,
        @body = @message_text,
        @subject = 'Long-Running Job to Check' 
    END

This sproc can be easily scheduled as a SQL Server Agent job or any other method necessary. It takes no action if the job is not running or it is running within specified parameters. It sends the email if the job has run longer than specified.

e.g.

EXEC sp_check_job_running 'JobNameGoesHere', 5, '[email protected]'

HT: http://www.sqlserverspecialists.co.uk/blog/_archives/2008/11/26/3996346.html

Answer 3

If found this on sqlservercentral (it probably for an older version of SQL Server, but still might be relevant to 2008)

Alert Procedure for Long-Running Job http://www.sqlservercentral.com/scripts/Lock+and+Connection+Management/30144/

Text below if you cant access it.

For jobs that run periodically and should take only a short time to run, a DBA may want to know when the job has been running for an excessive time. In this case, just checking to see IF the job is running won't do; the ability to make sure that it hasn't been running for a long period is needed. Matching the job id to a process id in sysprocesses requires some re-arranging of the job id to do the match. This script creates a stored procedure that will accept a job name, maximum run time allowed, and email address to notify. It will then use the job name to re-string the job number and check sysprocesses (based on the process id being part of the program name) to determine the amount of time the job has been runing, then alert if that time is over the "time allowed" parameter.

CREATE proc sp_check_job_running
    @job_name       char(50),
    @minutes_allowed    int,
    @person_to_notify   varchar(50)
AS  

DECLARE @var1           char(1),
    @process_id     char(8),
    @job_id_char        char(8),
    @minutes_running    int,
    @message_text       varchar(255)

select @job_id_char = substring(CAST(job_id AS char(50)),1,8) 
from  msdb..sysjobs
where name = @job_name

select @process_id =    substring(@job_id_char,7,2) + 
            substring(@job_id_char,5,2) +
            substring(@job_id_char,3,2) +
            substring(@job_id_char,1,2)


select @minutes_running = DATEDIFF(minute,last_batch, getdate())
from master..sysprocesses
where program_name LIKE ('%0x' + @process_id +'%')

if @minutes_running > @minutes_allowed
  BEGIN
    select @message_text = ('Job ' 
    + UPPER(SUBSTRING(@job_name,1,LEN(@job_name)))
    + ' has been running for '
    + SUBSTRING(CAST(@minutes_running AS char(5)),1,LEN(CAST(@minutes_running AS char(5))))
    + ' minutes, which is over the allowed run time of '
    + SUBSTRING(CAST(@minutes_allowed AS char(5)),1,LEN(CAST(@minutes_allowed AS char(5)))) 
    + ' minutes.')
    EXEC master..xp_sendmail 
    @recipients = @person_to_notify, 
    @message = @message_text,
        @subject = 'Long-Running Job to Check'
  END

--  Typical job step syntax for job to do the checking

execute sp_check_job_running
      'JobThatSHouldBeDoneIn5Minutes', 
       5, 
       '[email protected]'

Answer 4

You could use a monitoring software like Nagios to periodically count the number of slow queries in your database.

I personally use the following query with the excellent PRTG. I set it to send me an email each time I have more than 5 queries with an execution time greater than 3 seconds each, or when the slowest query takes longer than 10 seconds:

SELECT total_elapsed_time / execution_count / 1000000 avg_elapsed_time, execution_count, creation_time, last_execution_time, 
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE total_elapsed_time / execution_count / 1000000 >= 3  -- filter queries that takes > 3 seconds
AND CHARINDEX('dm_exec_query_stats', st.text) = 0   -- In case this query is slow, we don't want it in the result set!
ORDER BY total_elapsed_time / execution_count DESC;

Disclaimer: this query is based on https://stackoverflow.com/a/820252/638040

Now if you want to monitor your Stored Procedures, then just use sys.dm_exec_procedure_stats instead of sys.dm_exec_sql_text (also remove the field creation_time from the selection and tweak the SUBSTRING on st.text)