Data World

SQL Server Agent- Get Avg Jobs Run Time

Posted by Pramod Singla on March 12, 2017


Problem Statement: Script to find average SQL Server Agent jobs run time.

Solution:

SET TRANSACTION isolation level READ uncommitted; 

WITH cte_AR 
     AS (SELECT j.NAME                                             AS 'JobName', 
                Cast(( ( run_duration / 10000 * 3600 + 
                         ( run_duration / 100 )%100 * 60 + 
                                run_duration%100 
                                + 31 ) / 60.0 ) AS DECIMAL(18, 2)) AS 
                'RunDurationMin' 
         FROM   msdb.dbo.sysjobs j 
                INNER JOIN msdb.dbo.sysjobhistory h 
                        ON j.job_id = h.job_id 
         WHERE 
          --j.enabled = 1  and --Only Enabled Jobs  
          run_status = 1 --Successfull 
          AND step_id = 0 
          AND run_date >= '20161210'), 
     cte_std 
     AS (SELECT Jobname, 
                isnull(Cast(Stdev(RunDurationMin) AS DECIMAL(18, 2)), 0) AS 
                StddevMin 
         FROM   cte_Ar --where ntiled between 2 and 9 
         GROUP  BY Jobname), 
     cte_avg 
     AS (SELECT Jobname, 
                isnull(Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)), 0) AS 
                StdAvgMin 
         FROM   cte_Ar --where ntiled between 2 and 9 
         GROUP  BY Jobname), 
     cte_Final 
     AS (SELECT cte_AR.Jobname, 
                Cast(Avg(RunDurationMin) AS DECIMAL(18, 2)) AS FinalAvginMin 
         --,count(1) cnt 
         FROM   cte_AR 
                JOIN cte_std 
                  ON cte_AR.jobname = cte_std.jobname 
                JOIN cte_avg 
                  ON cte_AR.jobname = cte_avg.jobname 
         WHERE  RunDurationMin BETWEEN StdAvgMin - StddevMin AND 
                                       StdAvgMin + StddevMin 
         GROUP  BY cte_AR.Jobname) 
--select *from cte_final 
SELECT @@servername ServerName, 
       cte_ar.JobName, 
       FinalAvgInMin, 
       Count(1) RunCount 
FROM   cte_Final 
       RIGHT JOIN cte_AR 
               ON cte_AR.jobname = cte_Final.JobName 
--WHERE  cte_ar.jobname IN ('abc')
GROUP  BY cte_ar.jobname, 
          FinalAvginMin 
ORDER  BY jobname  
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: