Want to display the execution time of a SP

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 2000 Forums

SQL Server Development (2000)

Want to display the execution time of a SP

Author

Topic

amitsyadav
Starting Member

15 Posts

Posted - 2006-09-12 : 09:54:06

Hi All,

I want to capture the total time taken by SQL Server for execution of a Stored Procedure.

Please suggest.

Thanks in advance.
Amit

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 10:05:32

run your procedure through query analyser and see the time taken for execution at the button of the screen.

Chirag

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-12 : 10:09:13

declare @st_dt datetime
declare @end_dt datetime
select @st_dt = getdate()
exec your_procedure
select @end_dt = getdate()
--use @end_dt - @st_dt with datediff function

Thanks

Sanjeev Shrestha
12/17/1971

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 10:10:16

set statistics TIME on

select * from f_table_number_range(1, 100000)

set statistics TIME off

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 10:10:56

quote:
Originally posted by chiragkhabaria

run your procedure through query analyser and see the time taken for execution at the button of the screen.

Not 1001, 1002, 1003, 1004...?

Peter Larsson
Helsingborg, Sweden

amitsyadav
Starting Member

15 Posts

Posted - 2006-09-12 : 10:25:50

quote:
Originally posted by Peso

set statistics TIME on

select * from f_table_number_range(1, 100000)

set statistics TIME off

Peter Larsson
Helsingborg, Sweden

Peter,

Thanks but i am not getting you clearly. Actually I want to capture the total time taken in the execution of a SP by the SQL server in my report.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 10:31:42

Now we finally are getting some real information about how the information is going to be used!

Use the suggestion by sanjnep.

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-12 : 10:40:52

I like to have a sproc log that get's written to during the spoc's exit routine...it's lo the name of the sproc, the length of execution, and when the sproc was called...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

amitsyadav
Starting Member

15 Posts

Posted - 2006-09-12 : 10:41:44

thanks Sanjeev.

quote:
Originally posted by sanjnep

declare @st_dt datetime
declare @end_dt datetime
select @st_dt = getdate()
exec your_procedure
select @end_dt = getdate()
--use @end_dt - @st_dt with datediff function

Thanks

Sanjeev Shrestha
12/17/1971

Kristen
Test

22859 Posts

Posted - 2006-09-12 : 12:14:05

or

CREATE PROCEDURE MySProc
... parameters ...
@ElapsedTime int = NULL OUTPUT
AS
DECLARE @StartTime datetime
SELECT @StartTime = GetDate()
... do stuff ...
SELECT @ElapsedTime = DATEDIFF(millisecond, @StartTime, GetDate())
RETURN
GO

or you could SELECT the elapsed time to it is part of the resultset, rather than an OUTPUT parameter from your SProc, if that suits your reporting environment better.

Kristen

amitsyadav
Starting Member

15 Posts

Posted - 2006-09-13 : 03:50:02

Thanks everyone, I just want to check if this could be done in another way.

Thanks once again.
Amit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources