Queue Ratios
A ratio system is used to control how queues of different priorities share resources. Queues that have sessions on their wait list will execute queries if they stay under their ratio limit. The ratio limit is determined as follows:
1. The highest priority queue with active queries will not be affected by ratio limits. The queue will run queries up to its maximum allowed concurrent queries limit.
If a critical priority queue has queued queries, then the queues that are assigned high, medium, and low priorities may be capped by ratio limits.
If no critical priority queues have running queries, and a high priority queue has queued queries, then queues of medium and low priorities may be capped by ratio limits.
If no critical or high priority queues have running queries, and a medium priority queue has queued queries, then queues of low priority may be capped by ratio limits.
If only low priority queues are running queries, then ratio limits do not apply.
2. A queue that can be capped by ratio limits will only execute an additional query if its resource ratio does not exceed its ratio limits.
The resource ratio limit of a queue is the ratio number of this queue divided by the total of the ratio numbers of the highest active priority’s queues.
Example:
Let queue Q be the highest priority queue that has queued queries.
If queue Q has a ratio number of 10, and a lower priority queue has a ratio number of 5, then the lower priority queue will use half (5/10) as many resources as queue Q.
This is calculated individually for each managed resource (CPU, disk I/O). If any resource exceeds this limit, then the lower priority queue will not run additional queries until the current usage drops below the allowed limit. Dropping below the limit may mean the lower priority queue uses less resources (due to queries completing) but may also mean that the higher priority queues use more resources.
If multiple queues exist with the highest priority, the ratio is based on the sum of the queue’s resources and ratio numbers. For example, assume queue Q2 has the same priority as queue Q, and Q and Q2 have ratio numbers of 10 and 20 respectively. If the lower priority queue has a ratio number of 5, then the lower priority queue will use 5/30 of the total resources of Q and Q2 combined.
Last modified date: 03/21/2024