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