SQL Server Long Query Script

• yusufkahveci

  SSC-Addicted

  Points: 466

  More actions

  January 8, 2018 at 1:07 pm

  #409934

  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

  More actions

  May 30, 2018 at 4:11 am

  #1992269

  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: 33512

  More actions

  May 30, 2018 at 5:45 am

  #1992278

  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’! **