We help IT Professionals succeed at work.

find SQL job run average duration

1,939 Views
Last Modified: 2020-10-14
hi,

right now want to check SQL job run duration and I have diff version of SQL script to do it. I find this one is good:

SELECT	 [JobName] = [jobs].[name]
		,[Category] = [categories].[name]
		,[Owner] = SUSER_SNAME([jobs].[owner_sid])
		,[Enabled] = CASE [jobs].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Scheduled] = CASE [schedule].[enabled] WHEN 1 THEN 'Yes' ELSE 'No' END
		,[Description] = [jobs].[description]
		,[Occurs] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'Once'
					WHEN   4 THEN 'Daily'
					WHEN   8 THEN 'Weekly'
					WHEN  16 THEN 'Monthly'
					WHEN  32 THEN 'Monthly relative'
					WHEN  64 THEN 'When SQL Server Agent starts'
					WHEN 128 THEN 'Start whenever the CPU(s) become idle' 
					ELSE ''
				END
		,[Occurs_detail] = 
				CASE [schedule].[freq_type]
					WHEN   1 THEN 'O'
					WHEN   4 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' day(s)'
					WHEN   8 THEN 'Every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' weeks(s) on ' + 
						LEFT(
							CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
							CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END , 
							LEN(
								CASE WHEN [schedule].[freq_interval] &  1 =  1 THEN 'Sunday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  2 =  2 THEN 'Monday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  4 =  4 THEN 'Tuesday, '   ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] &  8 =  8 THEN 'Wednesday, ' ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 16 = 16 THEN 'Thursday, '  ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 32 = 32 THEN 'Friday, '    ELSE '' END + 
								CASE WHEN [schedule].[freq_interval] & 64 = 64 THEN 'Saturday, '  ELSE '' END 
							) - 1
						)
					WHEN  16 THEN 'Day ' + CONVERT(VARCHAR, [schedule].[freq_interval]) + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)'
					WHEN  32 THEN 'The ' + 
							CASE [schedule].[freq_relative_interval]
								WHEN  1 THEN 'First'
								WHEN  2 THEN 'Second'
								WHEN  4 THEN 'Third'
								WHEN  8 THEN 'Fourth'
								WHEN 16 THEN 'Last' 
							END +
							CASE [schedule].[freq_interval]
								WHEN  1 THEN ' Sunday'
								WHEN  2 THEN ' Monday'
								WHEN  3 THEN ' Tuesday'
								WHEN  4 THEN ' Wednesday'
								WHEN  5 THEN ' Thursday'
								WHEN  6 THEN ' Friday'
								WHEN  7 THEN ' Saturday'
								WHEN  8 THEN ' Day'
								WHEN  9 THEN ' Weekday'
								WHEN 10 THEN ' Weekend Day' 
							END + ' of every ' + CONVERT(VARCHAR, [schedule].[freq_recurrence_factor]) + ' month(s)' 
					ELSE ''
				END
		,[Frequency] = 
				CASE [schedule].[freq_subday_type]
					WHEN 1 THEN 'Occurs once at ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 2 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Seconds(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 4 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Minute(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					WHEN 8 THEN 'Occurs every ' + 
								CONVERT(VARCHAR, [schedule].[freq_subday_interval]) + ' Hour(s) between ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + 
								STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [schedule].[active_end_time]), 6), 5, 0, ':'), 3, 0, ':')
					ELSE ''
				END
		,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])
		,[Next_Run_Date] = 
				CASE [jobschedule].[next_run_date]
					WHEN 0 THEN CONVERT(DATETIME, '1900/1/1')
					ELSE CONVERT(DATETIME, CONVERT(CHAR(8), [jobschedule].[next_run_date], 112) + ' ' + 
						 STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), [jobschedule].[next_run_time]), 6), 5, 0, ':'), 3, 0, ':'))
				END
FROM	 [msdb].[dbo].[sysjobs] AS [jobs] WITh(NOLOCK) 
		 LEFT OUTER JOIN [msdb].[dbo].[sysjobschedules] AS [jobschedule] WITh(NOLOCK) 
				 ON [jobs].[job_id] = [jobschedule].[job_id] 
		 LEFT OUTER JOIN [msdb].[dbo].[sysschedules] AS [schedule] WITh(NOLOCK) 
				 ON [jobschedule].[schedule_id] = [schedule].[schedule_id] 
		 INNER JOIN [msdb].[dbo].[syscategories] [categories] WITh(NOLOCK) 
				 ON [jobs].[category_id] = [categories].[category_id] 
		 LEFT OUTER JOIN 
					(	SELECT	 [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) + 
																(([run_duration] % 10000) / 100 * 60) + 
																 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
						FROM	 [msdb].[dbo].[sysjobhistory] WITh(NOLOCK)
						WHERE	 [step_id] = 0 
						GROUP BY [job_id]
					 ) AS [jobhistory] 
				 ON [jobhistory].[job_id] = [jobs].[job_id];
GO

Open in new window


but one thing, the average duration by this script is not the same from what we saw from the SQL job history,  so average run time by this script can be very diff from what by summing up the run time from SQL jobs history and divided by number job history.

so I would like to know in the script, how it calculate the average run duration, I see this:

SUM((([run_duration] / 10000 * 3600) +
                                                                                                (([run_duration] % 10000) / 100 * 60) +
                                                                                                 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])

