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:
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 ?
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];
GObut 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 ?
The run duration column is in HHMMSS format so that calculation just breaks that out and converts into seconds
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]
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]
ASKER
sorry , what is @time % 10000 is about? I dont understand.
why * 3600 ? a year is 365, right ?
why * 3600 ? a year is 365, right ?
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 ?
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 ?
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),[AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration])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]
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])
?
you are saying if it has to convert to second, it is [AvgDurationInSec] = CONVERT(DECIMAL(10, 2), [jobhistory].[AvgDuration]
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])
?
you are saying if it has to convert to secondNo. 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.
ASKER
hi,
also why this part divided by job_id:
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
what is the purpose ?
also why this part divided by job_id:
([run_duration] % 10000) % 100)) * 1.0) / COUNT([job_id])
what is the purpose ?
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]
ASKER
"Because the run_duration is in milliseconds.
% is also a divide?
% is also a divide?
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 !
good !
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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].
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].
Did you try my solution?
ASKER
sorry don't have SQL server here. I just tried to understand that
Ok. Then I will let Matt to explain to you his example.
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?
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?
ASKER
tks anyway victor, you have done the best to tell me.
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
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
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
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 ?
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_idto 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 ?
Run_duration is not milliseconds. Run_duration is hours * 10000 + minutes * 100 + secondsMatt, 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
tks all, might come back later and we now see the result is in sec.
Thanks for this script.Very helpful
This is configurable in SQL Server Agent > properties > History