Compare SQL Job Runtime for a certain day of the week (A Migration Performance Check Item)
Use [msdb];
GO
Declare @jobname varchar(250)
Declare @DayOfWeek int = 7 --(7=Saturday)
Declare @SQLJobNameLike varchar(1000) = 'Maintenance'
Create Table #TMP (SQLJobName varchar(1000), Checked Bit)
Insert Into #TMP
Select [name], 0 from
[msdb]..sysjobs
where
[name] like '%' + @SQLJobNameLike + '%'
and
[enabled] = 1
Order by [name] asc
Create Table #Results (SQLJobName varchar(1000), ServerName varchar(100), AvgDurationMinutes varchar(100), DayDate varchar(100))
While Exists (Select 1 from #TMP where Checked = 0)
Begin
Set @jobname = (Select top 1 SQLJobName from #TMP where Checked = 0 Order By SQLJobName asc)
Print @jobname
Insert Into #Results
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 = @jobname
and
run_duration > 0
) d
where
datepart(dw,startdatetime) = @DayOfWeek
group by
d.jobname
,servername
,convert(char(10),startdatetime,101)
order by
d.jobname
,servername
,cast(convert(char(10),startdatetime,101)as datetime) desc
Update #TMP set checked = 1 where SQLJobName = @jobname
End;
Select SQLJobName, ServerName, AvgDurationMinutes, DayDate as [Date], DateName(WEEKDAY, DayDate) As [DayOfWeek]
from #Results Order By SQLJobName Asc, DayDate Desc
Drop Table #TMP
Drop Table #Results
GO
|
|
|
|
|
|