tim laqua dot com Thoughts and Code from Tim Laqua

Trending SQL Server Agent Job Duration by Hour

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  ([00],[01],[02],[03],[04],[05]
        ,[06],[07],[08],[09],[10],[11]
        ,[12],[13],[14],[15],[16],[17]
        ,[18],[19],[20],[21],[22],[23])
  ) AS p

UPDATE: Added AND run_status = 1

Then made a table (not sure why, but I can't resist doing the heatmap thing):

click the image to enlarge

And, of course, the chart so you can point at a picture should you have to explain something to somebody in the future:

click the image to enlarge