SQL Sever Agent Monthly Job Run Time Averages


Sometime as a ETL developer or Database Administrator you will need to gain insight into SQL Agent job executions times. ThisĀ  insight can be used to proactively monitor the processing times of the various jobs running within your data environment.

Information about jobs execution times is stored in the MSDB database in table sysjobhistory. This table has the start time and the run duration times which I have used to create a report that will show the average job start and end times by month for all jobs running on a instance of SQL Server.

/**************************************************************************
Author Tywan Terrell
object type: Stored Procedure
Description: This procedure was created to find the average start and end 
       times for SQL Agent Jobs. This prcedure take one parameter
       @reporttype.
       
       @reporttype = 1 -> Monthly average by job.
       @reporttype = 2 -> Monthly average by Step  

       Example Call
       exec MonthlyJobAvgTimes @reporttype = 1
       go;
       exec MonthlyJobAvgTimes @reporttype = 2

***************************************************************************/
go;
Create Procedure MonthlyJobAvgTimes( @reporttype int)
as
begin

if @reporttype = 1 
begin
IF OBJECT_ID('tempdb..#MONTHLYJOBAVGTIME') IS NOT NULL
    DROP TABLE #MONTHLYJOBAVGTIME

;WITH JOBHISTORY AS
(
 SELECT sj.name,
        sh.run_date,
        sh.step_name,
    CAST(CONVERT(VARCHAR(10),RUN_DATE)AS DATE) AS 'FORMATEDDATE',
        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
)
, JOBHISTORYSECONDS AS (

SELECT *, SUBSTRING(CONVERT (VARCHAR,FORMATEDDATE),6,2) AS MONTHNUMBER	, substring(cast (FORMATEDDATE as varchar),1,8) +'01 00:00:00'	as FOD
    ,( SUBSTRING(RUN_TIME,1,2) *3600)+ (SUBSTRING(RUN_TIME,4,2)* 60) +SUBSTRING(RUN_TIME,7,2) AS 'RUN_START_TIME(SEC)'
    , (SUBSTRING([run_duration (DD:HH:MM:SS)  ],1,2)*3600*24)+ (SUBSTRING([run_duration (DD:HH:MM:SS)  ],4,2)*3600)+(SUBSTRING([run_duration (DD:HH:MM:SS)  ],7,2)*60)+SUBSTRING([run_duration (DD:HH:MM:SS)  ],10,2) AS [run_duration_SEC ]
FROM JOBHISTORY  
)
, MONTHLYJOBAVGTIME AS (

SELECT 
name,MONTHNUMBER, FOD ,AVG([RUN_START_TIME(SEC)])  AS M_AVG_START_SEC,AVG([run_duration_SEC ]) AS M_AVG_DURATION
FROM JOBHISTORYSECONDS JS
GROUP BY 
JS.name,JS.MONTHNUMBER,FOD
)

SELECT 'MONTHLY-AVERAGE' AS AVG_TYPE,*, DATEADD(S,M_AVG_START_SEC, FOD )AS M_AVG_START_TIME, DATEADD(S,M_AVG_DURATION, DATEADD(S,M_AVG_START_SEC, FOD )) AS M_AVG_END_TIME
INTO #MONTHLYJOBAVGTIME
FROM 
  MONTHLYJOBAVGTIME



