Job Duration Configuration

MikeTomkies

I have several SSIS packages scheduled which i would like to use SQL response to alert me for unusual job duration. The problem is the configuration for alerts does not allow me to.

My package is scheduled every 5mins it checks for new data on a server. If no new data exsist in terminates duration about 3 secs, If their is new data it will process the data 30secs to 1 min dpending on amount of data.

The settings alert is on a baseline value. How is this calculated? is it the average i.e my baseleine for the above job is 10 sec iam getting alerts for when it takes 3 secs or >30 secs both normal executions.
Ideally i want it to alert me if it takes more than 3 mins as that is a problem and could deteriorate into Jobs overlapping.

Matthew Flatt

Hi Mike,

The baseline is the average of the previous 10 job runs, unfortunately with a max of 99% this will still alert you for jobs which take over 20 seconds.

There is a current feature request to set a value higher than 99% which I have updated with your input.

Thank you for bringing this up.

MikeTomkies

I think the enhacment needed for a package like this would be for me to specify a target runtime rather than using the baseleine. The base line value is meaningless with these packages as it factors in a scenario where their is no data and the task will only take 3 seconds.

I would also like to ignore instances that are under the Baseline or Target firgure only getting alerts for runs that are over.

fatherjack2

Hi,

I have already posted on something along these lines but it may be worth adding my thoughts here too (that and I can t find the other post to cross refer!)

We have replication working here 24/7. Most of the replication jobs run hourly but our main data updating is done M-F 9-5. This means that replication jobs that run during the day take much longer that those over night. We get alerts, predictably, in waves as we go from one cycle to the other until the average of the previous 10 executions levels.

Unfortunately Mike's suggestion of a target 'time to complete' wont work for this situation unless we can link it in some way to a schedule
ie: overnight/w-end execution 1min +-20%
weekday execution 5min +-20%

Possibly a more flexible alternative would be to class the severity of the difference from the norm (and possibly the faster or slower indicator too) and also allow for a selection of how many executions to use for the average value calculation?

For me, ideally, I would like to set a schedule for the alert to be active - I dont really mind if the 02:00 replication ran or not (probably only 3 or 4 rows were updated) but if the alert was monitoring the 08:00 - 18:00 period ... much more relevant and important and so deserving of an alert.