Jobs Running Time

Knowing the amount of time a job takes is relevant.  But knowing how much deviation from the “norm” is also useful.  Even more useful, would be to plot the job run times over time.  In this context it might be interesting to spot jobs that are taking longer to run as the data sets grow.
Although I did not attempt to handle the time based plot, getting the Agent runtimes has value.  You could take it one step further and place the call to the stored procedure in an excel file and send it to your manager.  All she would have to do is hit Data==>Refresh.
Just a little background.  For starters, the SQLAgent information is stored in the msdb database.  We’ll capture the job name out of the sysjobs table and the job run times out of the sysjobshistory table.
Regardless, here’s what the code looks like:

USE [msdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_jobs_summary_report]
AS

SELECT    j.[name],
        COUNT(jh.run_duration) AS 'Sample Size',
        CONVERT(DECIMAL(4, 2),
            MIN((    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 1, 2)) * 3600) +
                    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 3, 2)) * 60) +
                     CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 5, 2)))/3660.0))
                        AS 'MIN Runtime (hours)',
        CONVERT(DECIMAL(4, 2),
            MAX((    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 1, 2)) * 3600) +
                    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 3, 2)) * 60) +
                     CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 5, 2)))/3660.0))
                        AS 'MAX Runtime (hours)',
        CONVERT(DECIMAL(4, 2),
            AVG((    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 1, 2)) * 3600) +
                    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 3, 2)) * 60) +
                     CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 5, 2)))/3660.0))
                        AS 'AVG Runtime (hours)',
        CONVERT(DECIMAL(4, 2),
            STDEV((    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 1, 2)) * 3600) +
                    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 3, 2)) * 60) +
                     CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 5, 2)))/3660.0))
                        AS 'Std Dev.',
        CONVERT(DECIMAL(4, 2),
            VAR((    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 1, 2)) * 3600) +
                    (CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 3, 2)) * 60) +
                     CONVERT(int, SUBSTRING(STR(jh.run_duration, 6, 0), 5, 2)))/3660.0))
                        AS 'Variance'
FROM        sysjobs j
        INNER JOIN
            sysjobhistory jh
                ON    jh.job_id = j.job_id
WHERE    jh.run_status = 1    -- Succeeded
AND        jh.step_id = 0        -- Outcome
GROUP BY j.name
ORDER BY 5 DESC

No rocket science here.  Just plain old SQL.
And here’s the useless output from my notebook instance of SQL Server.  I’m happy to be getting the one row I got.  Try running this on a production server… then it’ll have real value.