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.
One Response
I’ve found that choosing off-hour/halfhour times works well, as infrastructure and others are also using the midnight, 12:30, etc. times. For instance, I’ll schedule hourly jobs to run at :08 after the hour, that sort of thing. Depending on how long the job takes, it frequently means there is less overlap.