Monday, August 5, 2013

Why is my SQL Agent Job Slowing Down?

The other day, we had a situation where a user had the perception that a particular job was taking longer than normal.

So I opened the Job History and glanced over the run times. Everything seemed fine to me. But I realized, I wanted something more precise than 'it looks good when I look at it'.

msdb to the Rescue

So I started digging into the job history tables in msdb. msdb is one of the system databases. Specifically, it keeps track of all the jobs, their run times, history, errors, outcomes, etc.

In fairly short order, I pulled together a couple of scripts to analyze the job history, and tell me exactly how run times have been changing, or not, over the past month or so.

The Persistence of Time

As it turns out, the run times were stable, the user's perception of time was not.

Overview of all jobs:

Job Run Times - All Jobs
Details on one job:

Job Run Times - One Job
A Couple of Quick Scripts

The first script gives an overview of all jobs, aggregated by month.

[sql]

-- Steven Ormrod
-- job run times - all jobs.sql

use msdb;
go

-- aggregations, omits failures as they skew the data
select j.name, left(h.run_date, 6) as 'year/month',
avg(h.run_duration) as 'avg duration',
max(h.run_duration) as 'max',
min(h.run_duration) as 'min',
cast(stdev(h.run_duration) as numeric(20, 2)) as 'stdev'
from dbo.sysjobhistory h
join dbo.sysjobs j
on h.job_id = j.job_id
where h.step_id = 0
and h.run_status = 1 -- omits failures
group by j.name,
left(h.run_date, 6)
order by j.name,
left(h.run_date, 6) desc;
go

[/sql]

The second script gives you the details for a specific job.

[sql]

-- Steven Ormrod
-- job run times - one job.sql

use msdb;
go

declare @strJobName varchar(100);
set @strJobName = 'YOURJOBNAMEGOESHERE';



-- details
select j.name, h.run_date, h.run_time, h.run_duration,
--select 'Slow Running Job' as 'name', h.run_date, h.run_time, h.run_duration,
h.run_status, h.message
from dbo.sysjobhistory h
join dbo.sysjobs j
on h.job_id = j.job_id
where j.name = @strJobName
and h.step_id = 0
and h.run_status = 1 -- just the successful runs
order by h.run_date desc,
h.run_time,
h.step_id;

go

[/sql]

Details are Important

The next time one of your users reports that a job is taking longer than usual, gather some basic stats so you can establish the facts before going too far.

No comments:

Post a Comment