I am trying to figure out how to accurately adjust my configuration settings in the probe to address the following alert:
Profile XXXX instnce XXXX, failed to execute in scheduled time interval, delayed by 44 seconds
In reading the help, I am left with more questions than answers.
Given my profiles settings:
Heartbeat 60 sec
check interval 5 min
SQL Timeout 60 secs
Profile Timeout 30 min
Delay Threshold 30 sec
and the profile status (taken from probe)
checkpoint # Execution time
1 85 secs
2 0 secs
3 4 secs
4 4 secs
5 35 secs
6 35 secs
7 54 secs
8 85 secs
9 4 secs
How or where is the 44 secs in the alert getting calculated?
This article states the following (mostly taken from the help file and I don't believe to be completly accurate)
Explanation of sqlserver probe timeout fields to help get rid of timeout failures/alarms:
1.Heartbeat - Defines the interval at which all profile checkpoints schedules will be tested and trigger eventual checkpoint execution. This number should be common denominator to all used check interval values. The higher the value the lower is the profile overhead.
2.Check Interval - Default value for check interval in the profile. Will be used if nothing else is defined in the checkpoint and overwrites the default checkpoint list setting.
3.Profile Timeout - Defines the maximum processing time for all checkpoints in the profile. If this timeout is reached, the interval processing is finished and the probe waits for next heartbeat to evaluate any checkpoint schedules. Alarm message is issued.
4. SQL Timeout - Every checkpoint query runs asynchronously. In case the query reaches the SQL timeout, the checkpoint processing will be terminated and the next checkpoint will be started. An alarm is issued.
5. Delay Threshold - Timeout threshold for the profile delay alarm.
If you are getting alarms for ‘Profile <Database Server>, failed to execute in scheduled time interval, delayed by <seconds> seconds’, it means that your profile is taking more time to get executed then the described heartbeat interval. For example, if the profile has configured as follows:
How can my delay be 44 seconds given my current query times?
Is Profile Timeout a cumulative value? In my case if the total time of all my checkpoints exceeds 5 mins - alert?
Is Check Value how often my profile will be run? In my case every 5 mins?
What is Heartbeat exactly - I have read this several times and still don't understand "Defines the interval at which all profile checkpoints schedules will be tested and trigger eventual checkpoint execution" In my example what is happening every 60 secs.
Any help or insight is greatly appreciated.