A key part of the SQL Server Agent is the ability to schedule jobs. While you can create one schedule for each agent job, frequently with applications like Reporting Services, users use Shared Schedules across multiple jobs. For instance, you can set a schedule to run at 8 am on week days or run every 2 hours or pick from a predefined list of schedules that already exist in the MSDB database. These are very convenient. However, if you choose to use these be sure you are keeping track of what is running for each of these shared schedules. You should not have everything running at once.
This is an example of a job schedule in a Management Studio SQL Server Agent Job. You can create a new one or pick from a list of already made schedules.
There’s nothing inherently wrong with using shared schedules—some very small operations can all run at the same time, however when you start to use them for larger operations you can really impact the overall performance of your server.
Many times, I have seen high CPU or locks as well as many other performance issues due to the system being overloaded with jobs running on shared schedules or all at the same time (midnight is a frequently common choice). Not every report to should run at 8 am and every data load run every 2 hours. If you not using shared schedules or added a separate schedule per job it is also important to make sure you are not running up against other things running. If you are using applications like SSRS, then you need to pay attention to when the report subscription refreshes are happening. Don’t overload your system by having everything run at once. Job and subscription schedules need to be analyzed and evaluated just like everything else you care for in your database.
To keep this from happening consult your agent jobs to see what other jobs are running before scheduling additional ones. You can easily get a glimpse in Job activity monitor to see what’s running and when it will run next. You can also really dive in using scripts provided in this really great post by Rick Dobson https://www.mssqltips.com/sqlservertip/5019/sql-server-agent-job-schedule-reporting/ .
Just a quick tidbit I thought I’d share as I am seeing this way too often in environments.
Funny you mention this now. Was just helping a client who had severe spiking on their new server every 10 seconds. They were running 44 “poll’ jobs and all would strike at the exact same time, get allocated to the 32 cpus and the CPU utilization would hit 100% for about 3 seconds and then settle back down. Oh, and they had two instances on the server doing this!
Spreading the jobs out over a 10 second period greatly helped smooth the cpu down and caused the jobs to run about 2x faster on average too.