SYMPTOM:
In the Strategy Intelligence Server 9.x and 10.x Job Monitor, a user notices that jobs remain in queue and do not execute while there are free database threads. Consider the following example:
There are 5 high, 10 medium and 10 low priority database threads set. The job monitor had 10 jobs executing with low priority and 13 waiting in queue with low priority. There were no medium or high priority jobs running. Why would the reports in queue not execute using one of the other priority threads?
CAUSE:
Strategy Intelligence Server engages in connection borrowing. Connection borrowing allows a job to run on a lower priority connection when no connections corresponding to the jobs' priority are available at execution. This feature is called 'borrow low'. However, 'borrow high' is not allowed. In other words, a low priority job can't run on a medium or high thread and a medium priority job can`t use a high priority thread. Such a mechanism can guarantee higher priority jobs a better chance of getting a database thread.
In the example mentioned above, all low priority threads are used. Although some medium and high threads are available, to use them is to implement 'borrow high', which is not allowed in Strategy Intelligence Server.
ACTION:
To avoid wasting database threads and expedite processing of low priority jobs, administrators may want to decrease the number of medium and/or high priority threads and increase the number of low priority threads. In that case, some medium or high priority jobs may not be executed due to the lack of threads.
What happens if all low priority threads are used and there is no low priority thread to be borrowed?
Administrators may also increase the number of low priority threads without decreasing the number of medium/high priority threads. However, in this case, more memory is used because every connection requires 1MB of Random Access Memory (RAM) on the Strategy Intelligence Server.
Administrators are advised to compare the benefits and drawbacks of each option and configure database thread prioritization based on system use patterns.
