Average Job Execution Time

• TheRedneckDBA

  SSChampion

  Points: 14001

  More actions

  October 20, 2009 at 7:32 am

  #136692

  Anyone know of a decent way to get the average time in seconds that a job usually executes?

  I've got this query to display historical info, but the run_duration is in hhmmss format, so taking the average of it when grouped by job name isn't accurate.

  SELECT

  J.name,

  h.run_duration

  FROM

  msdb.dbo.sysjobhistory H

  INNER JOIN msdb.dbo.sysjobs J

  ON H.job_id = J.Job_id

  WHERE

  H.step_name LIKE '%ZIP%'

  ORDER BY J.name

  The Redneck DBA

• Steve Jones - SSC Editor

  SSC Guru

  Points: 735678

  More actions

  October 20, 2009 at 7:49 am

  #1068201

  You'd have to cast it out, separate the parts, get the totals.

  So for seconds

  cast( substring( cast(run_duration) as char(6), 5, 2) as int)

  You can get minutes * 60 (for seconds)

  cast( substring( cast(run_duration) as char(6), 4, 2) as int) * 60