Configure Job Duration logic

brenttempleton

we have a good number of jobs that run to see if there is a file present, if so it takes awhile to run, otherwise it takes generally under a second.  Is there a way to configure the Job Duration Unusual alert or the formula to only include job executions over seconds or something like that?  That would be a much more accurate averages, at least in our cases.

Dan_J

Hi @brenttempleton

Thanks for reaching out to us regarding this.

Unless I am misunderstanding your question, then yes it is possible to configure this by increasing the 'Don't raise an alert i the job run time is less then .... seconds' setting on the alert configuration page.

This may also help: https://www.red-gate.com/products/dba/sql-monitor/resources/articles/sql-server-agent-job-activity-monitor

brenttempleton

That would not do what I am seeking, that would keep the alert from firing if it takes longer than xxx seconds (120 in screenshot).  What I want is do is to eliminate the times from the computed average that are less than 2 second (for example) then the average duration would be an average of runs when it had work to do.  So if the last 20 run were as follows (in seconds)
0,0,0,.5,10,5,.5,0,1,18,20,1,0,0,0,12,1,5,1,0
avg of last 10 =3.4
avg tossing out < 2 = 10.8
10.8 is a more realistic time the job spent, so raise alerts when job takes 15 sec or more, right now getting alerts for “normal” run durations, as even 2x avg is still way under normal.

I briefly tried to see if I could configure custom metric, but didn’t get very far.

thanks

Dan_J

Hi @brenttempleton

Thanks for clarifying on this.

I've taken a look into this and unfortunately it appears that it isn't possible to do this using absolute values. However saying that, you mention wanting to base this on the average time the job takes to run, and so I think the 'best fit' I can see for achieving this is to utilise the baseline duration deviation setting (the baseline being the median of the last 10 runs):

The only difficulty/limitation here is that we use the median average rather than the 'arithmetic' average, which I think you are wanting to base this on. 

Also to advise again of this document that further details this: https://www.red-gate.com/products/dba/sql-monitor/resources/articles/sql-server-agent-job-activity-monitor

brenttempleton

Dan --  correct, what I want to do is to throw out low durations when calculating the baseline duration, so values less than 1 or 2 seconds in the case that I am working, when this task has "work" to do.  I am new to SQL Monitor, wondering if I can setup this job that only when this job runs longer than say 5 minutes, to alert us, but don't want to change for all agent jobs -- I know I can ignore this job completely, which is not also the desired approach.
thanks again
Brent

Dan_J

Hi @brenttempleton

My sincere apology for the delayed update on this, I have been discussing this with my colleagues here.

After looking at this for some time I unfortunately don't think we're going to achieve exactly what you are after here.

"...what I want to do is to throw out low durations when calculating the baseline duration" - unfortunately this isn't possible because the baseline is calculated according to the average length of the last 10 runs, meaning the baseline can change. It isn't possible to change/control this.

Looking again at the Alert settings we discussed earlier in this thread, while they won't do specifically what you are after, they may get you close. As previously mentioned, this is all dependent on what the calculated baseline is, and I think because the baseline in your case is low it is causing the issue in the following example:

The setting for not raising when the job runs less than x seconds, being set to (for example) 240 seconds would cause only jobs that ran longer than 4 minutes to be able to raise the alert, and then only if their duration was deviated by more than 50%. 

So if the baseline was calculated to be 5 minutes and you had a job run for 15 minutes, that job would in theory raise an alert, but others running  1 minute would not since they are under 4 minutes duration.  However if the baseline is 1 or 2 minutes, then EVERY job that ran for 5 minutes would be alerted on rather than only alerting on an unusual duration (e.g. 15 minutes) for those which run about 5 minutes normally.