Are jobs scheduled to run during production lulls?
Do any SQL Server jobs on the same server overlap?
Do you have any non-SQL Server jobs that overlap?
Have jobs that run T-SQL been optimized?
Have you checked to see how long jobs run?
Are there alternative to your current jobs?
Enter your results in the table above.
SQL Server Jobs Can Negatively Affect Performance, If You Are Not Careful
Virtually every SQL Server runs one or more daily jobs. And most likely, runs many weekly jobs. Unfortunately, most DBAs set up jobs, and then forget about them, unless of course they break. But if they run day after day without any problems, most jobs are forgotten about.
Just as any application can negatively affect SQL Server’s performance, the same is true about jobs. Jobs that run poorly-designed code, or run at bad times, can put a significant strain on SQL Server. Because of this, it is important to include your SQL Servers’ jobs as part of your performance audit.
In this section of the SQL Server Performance Audit, we focus on how to identify, and correct, potential job-related performance issues.
Are You Running Any Unnecessary Jobs?
Because jobs are often forgotten about, it is very easy to set up a job to accomplish a specific task, and then forget to remove the job when the task is no longer necessary. For example, you may need to create a job that moves data from several tables into another table, nightly, that can be used to produce reports. But if that report is no longer being used, there is no longer any need to run the job, and it should be removed to reduce overhead. The problem is that there is no direct link between the job and the report, so if the report is no longer used, it is easy to forget to remove the job.
As part of your audit, review each of the jobs that are running on each of your servers, and determine if the job is really necessary. If not, then get rid of it.
Along this same line of thinking, look for duplicate jobs. For example, I have seen novice DBAs use the Maintenance Wizard to set up jobs within SQL Server, and not realize exactly what they have done. Then they will add some manual jobs that duplicate one or more of the jobs that were created with the Maintenance Wizard. Doing the same thing twice can contribute to a lot of wasted SQL Server resources.
Are Jobs Scheduled to Run During Production Lulls?
As you review each of the jobs on your SQL Servers, take a close look at when they run. Assuming that a job doesn’t have to run at a specific time, do your best to schedule jobs so that they run when the SQL Server is less busy, such as at nights or on weekends, depending on your situation.
If you aren’t sure when your SQL Server has lulls, do a Performance Monitor log over a week’s period. This should provide you with enough data to be able to identify lull periods where you can run non-time-sensitive jobs.