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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matt BowlerDB team leadCommented:
"usually how long the record in sysjobhistory kept ?"

This is configurable in SQL Server Agent > properties > History
0
Matt BowlerDB team leadCommented:
The run duration column is in HHMMSS format so that calculation just breaks that out and converts into seconds
0
Matt BowlerDB team leadCommented:
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]
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

marrowyungSenior Technical architecture (Data)Author Commented:
sorry , what is @time % 10000 is about? I dont understand.

why * 3600 ? a year is 365, right ?
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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!
0
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])
?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
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  ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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]
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Because the run_duration is in milliseconds.

% is also a divide?
0
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 !
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
% is also a divide?
Yes but an integer divide. Check the difference:
  • 12 % 5 = 2
  • 12 / 5 = 2.4
0
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].
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try my solution?
0
marrowyungSenior Technical architecture (Data)Author Commented:
sorry don't have SQL server here. I just tried to understand that
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Then I will let Matt to explain to you his example.
0
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?
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks anyway victor, you have done the best to tell me.
0
Matt BowlerDB team leadCommented:
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
0
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 ?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
0
Matt BowlerDB team leadCommented:
""hours * 10000 + minutes * 100 + seconds"

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

No. See below.

""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 ?"

Yes - I am referring to the example that I used which started with a test run_duration value in HHMMSS format, and the calculation used to get that format. I was hoping that showing the calculation would make it clearer where the 10000 comes from :)

The run_duration column from sysjobhistory is not in seconds, it is a strange hybrid integer. The first two digits are the hour, the second two are the minutes and the last two are seconds. It's not easy to deal with this value in a straightforward way, hence the two most common options are to break out the three parts mathematically by dividing by 10000, and using modulo arithmetic as in your original post. The other way would be to treat the integer as a string, break out the three parts and then convert back to the appropriate format (or just output as a string) as in your most recent post.

You should feel free to use which ever method makes most sense to you. My preference would be to use the mathematical method as this would be likely to perform better.

I think that your original question has been answered?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
tks all, might come back later and we now see the result is in sec.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.