SQL Agent Jobs by Average Run Duration

SQL Server Agent has a lot of great information stored away in the system tables, and though the GUI provides some interesting views of the data, it is necessary to roll your sleeves up and get querying the tables yourself. All pertinent tables are in the dbo schema in the msdb database, so querying for the data quite straightforward.

This week I am posting a query a day that I have written to get the average duration of the jobs run. The real kicker about getting this information is the run_duration column in the sysjobhistory table: the column itself is an int, so it takes a fair bit of data manipulation to retrieve the data in an accurate and legible format.

The query below returns both the duration and the start time of a job within the past 24 hours. This is useful in determining any long running jobs over the past 24 hours, which helped me when I was trying to locate a job that was generating a lot of transactions and causing the log backups to grow quite large. The reason this was a problem for me was that I had a log shipping database that was used for reporting on, and the creation of the transaction undo file (tuf file) in between each backup with STANDBY was causing the restore job to take a lot longer than normal. I’ll pick up on how to fix this issue in a later post, as the reporting database needed to be back up and online with as little down time as possible.

SELECT DISTINCT job_name = NAME
,db_run = rs.db
,run_duration = CONVERT(CHAR(20), DATEADD(second, rd, 0), 108)
,run_datetime = CONVERT (CHAR (50), rundatetime, 108)
FROM (
SELECT j.NAME
,rd = DATEDIFF(SECOND, 0, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_duration), 6), 5, 0, ':'), 3, 0, ':'))
,rundatetime = STUFF (STUFF (CONVERT(VARCHAR (20), h.run_date), 5, 0, '-'), 8, 0, '-') + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR (20), run_time), 6), 5, 0, ':'), 3, 0, ':')
,db = s.database_name
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs AS j ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE h.step_id = 0
and run_duration >= -1
GROUP BY j.NAME,s.database_name, s.step_name, run_duration, run_date, run_time
)
AS rs
WHERE CONVERT (CHAR (50), rundatetime, 108) > DATEADD (HOUR, -24, GETDATE())
AND rs.db IS NOT NULL
ORDER BY run_datetime desc

 

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Knows a thing or two about Pokémon. Knows too much about SQL Agent. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s