Today’s post is going to be short and sweet.  About a year ago I put together a procedure that allows me to see the run times of all of the SQL Agent jobs on a database server.  Some might argue that I got a little carried away when I added the standard deviation and variance statistics, but I can counter that they do have meaning.

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.

Happy SQL-ing!