It’s sometimes useful to know how long on average each SQL Server Agent Job takes and what the variation in running time is. You might want to increase how much Agent job history that SQL Server retains (the defaults are quite low).
-- Agent job history over all runs in retained in history
declare @tmp_sp_help_jobhistory table
(
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
)
insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory @mode='FULL'
;with cteRuns as
(
SELECT
tshj.job_name AS [JobName],
convert(datetime,
stuff(stuff(cast(tshj.run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + tshj.run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) AS [RunDate],
( run_duration % 100 + ((run_duration / 100) % 100) * 60 + ((run_duration / 10000) % 100) * 3600 +
((run_duration / 1000000) % 100) * 86400 )
AS [RunDurationSeconds]
FROM @tmp_sp_help_jobhistory as tshj
where step_ID = 0 and run_status = 1
)
, cteAggRuns as
(
SELECT
JobName,
MAX(RunDate) AS LastRunDate,
AVG(RunDurationSeconds) AS [AverageRunDurationSeconds],
Min(RunDurationSeconds) AS [MinDurationAverageSeconds],
Max(RunDurationSeconds) AS [MaxRunDurationSeconds],
COUNT(jobname) as NumRunsInHistory,
sqrt(var(RunDurationSeconds)) AS [stdRunDurationSeconds]
FROM cteRuns
group by JobName
)
select
JobName,
LastRunDate,
AverageRunDurationSeconds,
MinDurationAverageSeconds,
MaxRunDurationSeconds,
NumRunsInHistory,
stdRunDurationSeconds
from cteAggRuns
ORDER BY JobName
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Similarly you might want to get this information for just the last 5 runs say (for instance, when you’ve altered jobs):
declare @tmp_sp_help_jobhistory table
(
instance_id int null,
job_id uniqueidentifier null,
job_name sysname null,
step_id int null,
step_name sysname null,
sql_message_id int null,
sql_severity int null,
message nvarchar(4000) null,
run_status int null,
run_date int null,
run_time int null,
run_duration int null,
operator_emailed sysname null,
operator_netsent sysname null,
operator_paged sysname null,
retries_attempted int null,
server sysname null
)
insert into @tmp_sp_help_jobhistory
exec msdb.dbo.sp_help_jobhistory @mode='FULL'
;with cteRuns as
(
SELECT
job_name AS [JobName],
( run_duration % 100 + ((run_duration / 100) % 100) * 60 + ((run_duration / 10000) % 100) * 3600 +
((run_duration / 1000000) % 100) * 86400 )
AS [RunDurationSeconds],
convert(datetime,
stuff(stuff(cast(run_date as nchar(8)), 7, 0, '-'), 5, 0, '-') + N' ' +
stuff(stuff(substring(cast(1000000 + run_time as nchar(7)), 2, 6), 5, 0, ':'), 3, 0, ':'),
120) AS [RunDate]
FROM @tmp_sp_help_jobhistory
where step_ID = 0 and run_status = 1
)
, cteLast5Runs as
(
SELECT
ROW_NUMBER() over (partition by JobName order by RunDate DESC) as rownum,
JobName,
RunDurationSeconds,
RunDate
FROM cteRuns
)
SELECT
JobName,
AVG(RunDurationSeconds) AS [AverageRunDurationSeconds],
Min(RunDurationSeconds) AS [MinDurationAverageSeconds],
Max(RunDurationSeconds) AS [MaxRunDurationSeconds],
COUNT(jobname) as NumRunsInHistory,
sqrt(var(RunDurationSeconds)) AS [stdRunDurationSeconds]
FROM cteLast5Runs
where rownum between 1 AND 5
group by JobName
ORDER BY JobName
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/white-space: pre;/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
(ProjectDir)\FxCop
(ProgramFiles)\Microsoft FxCop 1.36\Xml\VSConsoleOutput.xsl"