Post reply

T-SQL to get query duration

  • September 8, 2003 at 2:59 pm

    #106493

    Does anyone know how to get query duration as part of the result of a query? Similar to the way we can get a row count? I see in isqlw we can set the "Show Server Trace" option. But I want to automate the query and send the duration number to an output file.

    Thanks in advance

  • September 8, 2003 at 5:33 pm

    #473124

    I don't know if this can be done in t-sql but I would simply use a profile trace in profiler and have the output saved to a file.

    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • September 8, 2003 at 5:44 pm

    #473125

    You can see the duration using SET STATISTICS TIME ON and then executing the query, but this is probably only useful if you're manually running queries in QA or something like that.

    As Gary has already indicated, probably the best way to capture this information is to a trace file. You can do this using Profiler or using the various trace stored procedures and functions. You can pull the information out of a trace file, say into a trace table within SQL Server, at a later time (you could also write directly to a trace table, but writing to the file is typically faster).

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • September 9, 2003 at 10:08 am

    #473255

    Maybe something along these lines.

    Create Procedure testtime

    as

    declare @starttime smalldatetime

    Select @Starttime = getdate()

    waitfor delay '000:00:2' -- replace with your query

    select datediff(ms,@starttime, getdate())

    Store your queries results in a cursor and combine the cursor result with the dafediff statement to return your results and the time the query took.

    May slow things down a little storing your results it a cursor. Depends on whats important.

  • September 9, 2003 at 10:09 am

    #473257

    Thanks for your replies. This helps a lot!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply