Problem Statement: Script to find average SQL Server Agent jobs run time.
Solution:
SET TRANSACTION isolation level READ uncommitted;
WITH cte_AR
AS (SELECT j.NAME AS 'JobName',
Cast(( ( run_duration / 10000 * 3600 +
( run_duration / 100 )%100 * 60 +
run_duration%100
+ 31 ) / 60.0 ) AS DECIMAL(18, 2)) AS
'RunDurationMin'
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
WHERE
--j.enabled = 1 and --Only Enabled Jobs
run_status = 1 --Successfull
AND step_id = 0
AND run_date >= '20161210'),
cte_std
AS (SELECT Jobname,
isnull(Cast(Stdev(RunDurationMin) AS DECIMAL(18, 2)), 0) AS
StddevMin
FROM cte_Ar --where ntiled between 2 and 9
GROUP BY Jobname),
cte_avg
AS (SELECT Jobname,
isnull(Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)), 0) AS
StdAvgMin
FROM cte_Ar --where ntiled between 2 and 9
GROUP BY Jobname),
cte_Final
AS (SELECT cte_AR.Jobname,
Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)) AS FinalAvginMin
--,count(1) cnt
FROM cte_AR
JOIN cte_std
ON cte_AR.jobname = cte_std.jobname
JOIN cte_avg
ON cte_AR.jobname = cte_avg.jobname
WHERE RunDurationMin BETWEEN StdAvgMin - StddevMin AND
StdAvgMin + StddevMin
GROUP BY cte_AR.Jobname)
--select *from cte_final
SELECT @@servername ServerName,
cte_ar.JobName,
FinalAvgInMin,
Count(1) RunCount
FROM cte_Final
RIGHT JOIN cte_AR
ON cte_AR.jobname = cte_Final.JobName
--WHERE cte_ar.jobname IN ('abc')
GROUP BY cte_ar.jobname,
FinalAvginMin
ORDER BY jobname
