SQL Server Long Query Script

  • yusufkahveci

    SSC-Addicted

    Points: 466

    Comments posted to this topic are about the item SQL Server Long Query Script

    SQL Server Consultant
    yusufkahveci@sqlturkiye.com
    www.sqlturkiye.com

  • joseph.williams 87509

    SSC Rookie

    Points: 26

    Seems really useful thanks, but what unit of time are the max elapsed time and avg elapsed time in? (seconds, milliseconds etc)

    Thank you
    Joe

  • HanShi

    SSC-Dedicated

    Points: 33286

    joseph.williams 87509 - Wednesday, May 30, 2018 4:11 AM

    Seems really useful thanks, but what unit of time are the max elapsed time and avg elapsed time in? (seconds, milliseconds etc)

    Thank you
    Joe

    As from BOL / MSDN / docs:
    "max_elapsed_time........bigint........Maximum elapsed time, reported in microseconds (but only accurate to milliseconds), for any completed execution of this plan."

    If you repace the max-duration and avg fields with the SQL code below, the result will show the values converted to the TIME datatype (which is more human friendly readable)

    -- ,eqs.max_elapsed_time AS sqltr_maxelapsedTime
     ,cast(dateadd(MILLISECOND, eqs.max_elapsed_time/1000, '0:00:00') as time(3)) AS sqltr_maxelapsedTime
    -- ,ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0) AS sqltr_avgElapsedTime
     ,cast(dateadd(MILLISECOND, ISNULL(eqs.total_elapsed_time / NULLIF(eqs.execution_count, 0), 0)/1000, '0:00:00') as time(3)) AS sqltr_avgElapsedTime

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 3 posts - 1 through 3 (of 3 total)

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