{"id":1094,"date":"2017-03-12T17:23:59","date_gmt":"2017-03-12T11:53:59","guid":{"rendered":"http:\/\/pramodsingla.com\/?p=1094"},"modified":"2017-03-12T17:23:59","modified_gmt":"2017-03-12T11:53:59","slug":"sql-server-agent-get-avg-jobs-run-time","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=1094","title":{"rendered":"SQL Server Agent- Get Avg Jobs Run Time"},"content":{"rendered":"

Problem Statement: <\/strong>Script to find average SQL Server Agent jobs run time.<\/h2>\n

Solution:<\/strong><\/h2>\n
\n
SET<\/span>\u00a0TRANSACTION<\/span>\u00a0isolation<\/span>\u00a0level<\/span>\u00a0READ<\/span>\u00a0uncommitted<\/span>;<\/span> \n\nWITH<\/span>\u00a0cte_AR<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0j<\/span>.<\/span>NAME<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0'JobName'<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast<\/i><\/span>(<\/span>(<\/span>\u00a0(<\/span>\u00a0run_duration<\/span>\u00a0\/<\/span>\u00a010000<\/span>\u00a0*<\/span>\u00a03600<\/span>\u00a0+<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>\u00a0run_duration<\/span>\u00a0\/<\/span>\u00a0100<\/span>\u00a0)<\/span>%<\/span>100<\/span>\u00a0*<\/span>\u00a060<\/span>\u00a0+<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0run_duration<\/span>%<\/span>100<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+<\/span>\u00a031<\/span>\u00a0)<\/span>\u00a0\/<\/span>\u00a060.0<\/span>\u00a0)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>\u00a0AS<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'RunDurationMin'<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0msdb<\/span>.<\/span>dbo<\/span>.<\/span>sysjobs<\/span>\u00a0j<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0msdb<\/span>.<\/span>dbo<\/span>.<\/span>sysjobhistory<\/span>\u00a0h<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0j<\/span>.<\/span>job_id<\/span>\u00a0=<\/span>\u00a0h<\/span>.<\/span>job_id<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--j.enabled\u00a0=\u00a01\u00a0\u00a0and\u00a0--Only\u00a0Enabled\u00a0Jobs\u00a0<\/i><\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0run_status<\/span>\u00a0=<\/span>\u00a01<\/span>\u00a0--Successfull<\/i><\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0step_id<\/span>\u00a0=<\/span>\u00a00<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0run_date<\/span>\u00a0>=<\/span>\u00a0'20161210'<\/span>)<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0cte_std<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0Jobname<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0isnull<\/b><\/span>(<\/span>Cast<\/i><\/span>(<\/span>Stdev<\/i><\/span>(<\/span>RunDurationMin<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>,<\/span>\u00a00<\/span>)<\/span>\u00a0AS<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0StddevMin<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0cte_Ar<\/span>\u00a0--where\u00a0ntiled\u00a0between\u00a02\u00a0and\u00a09<\/i><\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0Jobname<\/span>)<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0cte_avg<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0Jobname<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0isnull<\/b><\/span>(<\/span>Cast<\/i><\/span>(<\/span>Avg<\/i><\/span>(<\/span>RunDurationMin<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>,<\/span>\u00a00<\/span>)<\/span>\u00a0AS<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0StdAvgMin<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0cte_Ar<\/span>\u00a0--where\u00a0ntiled\u00a0between\u00a02\u00a0and\u00a09<\/i><\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0Jobname<\/span>)<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0cte_Final<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0AS<\/span>\u00a0(<\/span>SELECT<\/span>\u00a0cte_AR<\/span>.<\/span>Jobname<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast<\/i><\/span>(<\/span>Avg<\/i><\/span>(<\/span>RunDurationMin<\/span>)<\/span>\u00a0AS<\/span>\u00a0DECIMAL<\/i><\/span>(<\/span>18<\/span>,<\/span>\u00a02<\/span>)<\/span>)<\/span>\u00a0AS<\/span>\u00a0FinalAvginMin<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0--,count(1)\u00a0cnt<\/i><\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FROM<\/span>\u00a0\u00a0\u00a0cte_AR<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN<\/span>\u00a0cte_std<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0cte_AR<\/span>.<\/span>jobname<\/span>\u00a0=<\/span>\u00a0cte_std<\/span>.<\/span>jobname<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0JOIN<\/span>\u00a0cte_avg<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0cte_AR<\/span>.<\/span>jobname<\/span>\u00a0=<\/span>\u00a0cte_avg<\/span>.<\/span>jobname<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0WHERE<\/span>\u00a0\u00a0RunDurationMin<\/span>\u00a0BETWEEN<\/span>\u00a0StdAvgMin<\/span>\u00a0-<\/span>\u00a0StddevMin<\/span>\u00a0AND<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0StdAvgMin<\/span>\u00a0+<\/span>\u00a0StddevMin<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0GROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0cte_AR<\/span>.<\/span>Jobname<\/span>)<\/span> \n--select\u00a0*from\u00a0cte_final<\/i><\/span> \nSELECT<\/span>\u00a0@@servername ServerName<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0cte_ar<\/span>.J<\/span>obName<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FinalAvgInMin<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Count<\/i><\/span>(<\/span>1<\/span>) RunCount<\/span> \nFROM<\/span>\u00a0\u00a0\u00a0cte_Final<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0RIGHT<\/span>\u00a0JOIN<\/span>\u00a0cte_AR<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0cte_AR<\/span>.<\/span>jobname<\/span>\u00a0=<\/span>\u00a0cte_Final<\/span>.<\/span>JobName<\/span> \n--WHERE<\/span>\u00a0\u00a0cte_ar<\/span>.<\/span>jobname<\/span>\u00a0IN ('abc')<\/span>\nGROUP<\/span>\u00a0\u00a0BY<\/span>\u00a0cte_ar<\/span>.<\/span>jobname<\/span>,<\/span> \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0FinalAvginMin<\/span> \nORDER<\/span>\u00a0\u00a0BY<\/span>\u00a0jobname<\/span>\u00a0 <\/span><\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"
Problem Statement: Script to find average SQL Server Agent jobs run time. Solution: SET\u00a0TRANSACTION\u00a0isolation\u00a0level\u00a0READ\u00a0uncommitted; WITH\u00a0cte_AR \u00a0\u00a0\u00a0\u00a0\u00a0AS\u00a0(SELECT\u00a0j.NAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AS\u00a0‘JobName’, \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Cast((\u00a0(\u00a0run_duration\u00a0\/\u00a010000\u00a0*\u00a03600\u00a0+ \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(\u00a0run_duration\u00a0\/\u00a0100\u00a0)%100\u00a0*\u00a060\u00a0+ \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0run_duration%100 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0+\u00a031\u00a0)\u00a0\/\u00a060.0\u00a0)\u00a0AS\u00a0DECIMAL(18,\u00a02))\u00a0AS[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[27],"tags":[75,105,164],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1094"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1094"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/1094\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}