Do Any SQL Server Jobs on the Same Server Overlap?
This is a bigger problem than most DBAs realize, especially when a SQL Server has many, many jobs. Just as with any activity on SQL Server, it is ideal if jobs can be spread over time as much as possible, instead of doing all of them at once. For example, if your SQL Server has 10 databases, and you create backup jobs for each of them, it is much better to schedule them to run one at a time, instead of all at the same time.
While you can view how long a job runs from Enterprise Manager, there is no easy way to schedule manual jobs, one after another (giving each job enough time to complete), so that they don’t overlap. It can be done, but for servers with lots of jobs, you may need a spreadsheet to figure this all out. As an option, you may want to consider using a third-party tool, such as SQL Sentry (www.sqlsentry.net), which allows you to view and manage all your jobs visually, ensuring that critical jobs don’t overlap.
So as you perform your job audit, check to see that jobs don’t overlap, assuming this is possible. If they do overlap, do your best to reschedule them to prevent the overlap, spreading the load over a great a lull time as possible.
Do You Have Any Non-SQL Server Jobs that Overlap?
Besides SQL Server jobs, you may have non-SQL Server jobs on your SQL Server. Some examples of these include defragmentation or tape backup jobs that don’t use the SQL Server scheduler. Since these don’t use the SQL Server scheduler, they are easy to forget about, and you may end up running some of these jobs at the same time as your SQL Server jobs. As with SQL Server jobs, it is ideal if you can schedule these jobs to run at times other than when your SQL Server jobs run. If need be, include these in the spreadsheet discussed above.
Have Jobs that Run T-SQL Been Optimized?
Just as with code found in applications or scripts, T-SQL that is run as part of a job needs to be optimized. The T-SQL code should follow all the performance tips found on this website, and also any relevant indexes should be added to help the job code run as efficiently as possible.
So for every job that has T-SQL code in it, you should run it through Query Analyzer to views its Execution Plan, looking for potential problems, and also through the Index Wizard, looking for potential useful indexes to boost performance.
Have You Checked to See How Long Jobs Run?
I have already mentioned that you can use Enterprise Manager to view how long any particular job has run. But what I didn’t mention is that it is a good idea to check this over time to see if there are a lot of variations in how long a particular job runs. For example, a particular job may normally take 2 minutes to run, but you discover that once a week, on Sundays, that this same job takes over 15 minutes to run. Significant changes in the amount of time that it takes to run a job is a good indication that there is some conflict with this job and another process running on SQL Server. If you find anything like this, you will want to research it in more detail to identify what is going on, and fix it.