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.
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.
Are There Alternative to Your Current Jobs?
Just because you have a job running doesn’t mean its the best way to accomplish the task at hand. Evaluate each job, and determine if there is a better way to accomplish the same thing. For example, perhaps writing T-SQL code to perform a nightly import might be more efficient that using your current DTS package. Or perhaps a job you are running, that shells out of SQL Server to run, could better be done from another scheduling program instead of SQL Server. I can’t make any specific recommendations here, as there is so much variability involved. But the key thing to remember is that your current jobs are often not the only solution, and that there may be better solutions available–that reduce server overhead–if you take the time to think about them.
Now What?
Your goal should be to perform this part of the performance audit, described on this page, for each of the jobs in each of databases in each of your SQL Servers, and then use this information to make changes as appropriate.
Once you have completed this part of the performance audit, you are now ready to take a look at how to use Profiler to identify poor performing queries.
*Originally published at SQL-Server-Performance.com
Brad M. McGehee is a full-time DBA with a large manufacturing company, and the publisher of http://www.SQL-Server-Performance.Com, a website specializing in SQL Server performance tuning and clustering.
He is an MVP, MCSE+I, MCSD, and MCT (former).
Brad also runs another website called http://www.WorldClassGear.com It provides independent gear reviews for backpackers, trekkers, and adventure travelers.
