Vous êtes ici :
L’historique de l’agent SQL Server, pour peu que l’on ne limite pas trop son historique, nous permet de produire des statistiques des durées d’exécution des jobs. Facile, à partir de la requête suivante, de faire des histogrammes, avec Excel ou PowerBI.
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
/*------------------------------------------------------------------- [SCRIPT] Job History Analysis [Description] Pour analyser l'évolution des durées d'exécution des jobs SQL Agent [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20191001 -------------------------------------------------------------------*/ USE msdb; GO --CREATE VIEW jobhistory_analysis as -- si vous voulez SELECT j.name AS job_name, h.step_id, run_datetime = CONVERT(DATETIME, RTRIM(run_date)) + (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4, run_status, run_duration = SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 2), run_duration_secs = CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 1, 2) AS INT) * 3600 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 3, 2) AS INT) * 60 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 2) AS INT), run_duration_min = CAST((CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 1, 2) AS DECIMAL(6, 2)) * 3600 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 3, 2) AS DECIMAL(6, 2)) * 60 + CAST(SUBSTRING(RIGHT('000000' + CONVERT(VARCHAR(6), run_duration), 6), 5, 2) AS DECIMAL(6, 2)) ) / 60 AS DECIMAL(10, 2)) FROM msdb..sysjobhistory h INNER JOIN msdb..sysjobs j ON h.job_id = j.job_id ORDER BY job_name, instance_id, h.step_id, run_datetime; |
Table of Contents