Threshold evaluation order

Predictive thresholds

Predictive thresholds are checked before reactive thresholds, because they affect whether a database activity can start to run.

The sequence in which predictive thresholds are evaluated is as follows. If you do not define a particular threshold, its step is skipped. Also, the steps described might be combined at run time for performance reasons.

1. Check if a CONCURRENTWORKLOADACTIVITIES threshold exists and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken. If applicable, move to the next step.
2. Check if an ESTIMATEDSQLCOST threshold exists and if so, whether it has been violated. If you define this threshold in more than one domain, the threshold is resolved according to the scope resolution rules (see Domain precedence for activity thresholds for more information). The result of this operation is one value of ESTIMATEDSQLCOST applicable to the activity. If the threshold is violated, the corresponding action is taken.
3. Check if a CONCURRENTDBCOORDACTIVITIES threshold exists for the workload work action set threshold domain and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken.
4. Check if a CONCURRENTDBCOORDACTIVITIES threshold exists for the database work action set threshold domain and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken.
5. Check if a CONCURRENTDBCOORDACTIVITIES threshold exists for the service subclass threshold domain and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken.
6. Check if a CONCURRENTDBCOORDACTIVITIES threshold exists for the service superclass threshold domain and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken.
7. Check if a CONCURRENTDBCOORDACTIVITIES threshold exists for the database threshold domain and if so, whether it has been violated. If the threshold is violated, the corresponding action is taken.

For concurrency thresholds, thresholds for workload-level work action sets are checked first and database-level work action sets are checked second. Thresholds for work action sets are checked first, in order to avoid work action set thresholds on particular types of work blocking work of other types, which would affect concurrency. For example, by checking database-level work action set concurrency thresholds first, the following situation is avoided.

Also, assume that one LOAD activity is already running in the database (under any service superclass) and nine activities are already running in service superclass S1. A second new LOAD activity enters as the 10th activity. If the activity threshold scope resolution hierarchy were used during threshold evaluation, the incoming LOAD activity would not violate the service class threshold, increasing the concurrency to 10. The LOAD activity is then evaluated against the database-level work action threshold concurrency limit, which is violated because a LOAD activity is already running in the database and the work action threshold concurrency value is only 1. The second LOAD activity is then queued.

Any new activity arriving into service superclass S1 is now queued (because the service class concurrency limit is already reached). The work action threshold queue is affecting the service class, which is undesirable because activities trying to run in the service class do not necessarily have a relationship with the work action threshold condition (for example, an insert operation trying to run in service superclass S1 should not have to wait on a LOAD activity that is queued because of a database-level work action threshold condition). Therefore, to avoid this type of situation, the database-level work action concurrency threshold is checked before service subclass, service superclass, and database concurrency thresholds. Because the database-level work action set concurrency threshold is checked first, the 10th activity in the service class (which happens to be a LOAD activity) is blocked at the database work action threshold level before it can attempt to consume one spot in the service superclass S1.

Reactive thresholds