Agent Job Step Duration

The SQL Agent stores job step durations as an integer in HHMMSS format, which makes it painful to sort or aggregate. This query decodes run_date and run_duration from msdb..sysjobhistory into a real datetime start time, a time(0) duration, and the duration expressed in seconds - so you can finally answer "how long did this step take?" and sort by it. Edit the WHERE clause at the bottom to target the job and step you care about.

select
	*
from
	(
	select
		j.name AS JobName,
		jh.step_name AS StepName,
		Cast(SubString(Cast(jh.run_date as VarChar), 5, 2)
			+ '/' + SubString(Cast(jh.run_date as VarChar), 7, 2)
			+ '/' + SubString(Cast(jh.run_date as VarChar), 1, 4)
			+ ' ' + STUFF(STUFF(REPLACE(STR(jh.run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':')
		as DateTime) AS StartDate,

		CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS Duration,

		(Cast(Substring(Replace(SPACE(10 - LEN(run_duration)), ' ', '0') + CAST(run_duration as VARCHAR), 9, 2) as Int))
			+ (Cast(Substring(Replace(SPACE(10 - LEN(run_duration)), ' ', '0') + CAST(run_duration as VARCHAR), 7, 2) as Int) * 60)
			+ (Cast(Substring(Replace(SPACE(10 - LEN(run_duration)), ' ', '0') + CAST(run_duration as VARCHAR), 1, 6) as Int) * 3600) AS [Duration Seconds]
	from
		msdb..sysjobhistory jh
	join msdb..sysjobs j
		on jh.job_id = j.job_id
	) as Details
where
	JobName = 'ETLEngine.Synchronize->Production'
	and StepName = '(Job outcome)'
order by
	StartDate desc