Script for SQL Agent "Weighted Job Duration"

Hi,

Does anyone know of a script that will give "weighted job duration"?
I want to use it, to identify which jobs are hogging the CPU. That is
for a given server, list the sql agent jobs ordered by:

(avg job duration in minutes) times (avg num of times job runs in a
given day).On 20 Jul 2004 08:54:48 -0700, Louis wrote:

> Hi,
> Does anyone know of a script that will give "weighted job duration"?
> I want to use it, to identify which jobs are hogging the CPU. That is
> for a given server, list the sql agent jobs ordered by:
> (avg job duration in minutes) times (avg num of times job runs in a
> given day).

Assuming SQL Server 2000 or higher, I came up with this:

Select T3.name, avg(T3.Recurrences) 'AvgRecurrencesPerDay',
sum(T3.[TotalDailyDuration])/sum(T3.Recurrences) 'AvgDuration',
avg(T3.Recurrences) * sum(T3.TotalDailyDuration)/sum(T3.Recurrences)
'Weight'
from (
select T2.name, T1.run_date,
count(*) 'Recurrences',
sum(T1.run_duration) 'TotalDailyDuration'
from msdb.dbo.sysjobhistory T1
inner join msdbo.dbo.sysjobs T2 on T1.job_id = T2.job_id
group by T2.name, T1.run_date
) T3
group by T3.name
order by Weight desc, T3.Name asc|||Thanks Ross. I played with your script and altered it a bit. I decided
what I really want is avg minutes per day.
- Louis

SELECT name as Job,
str(occurrences/@.numDays,10,1) as RunsPerDay,
str(duration/occurrences,10,1) as MinsPerJob,
str(duration/@.numDays,10,1) as MinsPerDay
FROM (
SELECT T2.name,
cast(count(*) as dec) as occurrences,
cast(sum(
(run_duration / 100)/100*60 + (run_duration / 100)%100 --
run_duration is in hhmmss crazy format
) as dec) as duration
FROM msdb.dbo.sysjobhistory T1
INNER JOIN msdb.dbo.sysjobs T2
ON T1.job_id = T2.job_id and step_id=0
and cast(rtrim(T1.run_date) as datetime) between @.startdate and
@.enddate
GROUP BY T2.name
) T
ORDER BY MinsPerDay desc

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!