on June 20, 2009
Here’s a query I found useful today– this week we moved many of our production datamart servers to SQL 2K5 SP3 CU4, and today among the course of other issues I wanted to take a look at my job runtimes to see if they might be noticeably slower or faster than prior runs. I often am in a similar situation after deploying significant changes to our codebase.
Why average runtime?
Since most of my processing runs in SQL Server Agent jobs, looking at average runtime per day is a pretty convenient index of performance. However, the load in processing varies by day of week, so it’s frequently useful to check activity for only a certain day of the week.
This script allows for both. I usually want to tweak the conditions, so I don’t set them in variables at the top, I edit them within the query itself each time:
use msdb;
GO
select
d.jobname
,d.servername
, avgDurationMinutes=avg(d.durationMinutes)
, daydate=convert(char(10),startdatetime,101)
from (
select
jobname=j.name
,servername=server
,startdatetime=
CONVERT (DATETIME, RTRIM(run_date))
+ (
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
) / 216e4
, durationMinutes=
(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
)/60.
,enddatetime =
dateadd
(ss,
(CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),1,3) AS INT) * 60 * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),4,2) AS INT) * 60
+ CAST(SUBSTRING((right('0000000' + convert(varchar(7), run_duration), 7)),6,2) AS INT)
)
,
(CONVERT (DATETIME, RTRIM(run_date))
+ (
run_time * 9
+ run_time % 10000 * 6
+ run_time % 100 * 10
) / 216e4 )
)
, retries_attempted
from sysjobs j (nolock)
join sysjobhistory h on
h.job_id = j.job_id
and h.step_id = 0 -- look only at the job outcome step for the total job runtime
where
j.name in ('<strong>JobName</strong>') -- Set the jobname here
) d
where
datepart(dw,startdatetime)=7 -- Set your day of week here if desired. 7=Saturday
group by
d.jobname
,servername
,convert(char(10),startdatetime,101)
order by
d.jobname
,servername
,cast(convert(char(10),startdatetime,101)as datetime) desc;
GO