is it count using 1 year figure? or just 100 days ? then what is 10000 is about?


usually how long the record in sysjobhistory kept ?
Comment
Watch Question

Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
"usually how long the record in sysjobhistory kept ?"

This is configurable in SQL Server Agent > properties > History
Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
The run duration column is in HHMMSS format so that calculation just breaks that out and converts into seconds
Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
The following breakdown might make it clearer (and then the code sums and averages the various values):

DECLARE @time INT = 020304

SELECT
@time / 10000 AS [hours],
(@time / 10000 * 3600) AS [hours in seconds],
(@time % 10000) / 100 AS [minutes],
((@time % 10000) / 100 * 60) AS [minutes in seconds],
(@time % 10000) % 100 AS [seconds],
(@time / 10000 * 3600) + ((@time % 10000) / 100 * 60) + (@time % 10000) % 100 * 1.0 AS [total seconds]
marrowyungSenior Technical architecture (Data)

Author

Commented:
sorry , what is @time % 10000 is about? I dont understand.

why * 3600 ? a year is 365, right ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
but the problem is the output column , AvgDurationInSec, is in sec, right?  not in MMSS anymore, agree?

I am worrying about why we need this:

CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])

converting the HHMMSS avgduration to sec and still need 10.2 format ? so xxx.xx second ? not xxx minutes and xx sec anymore ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
As Matt commented, the duration is in seconds. If you want to see it as HH:MM:SS, use the following formula:
,[AvgDuration] = CAST(DATEADD(SECOND, [jobhistory].[AvgDuration], 0) AS time)

Open in new window

The above code should replace the line with the code:
,[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])

Open in new window

NOTE: Do not change the subselect that has the formula!
marrowyungSenior Technical architecture (Data)

Author

Commented:
"The above code should replace the line with the code:"

you are saying if it has to convert to second, it is [AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration]), right?

what % means here:

(@time % 10000)  ?

why divided by 10000 ?

I am not sure why this make:
1) @time / 10000 is [hours].
2) (@time % 10000) / 100 is [minutes].
3) (@time % 10000) % 100 is [seconds]

 "Do not change the subselect that has the formula!"

this one:

SELECT       [job_id], [AvgDuration] = (SUM((([run_duration] / 10000 * 3600) +
                                                                                                (([run_duration] % 10000) / 100 * 60) +
                                                                                                 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
you are saying if it has to convert to second
No. The formula in the subselect is what's converting to seconds.

why divided by 10000 ?
Because the run_duration is in milliseconds.

this one:
Yes. That one. Do not touch it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

also why this part divided by job_id:

 ([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])

what is the purpose  ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
also why this part divided by job_id:
Is not dividing by job_id but by the number of the same job_id, so you can get the average. The alias names says everything: [AvgDuration]
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Because the run_duration is in milliseconds.

% is also a divide?
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Is not dividing by job_id but by the number of the same job_id, so you can get the average. The alias names says everything: [AvgDuration]

good !
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks,

by matt's example:

DECLARE @time INT = 020304

I assume that one is HHMMSS example as run_Duration is in HHMMSS and I can't see why:
@time / 10000 is [hours].
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Did you try my solution?
marrowyungSenior Technical architecture (Data)

Author

Commented:
sorry don't have SQL server here. I just tried to understand that
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Ok. Then I will let Matt to explain to you his example.
marrowyungSenior Technical architecture (Data)

Author

Commented:
but actually you have told me that :

Check the difference:
12 % 5 = 2
12 / 5 = 2.4

so I trust you on that but I just didn't know why we need to % or / 10000, the runduration /10000 to give hour. the @time in Matt's example is in hours minutes and second format, right?
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks anyway victor, you have done the best to tell me.
Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
Run_duration is not milliseconds. Run_duration is hours * 10000 + minutes * 100 + seconds

So 2 hours, 3 minutes and 4 seconds = 2*10000 + 3* 100 + 4 = 020304 (as in my example).

We divide by 10000 to isolate the hours (rounding is implicit because we are using integers throughout). 20304/10000 = 2 as an integer
marrowyungSenior Technical architecture (Data)

Author

Commented:
hi,

tks for your reply.

"Run_duration is hours * 10000 + minutes * 100 + seconds"

Run_duration still the column in [sysjobhistory], right?

I found this script from https://social.msdn.microsoft.com/Forums/sqlserver/en-US/57f325bc-d7a1-4036-b915-e7e8646a0853/question-about-runduration-in-sysjobhistory-table?forum=sqldatabaseengine

SET NOCOUNT ON;

 SELECT sj.name,
        sh.run_date,
        sh.step_name,
        STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
        STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS)  '
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id

Open in new window


to make the run_duration much easier to read and that post also said this column is not easy to understand.

by this:

"hours * 10000 + minutes * 100 + seconds"

the runs duration in the output will be in second, right ?

"So 2 hours, 3 minutes and 4 seconds = 2*10000 + 3* 100 + 4 = 020304 (as in my example)."

I am sorry, you are referring to 020304 the final output in HHMMSS format, right ?
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Run_duration is not milliseconds. Run_duration is hours * 10000 + minutes * 100 + seconds
Matt, you're right. Sorry for my confusion. This can be stated in MSDN article for sysjobhistory:
run_duration  int Elapsed time in the execution of the job or step in HHMMSS format
Database Reliability Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all, might come back later and we now see the result is in sec.
Thanks for this script.Very helpful