sELECT name,AvgDesc, [1] AS Jan, [2] as Feb,[3] as Mar,[4] as Apr,[5] as May,[6] as Jun,[7]as Jul,[8] as Aug,[9] As Sept,[10] as Oct,[11] as NOv,[12] as [Dec] 
FROM 
(SELECT name ,'StartTime' as AvgDesc,  CAST(MONTHNUMBER AS INT) AS MONTHNUMBER , (M_AVG_START_TIME)as M_AVG_START_TIME
   FROM #MONTHLYJOBAVGTIME) AS SOURCEPIVOT
PIVOT
(
  MAX(M_AVG_START_TIME)
  FOR MONTHNUMBER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) AS PIVOTTABLE
union all 
--Endtime Pivot 
sELECT name,AvgDesc, [1] AS Jan, [2] as Feb,[3] as Mar,[4] as Apr,[5] as May,[6] as Jun,[7]as Jul,[8] as Aug,[9] As Sept,[10] as Oct,[11] as NOv,[12] as [Dec] 
FROM 
(SELECT name ,'EndTime' as AvgDesc,  CAST(MONTHNUMBER AS INT) AS MONTHNUMBER ,  M_AVG_END_TIME FROM #MONTHLYJOBAVGTIME) AS SOURCEPIVOT
PIVOT
(
  MAX(M_AVG_END_TIME)
  FOR MONTHNUMBER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) AS PIVOTTABLE
order by 1
end

if @reporttype = 2
begin
--

IF OBJECT_ID('tempdb..#MONTHLYJOB_STEPAVGTIME') IS NOT NULL
    DROP TABLE #MONTHLYJOB_STEPAVGTIME


;WITH JOBHISTORY AS
(
 SELECT sj.name,
        sh.run_date,
        sh.step_name,
    CAST(CONVERT(VARCHAR(10),RUN_DATE)AS DATE) AS 'FORMATEDDATE',
        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
  
inner JOIN msdb.dbo.sysjobhistory sh ON sj.job_id = sh.job_id
)
, JOBHISTORYSECONDS AS (

SELECT *, SUBSTRING(CONVERT (VARCHAR,FORMATEDDATE),6,2) AS MONTHNUMBER	, substring(cast (FORMATEDDATE as varchar),1,8) +'01 00:00:00'	as FOD	
    ,( SUBSTRING(RUN_TIME,1,2) *3600)+ (SUBSTRING(RUN_TIME,4,2)* 60) +SUBSTRING(RUN_TIME,7,2) AS 'RUN_START_TIME(SEC)'
    , (SUBSTRING([run_duration (DD:HH:MM:SS)  ],1,2)*3600*24)+ (SUBSTRING([run_duration (DD:HH:MM:SS)  ],4,2)*3600)+(SUBSTRING([run_duration (DD:HH:MM:SS)  ],7,2)*60)+SUBSTRING([run_duration (DD:HH:MM:SS)  ],10,2) AS [run_duration_SEC ]
FROM JOBHISTORY  
)
, MONTHLYJOB_STEPAVGTIME AS (

SELECT 
  name,step_name,FOD,MONTHNUMBER,AVG([RUN_START_TIME(SEC)])  AS M_AVG_START_SEC,AVG([run_duration_SEC ]) AS M_AVG_DURATION
FROM JOBHISTORYSECONDS JS
GROUP BY 
JS.name,step_name,FOD,JS.MONTHNUMBER
)

SELECT 'MONTHLY STEP AVERAGE' AS AVG_TYPE,*, DATEADD(S,M_AVG_START_SEC, FOD ) AS M_AVG_START_TIME, DATEADD(S,M_AVG_DURATION, DATEADD(S,M_AVG_START_SEC, FOD )) AS M_AVG_END_TIME
      into #MONTHLYJOB_STEPAVGTIME
FROM 
  MONTHLYJOB_STEPAVGTIME




SELECT name,step_name,TimeType, [1] AS Jan, [2] as Feb,[3] as Mar,[4] as Apr,[5] as May,[6] as Jun,[7]as Jul,[8] as Aug,[9] As Sept,[10] as Oct,[11] as NOv,[12] as [Dec] 
FROM 
(SELECT name ,step_name,'StartTime' as TimeType,  CAST(MONTHNUMBER AS INT) AS MONTHNUMBER , replace(M_AVG_START_TIME,'1900-01-01','')as M_AVG_START_TIME
   FROM #MONTHLYJOB_STEPAVGTIME) AS SOURCEPIVOT
PIVOT
(
  MAX(M_AVG_START_TIME)
  FOR MONTHNUMBER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )
) AS PIVOTTABLE
union all 
--Endtime Pivot 
sELECT name,step_name,TimeType, [1] AS Jan, [2] as Feb,[3] as Mar,[4] as Apr,[5] as May,[6] as Jun,[7]as Jul,[8] as Aug,[9] As Sept,[10] as Oct,[11] as Nov,[12] as [Dec] 
FROM 
(SELECT name ,step_name,'EndTime' as TimeType,  CAST(MONTHNUMBER AS INT) AS MONTHNUMBER ,  M_AVG_END_TIME FROM #MONTHLYJOB_STEPAVGTIME) AS SOURCEPIVOT
PIVOT
(
  MAX(M_AVG_END_TIME)
  FOR MONTHNUMBER IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )--, 2,3,4,5,6,7,8,9,10,11,12)
) AS PIVOTTABLE
order by name,step_name,TimeType desc 
end 
end

Results MonthlyaverageI typically schedule this to run on the first day of the month to help me better understand how the job processing times are trending month over month. I hope you find this code as helpful as I have.


2 thoughts on “SQL Sever Agent Monthly Job Run Time Averages”

Leave a Reply

Your email address will not be published. Required fields are marked *