SQL Agent Jobs

In my professional opinion, DBAs, in general, do not pay enough attention to the scheduling and monitoring of the execution of SQL Agent jobs.

SQL Agent jobs can perform a variety of activities:

1. Execute database backups.
2. Execute database maintenance and optimizations.
3. Execute data maintenance and optimizations.
4. Execute reports.
5. Schedule execution of scripts to be executed by the Windows Server.
6. Execute just about anything that needs to be done on a server.
   

There are some important considerations a DBA must carefully think through when creating and scheduling SQL Agent jobs:

1. Does a job utilize a high percentage of available server resources? If so, when is the best time to schedule it to avoid conflicts with other server resource consumers?
2. Does a job utilize a high percentage of available direct attached disk storage or SAN resources? If so, when is the best time to schedule it to avoid conflicts with other I/O resource consumers?
3. Will the execution time interfere with any other important activity occurring on the server?

In general, it is important for long-time-to-execute SQL Agent jobs to not overlap with the execution of other long-time-to-execute SQL Server Agent jobs. Said another way: no high resource utilization jobs should be executing simultaneously. To accomplish this feat, a DBA must create and maintain a spreadsheet showing the execution schedule and maximum elapsed times for all SQL Agent jobs. But how to easily track SQL Agent job execution times? The tools in SQL Server Management Studio provide too much information for efficient tracking.

I have developed a suite of tools that condense the most important information about the execution times for SQL Agent jobs into a simple SQL query generated report whose output can be examined and digested in under 2 minutes, even for a server executing in excess of 60 jobs per day. The report shows execution times: minimum, maximum, most recent, and average execution times for each job. It has the added benefit of allowing me, as your DBA, to easily determine 3 things:

1. Whether the average job execution time is steadily increasing, and whether the job execution time for this, and jobs adjacent in the job schedule, need to have their schedule(s) adjusted.
2. That the job was executed on schedule and completed successfully.
3. Whether the maximum job execution time is outside what is consider normal for the job.

Point 2 warrants some dissertation. Proper job creation includes a mechanism that automates the notification of a DBA and/or other administrators of a failure. But best practices do not fully trust automation. It is important to verify successful SQL Agent job execution every day.

Point 3 warrants some dissertation. A SQL Agent job whose minimum execution time is 2 seconds, whose maximum execution time is 12 seconds, whose average execution time is 5 seconds, that suddenly requires 3 minutes to execute, would get my immediate attention. This is far outside normal execution times for this job and the cause of this anomaly needs to be investigated. Why? Because a sudden increase in SQL Agent job execution times is often the first indication of a developing problem. 

Intercepting small problems before they become big problems is the heart of best practices. The tools I’ve developed as a DBA allow me to intercept developing problems.