Another sysjobhistory query

Question

Another sysjobhistory query

Dave Daiker

SSC Journeyman

Points: 82
More actions
June 13, 2016 at 1:19 pm

#308353

For many years I've wanted to be able to visualize how long my SQL Agent jobs are taking to run over time. I've wanted to see them both as a total duration of the job and broken down by each step. I've seen some tools (some free some paid) that get close but none that give me exactly what I want. I've also tried to write queries using sysjobs and sysjobhistory to pull out the data that I want but infuriatingly there is no way straight forward way to tie the execution of steps in sysjobhistory to each other. What I mean by this is that there is a job_id field linking it to a job but nothing linking it to an execution of that job. A human can easily look at execution times, etc and see which steps go with what executions but that's not good enough if you want to visualize your executions on a graph, etc.
Today I scoured the internet again and came up with nothing so I buckled down and came up with a solution myself. I was able to tie a step to it's siblings by calculating the begin and end times of a job execution and then assuming if a step's execution falls in this time frame then it's part of the same job's execution. Be warned, this query can take some time to run (15 minutes on my production server). If you filter it to one job it runs much quicker. Because of this I used NOLOCK to not lock the tables so if you have jobs that are currently running you may get odd results.
A key point about sysjobhistory is that if the step_id is 0 then the row represents the execution of the whole job. If the step_id is not 0 then it's one of the individual steps. I use this to build a list of executions in a CTE calculating the begin and end date/time. I then join back to sysjobhistory getting the details of each step. If you only care about the info for the job execution as a whole just use the CTE by itself, no need to join back to sysjobhistory for the step details.
I use several tricks about getting run_date, run_time, run_duration and run_status into a usable form that I won't get into the details. You can search the internet on these field names and find lots of discussions if you are interested. The only thing I'll mention about this is that I could not use agent_datetime on run_duration because I have jobs that took more than 24 hours and that blew up agent_datetime.
I use the FORMAT command so this is only going to work in SQL 2012+. I also use the undocumented dbo.agent_datetime and I'm not sure when MS added that to MSDB. To use it in older versions you may have to figure out how to replace those two functions.
Be warned that JobInstanceNumber is just a row number counter and will change between executions of the query, you can't use it as a key to store off the data and refer to it later. I have it in the query to make creating the visualizations simpler.
I really only got this working today so there may be some issues with it that I haven't discovered, feel free to help me debug it if that is the case.
WITH JobInstance AS (
SELECT
JobID = j.job_id
,JobName = j.name
,JobInstanceNumber = ROW_NUMBER() OVER(ORDER BY j.job_id,h.run_date,h.run_time)
,JobInstanceStartDateTime = dbo.agent_datetime(h.run_date,h.run_time)
,JobInstanceDuration = FORMAT(run_duration,'00:00:00')
,JobInstanceDurationSeconds = run_duration/10000*3600 + run_duration/100%100*60 + run_duration%100
,JobInstanceEndDateTime = DATEADD(second,
run_duration/10000*3600 + run_duration/100%100*60 + run_duration%100,
dbo.agent_datetime(h.run_date,h.run_time) )
,JobInstanceStatus = CASE h.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' END
,JobInstanceMessage = h.message
FROM sysjobhistory AS h
INNER JOIN sysjobs AS j ON j.job_id = h.job_id
WHERE 1=1
AND h.step_id = 0 )
SELECT ji.*
,StepID = h.Step_ID
,StepName = h.step_name
,StepStartDateTime = dbo.agent_datetime(h.run_date,h.run_time)
,StepDuration = FORMAT(run_duration,'00:00:00')
,StepDurationSeconds = run_duration/10000*3600 + run_duration/100%100*60 + run_duration%100
,StepEndDateTime = DATEADD(second,
run_duration/10000*3600 + run_duration/100%100*60 + run_duration%100,
dbo.agent_datetime(h.run_date,h.run_time) )
,StepStatus = CASE h.run_status WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' END
,StepMessage = h.message
FROM JobInstance ji WITH (NOLOCK) INNER JOIN sysjobhistory h WITH (NOLOCK)
ONji.JobID = h.job_id
AND dbo.agent_datetime(h.run_date,h.run_time) BETWEEN ji.JobInstanceStartDateTime and ji.JobInstanceEndDateTime
AND h.step_id <> 0
ORDER BY JobName, JobInstanceStartDateTime,h.step_id

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

Dave Daiker (6/13/2016)
I use the FORMAT command so this is ...

First, thanks for the code. I'll suggest that you should write an article about it and submit it. They don't pay much on this site but it IS a chance to get published. See the "Write for us" link near the top left corner of this page.

On the thing I quoted... the FORMAT function is miserably slow compared to other functions (44 times slower than CONVERT, in most cases). I recommend not using it anywhere until they fix it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 2

About six weeks ago, I was having similar thoughts, and ended up figuring out a method to extract job history information into a form that can then be displayed in Outlook Calendars[/url]. In my case, this was to show when our storage back-end was particularly busy with backups, but the principle is the same. It might be of help / interest.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Dave Daiker SSC Journeyman Points: 82 More actions · Answer 3

Thanks for the feedback, I'll look into writing an article. As for the FORMAT being slow I'm not overly concerned in this case as the data is relatively small (1000's of rows) and doing something like that other ways requires a lot more code. If there is more data then it would be worth avoiding FORMAT.

The calendar view of SQL jobs is really nice, I actually found a couple of free utilities (Idera Job Manager and SQLSoft Sql Job Vis) that do something similar but they don't give you the calendar view the same way. I'll have to look into using this for my needs.