info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Compare SQL Agent Job Runtime for a certain day of the week
by BF (Principal Consultant; Architecture; Engineering)
2022-11-13







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