[SQL Server] Afficher la moyenne d'exécution des jobs SQLServer
-- =============================================
-- Author: Zouhaier KHARROUBI
-- Create date: 20/03/2020
-- Description: Afficher la moyenne d'exécution des jobs SQLServer
-- =============================================
SELECT name AS NomJob,AVG(Seconde) MoyenneExecSeconde FROM (
SELECT name,step_name,run_date,run_duration_job
,SUBSTRING(run_duration_job,1,2) Heure
,SUBSTRING(run_duration_job,3,2) [Minute]
,SUBSTRING(run_duration_job,5,2) Secondes
,(SUBSTRING(run_duration_job,1,2)* 3600)+(SUBSTRING(run_duration_job,3,2)* 60)+(SUBSTRING(run_duration_job,5,2)) Seconde
FROM (
SELECT T1.name,T2.step_name,run_date,run_duration,REPLICATE('0',6-LEN(CAST(T3.run_duration AS VARCHAR(8))))+CAST(T3.run_duration AS VARCHAR(8))AS run_duration_job
FROM msdb.dbo.sysjobs T1
JOIN msdb.dbo.[sysjobsteps] T2 ON T2.job_id=T1.job_id
JOIN msdb.dbo.sysjobhistory T3 ON T3.job_id=T1.job_id AND T3.step_id=0
WHERE name LIKE'%MSBI SSIS%'
)R1
) R2 GROUPBY name ORDER BY name