Earlier today I noticed a SQL Server Agent job taking a little longer than usual (or what I thought was longer than usual). Let's face it, we're not staring at the Job Activity monitor all day, so unless you've written a report to monitor job run times - on occasion you ask yourself "is that a normal run time for this thing?" The job I was curious about happened to be a job that runs throughout the day and should only have real work to do once or twice an hour - and it should run roughly the same amount of time on any given business day for a given hour (i.e. at 1:00 PM on any given business day, this thing should do the same amount of work).
So I came up with the following query to PIVOT the run duration on the hour the job executed:
DECLARE @JobName VARCHAR(255) = 'Agent Job Name' ;WITH JobHistory AS ( SELECT a.run_date ,a.run_time / 10000 AS [hour] ,(a.run_duration / 10000 * 60 * 60 -- Hours + a.run_duration % 10000 / 100 * 60 -- Minutes + a.run_duration % 100 -- Seconds ) / 60.0 AS [DurationMinutes] FROM msdb.dbo.sysjobhistory a WITH(NOLOCK) INNER JOIN msdb.dbo.sysjobs b WITH(NOLOCK) ON a.[job_id] = b.[job_id] AND b.[name] = @JobName AND step_id = 0 AND run_status = 1 ) SELECT * FROM JobHistory PIVOT ( SUM(DurationMinutes) FOR [hour] IN (,,,,, ,,,,,, ,,,,,, ,,,,,,) ) AS p
UPDATE: Added AND run_status = 1