none
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

    Wednesday, August 13, 2014 9:03 PM

Answers

  • 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



    Wednesday, August 13, 2014 9:36 PM
  • 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


    Thursday, August 14, 2014 5:34 AM
  • 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
    Thursday, August 14, 2014 7:11 AM

All replies

  • 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



    Wednesday, August 13, 2014 9:36 PM
  • 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


    Thursday, August 14, 2014 5:34 AM
  • 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
    Thursday, August 14, 2014 7:11 AM