Checking Execution Time of Stored Procedure

Hi All…I was invisible since so long..:)…But was too busy with my stuff so can’t post anything here…But today I learnt something new which I would like to share with you all…

Challenge:

I have written one Stored Procedure with 958 Lines..which deletes data from around >70 tables. [Ooh..]. But when I was executing it I was eager to check its execution time. And I found it..So here is the way to check execution time of your stored procedure.

Solution:

 
DECLARE @startproc DATETIME
DECLARE @endproc DATETIME
DECLARE @time INTEGER 

SELECT @startproc = getdate()  --take start time 

--execute sp

EXEC <YOUR SP NAME> <PARAMETERS> 

SELECT @endproc = getdate()  --take end time 

SELECT @time = DATEDIFF(millisecond, @startproc, @endproc)  --take difference in milliseconds.

PRINT str(@time) + ' Milliseconds.' 

I think the way which I did is self explanatory.

The main function which I would like to elaborate further is DATADIFF : To get difference between two dates:

Here i have used millisecond as an argument you can put DAY,MONTH YEAR etc. [Beware!!! If your SP Is taking DAY/MONTH/YEAR to run then you need to think for that SP…:) Just kidding].

Here is the Link for more arguments of DATEDIFF function :http://msdn.microsoft.com/en-us/library/ms189794.aspx