List your scheduled tasks next run time and average duration MSSQL

Here’s a handy little query I came up with to list your scheduled jobs, what time they’re going to run next and their average duration on your MSSQL box. I’ve been chasing performance on my sever for months, tweaking things here and there, I found that windows was having massive page fault spikes every 5 minutes. Further investigation indicated that multiple (up to 10) scheduled tasks within SQL Server were fireing at the same time.

I used the query below to randomise the schedules (adding a random number of seconds to the start time) spreading the load more evenly over time.

A small gain but a nice one to finally nut out.

SELECT
    j.name, 

    endTime = CONVERT
    (
        DATETIME,
        RTRIM(next_run_date)
    )
    +
    (
        next_run_time * 9
        + next_run_time % 10000 * 6
        + next_run_time % 100 * 10
    ) / 216e4,

   (
        select avg(run_duration)
        from sysjobhistory jh
        where j.job_id = jh.job_id and run_duration is not null
   )
FROM
    msdb..sysjobschedules j
WHERE
	next_run_date <> 0
	and (name <> 'Schedule_1' and name <> 'Schedule 1')

order by
CONVERT
    (
        DATETIME,
        RTRIM(next_run_date)
    )
    +
    (
        next_run_time * 9
        + next_run_time % 10000 * 6
        + next_run_time % 100 * 10
    ) / 216e4