Print Progress for the Query

Question

I have a query that executes 2 stored procedures; how do I find out how long it took to complete each procedure?

EXEC MyProcedure1

EXEC MyProcedure2

Answer 1

SQL server does not offer any inbuilt functionality to achieve this. What you could do is  create an audit table and log it

create table tblAudit(Procname varchar(100), startTime Datetime, endtime Datetime)

insert into tblAudit(Procname ,startTime ) values('MyProcedure1',getdate())

EXEC MyProcedure1

update tblAudit set endtime =getdate() where Procname ='MyProcedure1'

insert into tblAudit(Procname ,startTime ) values('MyProcedure2',getdate())

EXEC MyProcedure2

update tblAudit set endtime =getdate() where Procname ='MyProcedure2'

or you could use SQL profiler..

vt

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 2

If you are looking for this in your DEV you can use this

SELECT GETDATE()
EXEC MyProcedure1
SELECT GETDATE()
EXEC MyProcedure2
SELECT GETDATE()

or You could use  Statistics Time 

---

Satheesh
My Blog | How to ask questions in technical forum

Answer 3

You can use Sql Server Profiler for this purposes it provides a lot of useful info along the each executed query and Stored procedure as well

SQL Profiler displays the execution time for each event

An other straightforward way:

print convert(varchar, getdate(), 21)
EXEC MyProcedure1
print convert(varchar, getdate(), 21)
EXEC MyProcedure2
print convert(varchar, getdate(), 21)

Please Mark This As Answer if it helps to solve the issue