How long do the parts of my job take?

When you schedule a job in SQL Server, you can see how long the job took by right clicking on the job and then clicking View History. If you want to see how long each step in the job took, you can click the little plus sign next to each instance of the job running and you’ll get a list of information about each step.

While this is very nice, what do you do if you want to keep the information longer than the history allows? Or if you want to share it with people or graph the information in various ways? Graphing can be a good way to tell if you’re really getting the performance improvements you’re expecting. Fortunately, there’s a way to query this data directly from SQL Server.

If you want to get the data in easily copyable or storable format, just query the two msdb tables: sysjobhistory and sysjobs. Between them, you can query by job name and get the time the job ran and the duration of every step. You’ll see the join in the query below. However, you should know there’s something weird about the way the data is presented.

If you were to run a query and return the columns run_time and run_duration, you might expect to get the time back formatted as a time and the run duration in seconds or milliseconds. What you will get back is two ints. If you ran your job at 3:15, you’ll see the number 31500 in the run_time column. If your job took 5 minutes and 47 seconds, you’ll see 547 in the run_duration column. Neither of these is formatted well for easy use. You’d want the run_time column to show the time in hours and minutes. You’d want the run_duration column to either show in seconds if you’re trying to graph it or do any math with it or you’d want it to be formatted like you’re used to seeing time. 547 looks like 5 hundred forty seven when it is actually 5 minutes 47 seconds.

In the following query, I change both columns to be more easily readable.

SELECT run_date, run_time, (mts * 60) + secs as run_duration_seconds
FROM
(SELECT run_date, run_time/100 as run_time, Right(run_duration, 2) as secs,
(run_duration – Right(run_duration, 2)) /100 as mts
FROM msdb..sysjobhistory H
INNER JOIN msdb..sysjobs J
ON J.job_id = H.job_id
WHERE J.[name] = ‘MyJob’
AND step_id = 0) Z
Order by run_date DESC

You can see in the subquery that I divide the run_time by 100 to get rid of the superfluous zeroes at the end. Since the rest of it is hours, minutes, seconds, it is easily formattable in whatever program you choose for display.

For run_time, I take the rightmost 2 characters as the seconds. I use a similar calculation to subtract the seconds from the integer, divide by 100 to get rid of the resulting zeroes and the rest is left as minutes. The final step is to multiply the minutes by 60 and add the seconds and you have a nicely graphable, base 10 integer of your step duration in seconds. I find this little transformation results in more usable data and it is easier to do it in the query than to try to do the calculation in Excel or other program later. I also find it better to do it before I store it so I don’t have to remember to do it later.

 

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>