locked
how to get total time taken in executing query RRS feed

  • Question

  • i want to display the time consumed in executing a query, please tell me how to find the same?
    Regards Kumar Gaurav.
    Tuesday, November 15, 2011 8:04 PM

Answers

  • You can use SQL Server profiler to do this, you would for example include the SQL:BatchCompleted and RPC:Completed events and include the startTime, endTime columns. Another option is to add "SET STATISTICS TIME ON" to start of your query, see the following for more info...

    http://msdn.microsoft.com/en-us/library/ms190287.aspx

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by Naomi N Wednesday, November 16, 2011 4:45 AM
    • Marked as answer by Peja Tao Tuesday, November 22, 2011 5:42 AM
    Wednesday, November 16, 2011 3:34 AM
  • If you want to measure a particular query you're testing in SSMS, you can save current_timestamp into a variable and then get the difference using datediff function between current time and saved variable.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Peja Tao Wednesday, November 16, 2011 8:48 AM
    • Marked as answer by Peja Tao Tuesday, November 22, 2011 5:42 AM
    Wednesday, November 16, 2011 4:45 AM

All replies

  • You can use SQL Server profiler to do this, you would for example include the SQL:BatchCompleted and RPC:Completed events and include the startTime, endTime columns. Another option is to add "SET STATISTICS TIME ON" to start of your query, see the following for more info...

    http://msdn.microsoft.com/en-us/library/ms190287.aspx

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (MCITP (2005/2008), MCAD, ITILv3, OCA 11g) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by Naomi N Wednesday, November 16, 2011 4:45 AM
    • Marked as answer by Peja Tao Tuesday, November 22, 2011 5:42 AM
    Wednesday, November 16, 2011 3:34 AM
  • If you want to measure a particular query you're testing in SSMS, you can save current_timestamp into a variable and then get the difference using datediff function between current time and saved variable.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Peja Tao Wednesday, November 16, 2011 8:48 AM
    • Marked as answer by Peja Tao Tuesday, November 22, 2011 5:42 AM
    Wednesday, November 16, 2011 4:45 AM
  • But how can i store the time in any variable that i can get on executing command?

    Please write the query


    Regards Kumar Gaurav.
    Wednesday, November 16, 2011 10:34 AM
  •  

    declare @StartTime datetime -- you can use higher precision in SQL 2008
    
    set @StartTime = CURRENT_TIMESTAMP
    
     -- query goes here
    
    print datediff(second, @StartTime, CURRENT_TIMESTAMP) -- execution time in seconds
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Wednesday, November 16, 2011 1:07 PM