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:
There are some important considerations a DBA must carefully think through when creating and scheduling SQL Agent jobs:
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:
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.