Procedure time executing counter

Question

I have got a procedure which inserts data from one table to other and one time it takes from example 5 minutes and next time for example 15 minutes. I want to write code that create a log in my log table when procedure will take more then 10 minutes. Is exists any function or time counter in ms sql that I can use?

Answer 1

Add the following lines into your SP and it should work:

ALTER PROCEDURE YourSP
    AS
BEGIN
DECLARE @StartTime AS DATETIME = GETDATE();
... <Your current lines>
IF DATEDIFF(mi, @StartTime, GETDATE()) > 10
    INSERT INTO LogTable <YourFields>, MinutesSpent 
    VALUES <YourValues>, DATEDIFF(mi, @StartTime, GETDATE())
END

Answer 2

Why would you only log particular calls to the stored procedure? You should log all calls and filter out the ones that you want. This week you might be interesting in timings longer than 10 minutes. Next week, the data might grow and it might be 12 minutes. Or you might change the code to make it more efficient, and it should finish in 2 minutes.

If you are only interested in timing, I would write a rather generic log table, something like this:

create table spTimeLog (
    procedureName varchar(255),
    startDateTime datetime
    endDateTime datetime,
    createdAt datetime default getdate()
);

create procedure usp_proc . . .
begin
    declare @StartTime datetime = getdate();
    . . .

    insert into spTimeLog (procedureName, startDateTime, endDateTime)
        values ('usp_proc', StartTime, getdate());
end;

Then you can get the information you want when you query the table:

select count(*)
from spTimeLog tl
where tl.procedureName = 'usp_proc' and
      endDateTime > dateadd(minute, 10, startDateTime);

In general, when I write stored procedures for a real application, the stored procedures generate audit logs when they enter and exit -- both successfully and when they fail.