Saturday, May 30, 2009

Analyzing SQL Agent Job and Job Step History in SQL Server

Problem
Almost every DBA configures SQL Agent Jobs for automation of repetitive tasks. This valuable facility works in the background in SQL Server and saves a lot of effort and manual rework. You may have a number of jobs scheduled and often there is the need to analyze the data to see which jobs are taking a long time or which job steps are taking a long time. As you add more jobs and overhead to the server these times become even more critical and analyzing the data is key. SSMS offers some insight into your jobs, but there is no easy way to slice and dice the data to see what is going on for a particular job or at a particular time of the day.

Some of the questions you may want to ask include:

  • How much difference in performance is there for the current scheduled time compared to changing the time to midnight?
  • Exactly how much time is consumed now to complete the job?
  • Have jobs performed better after changes or there is some negative impact?
  • Which step of a specified job is taking longer than it takes normally to complete?

Questions like these require analysis performed over the history of the jobs. So how do you generate the required set of data in a form that is suitable for analysis? Or a situation where someone does not have access to SSMS or EM to access the job history for verification and analysis?

See full detail: http://www.mssqltips.com/tip.asp?tip=1752

No comments: