Query performance degradation analysis timing
Mechanisms for improving resource usage of a computing system by properly balancing the timing of performance degradation analysis of an executable action set of a query based on an impact on the workload of that query. The mechanism collects workload metrics for each of multiple queries and their associated executable action sets. Performance degradation occurs when the current executed action set is no longer appropriate for the corresponding query such that more resources are used than are necessary. For each query, the system determines whether to perform degradation analysis of the current executable action set in a manner that is workload aware. That is, queries that occupy more computing resources or that impact the overall workload more will tend to have degradation analysis performed more frequently than those queries that have less impact on the overall workload. This results in improved resource usage for the workload with low overhead.
1. A computing system comprising:
one or more processors; and
one or more computer-readable media having stored thereon computer-executable instructions that are structured such that, when executed by the one or more processors, cause the computing system to perform a method for evaluating whether query performance degradation analysis is to be performed, the method comprising:
collecting workload metrics for each of multiple pairs constituting a respective query and executable action plan that directs how the query is to be executed; and
for each at least one of the multiple pairs, using the collected workload metrics to determine when to perform query performance degradation analysis of a paired query and executable action plan, the using the collected workload metrics comprising:
ranking the collected workload metrics based on an impact on workload; and
causing the performance degradation analysis of a higher-ranked pair to be performed more frequently than that of a lower-ranked pair, so that the timing of the performance degradation analysis is dependent on the collected workload metrics.
2. The computing system in accordance with claim 1 , the determination of when to perform query performance degradation analysis being performed by an in-memory analysis component.
3. The computing system in accordance with claim 2 , the analysis component performing the determination of whether to perform query performance degradation analysis using at least a portion of the collected workload metrics that is also in-memory.
4. The computing system in accordance with claim 3 , the portion of the collected workload metrics that is in-memory comprising a single metric that is a function of multiple performance metrics.
5. The computing system in accordance with claim 1 , the method further comprising:
performing the query performance degradation analysis for a paired query and executable action plan if it has been determined to then perform the query performance degradation analysis.
6. The computing system in accordance with claim 5 , the query performance degradation analysis resulting in a pairing of a different executable action plan with the query.
7. The computing system in accordance with claim 5 , the query performance degradation analysis not resulting in a pairing of a different executable action plan with the query.
8. The computing system in accordance with claim 1 , the timing of the query performance degradation analysis being performed for the query based on a ranking of the query when ranked with the collected performance metrics.
9. The computing system in accordance with claim 1 , the query performance degradation analysis being performed for each at least some of the multiple pairs.
10. The computing system in accordance with claim 1 , the query performance degradation analysis being performed for each of at least most of the multiple pairs.
11. The computing system in accordance with claim 1 , the multiple pairs constituting queries against a database.
12. The computing system in accordance with claim 1 , the collected workload metrics being updated for a particular pair of a query and an executable action plan each time the query and executable action plan are executed.
13. The computing system in accordance with claim 1 , the determining when to perform query performance degradation analysis of a paired query and executable action plan being performed each time the query is received.
14. The computing system in accordance with claim 1 , the multiple pairs of queries and executable action plans each being against a relational database.
15. The computing system in accordance with claim 1 , the workload metrics for at least one executable action pair comprising a single metric that is a function of multiple performance metrics.
16. The computing system in accordance with claim 15 , a weighting of the performance metrics of prior executions of that same query and executable action plan decaying over time in influence on the single metric.
17. The computing system in accordance with claim 15 , the multiple performance metrics comprising at least two of the following:
processing unit time;
processing unit cycles;
elapsed time;
wait time;
memory consumed;
number of input/output operations;
consumed channel bandwidth; and
amount of parallelism.
18. The computing system in accordance with claim 15 , the multiple performance metrics comprising at least four of the following:
processing unit time;
processing unit cycles;
elapsed time;
wait time;
memory consumed;
number of input/output operations;
consumed channel bandwidth; and
amount of parallelism.
19. A method for evaluating whether query regression analysis is to be performed, the method comprising:
collecting workload metrics for each of multiple pairs constituting a corresponding respective query and executable action plan that directs how the query is to be executed; and
for each at least one of the multiple pairs, using the collected workload metrics to determine when to perform query performance degradation analysis of a paired query and executable action plan, the using the collected workload metrics comprising:
ranking the collected workload metrics based on an impact on workload; and
causing the performance degradation analysis of a higher-ranked pair to be performed more frequently than that of a lower-ranked pair, so that the timing of the performance degradation analysis is dependent on the collected workload metrics.
20. A method for evaluating whether query regression analysis is to be performed, the method comprising:
collecting workload metrics for each of multiple pairs constituting a corresponding respective data request type and executable action set that directs how the data request type is to be executed against data; and
for each at least one of the multiple pairs, using the collected workload metrics to determine when to perform request performance degradation analysis of a paired data request type and executable action set, the using the collected workload metrics comprising:
ranking the collected workload metrics based on an impact on workload; and
causing the performance degradation analysis of a higher-ranked pair to be performed more frequently than that of a lower-ranked pair, so that the timing of the performance degradation analysis is dependent on the collected workload metrics.