Avatar of marrowyung
marrowyung
 asked on

find SQL job run average duration

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 ?
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Suresh Kurapati

8/22/2022 - Mon
Matt Bowler

"usually how long the record in sysjobhistory kept ?"

This is configurable in SQL Server Agent > properties > History
Matt Bowler

The run duration column is in HHMMSS format so that calculation just breaks that out and converts into seconds
Matt Bowler

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]
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
marrowyung

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

why * 3600 ? a year is 365, right ?
marrowyung

ASKER
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ão

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!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
"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ão

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.
marrowyung

ASKER
hi,

also why this part divided by job_id:

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

what is the purpose  ?
Your help has saved me hundreds of hours of internet surfing.
fblack61
Vitor Montalvão

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]
marrowyung

ASKER
"Because the run_duration is in milliseconds.

% is also a divide?
marrowyung

ASKER
"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 !
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
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ão

Did you try my solution?
marrowyung

ASKER
sorry don't have SQL server here. I just tried to understand that
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Vitor Montalvão

Ok. Then I will let Matt to explain to you his example.
marrowyung

ASKER
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?
marrowyung

ASKER
tks anyway victor, you have done the best to tell me.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Matt Bowler

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
marrowyung

ASKER
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ão

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Matt Bowler

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
tks all, might come back later and we now see the result is in sec.
Suresh Kurapati

Thanks for this script.Very helpful