Tune resource setting levels for query execution

Patent metadata

Field	Value
Publication number	US-10789247-B2
Application number	US-201815986493-A
Country	US
Kind code	B2
Filing date	May 22, 2018
Priority date	May 22, 2018
Publication date	Sep 29, 2020
Grant date	Sep 29, 2020

Abstract

According to examples, an apparatus may include a machine readable medium on which is stored machine readable instructions that may cause a processor to, for each of a plurality of resource setting levels, determine resource usage characteristics and execution times of executed workloads, assign, based on the resource usage characteristics of the executed workloads, each of the executed workloads into one of a plurality of resource bins, determine, for each of the resource bins, an average execution time of the executed workloads in the resource bin, determine a total average execution time of the executed workloads from the determined average execution times, identify a lowest total average execution time of the determined total average execution times, determine the resource setting level corresponding to the identified lowest total average execution time, and tune a resource setting to the determined resource setting level.

First claim

What is claimed is:

1. An apparatus comprising:
a processor; and
a machine readable medium on which is stored machine readable instructions that are to cause the processor to:
for each of a plurality of resource setting levels,
determine resource usage characteristics and execution times of executed workloads;
assign, based on the resource usage characteristics of the executed workloads, each of the executed workloads into one of a plurality of resource bins, wherein each of the plurality of resource bins defines a different range of resource usage characteristics;
determine, for each of the resource bins, an average execution time of the executed workloads in the resource bin; and
determine a total average execution time of the executed workloads from the determined average execution times;

identify a lowest total average execution time of the determined total average execution times;
determine the resource setting level of the plurality of resource setting levels corresponding to the identified lowest total average execution time; and
tune a resource setting to the determined resource setting level.

2. The apparatus of claim 1 , wherein the resource usage characteristics comprise ideal memory sizes for execution of the workloads and wherein the plurality of resource bins are divided according to different ranges of memory sizes.

3. The apparatus of claim 1 , wherein the plurality of resource setting levels comprise at least one of degree of parallelism, maximum memory, or cost threshold for parallelism.

4. The apparatus of claim 1 , wherein the instructions are further to cause the processor to:
apply weights to the average execution times of executed workloads assigned to the resource bins, wherein the weights applied to the resource bins are based on probabilities of occurrence of the executed workloads in the resource bins.

5. The apparatus of claim 1 , wherein the instructions are further to cause the processor to:
apply the determined resource setting level in the execution of additional workloads.

6. The apparatus of claim 1 , wherein the instructions are further to cause the processor to:
partition the workloads into multiple workload groups,
determine total average execution times of each of the multiple workload groups for each of the plurality of resource setting levels;
identify, for each of the multiple workload groups, a lowest total average execution time of the determined total average execution times; and
determine, for each of the multiple workload groups, a respective resource setting level of the plurality of resource setting levels corresponding to the identified lowest total average execution time.

7. The apparatus of claim 6 , wherein the instructions are further to cause the processor to:
determine priority levels of the multiple workload groups; and
apply the determined resource setting level in the execution of additional workloads in the multiple workload groups according to the determined priority levels.

8. The apparatus of claim 1 , wherein the instructions are further to cause the processor to:
identify, from the determined total average execution times of the executed workloads, a plurality of workload groups, wherein a respective resource setting level is to minimize the total average execution time for the workloads in each of the workload groups.

9. The apparatus of claim 1 , wherein the instructions are further to cause the processor to:
determine whether the workloads were executed under mixed-modes of resource setting levels;
determine whether the mixed-modes of resource setting levels are compatible with each other; and
based on a determination that one of the resource setting levels is incompatible with another one of the resource setting levels, discard workloads executed under an incompatible resource setting level from the assignment of the executed workloads into the plurality of resource bins.

10. A method comprising:
at each resource setting level of a plurality of resource setting levels,
assigning, by the processor, executed queries into respective resource bins based on determined resource usage characteristics of the executed queries, each of the resource bins defining a different range of resource usage characteristics;
determining, by the processor and for each of the resource bins, an average execution time of the executed queries in the resource bin; and
determining, by the processor, a total average execution time of the executed queries from the determined average execution times;

determining, by the processor, the resource setting level corresponding to a lowest total average execution time of the determined total average execution times; and
tuning, by the processor, a resource setting to the determined resource setting level.

11. The method of claim 10 , further comprising:
calculating, for each of the resource bins, a probability that an executed query will be assigned to the resource bin;
assigning a respective weight to the average execution time of the executed queries in a resource bin based on the calculated probability for the bin; and
wherein determining the total average execution time further comprises determining the total average execution time as a weighted sum of the determined average execution times.

12. The method of claim 10 , wherein the resource usage characteristics comprise ideal memory sizes for execution of the queries, wherein the bins are divided according to different ranges of memory sizes, and wherein the plurality of resource setting levels comprise at least one of a maximum degree of parallelism pertaining a number of parallel threads used to execute a query, maximum memory that a query is permitted, or cost threshold for parallelism.

13. The method of claim 10 , further comprising:
determining a baseline lowest total average execution time; and
wherein determining the resource setting level corresponding to the lowest average execution time further comprises determining the resource setting level using the baseline lowest total average execution time.

14. The method of claim 10 , further comprising:
executing additional queries under the tuned resource setting level.

15. The method of claim 10 , further comprising:
partitioning the queries into multiple query groups,
determining total average execution times of each of the multiple query groups for each of the plurality of resource setting levels;
identifying, for each of the multiple query groups, a lowest total average execution time of the determined total average execution times; and
determining, for each of the multiple query groups, a respective resource setting level of the plurality of resource setting levels corresponding to the identified lowest resource setting level.

16. The method of claim 15 , further comprising:
determining priority levels of the multiple query groups; and
setting the resource setting level in the execution of additional queries in the multiple query groups according to the determined priority levels.

17. The method of claim 10 , further comprising:
identifying, from the determined total average execution times of the executed queries, a plurality of query groups, wherein a respective resource setting level is to minimize the total weighted average execution time for the queries in each of the query groups.

18. A non-transitory machine readable medium on which is stored machine readable instructions that when executed by a processor, cause the processor to:
determine resource usage characteristics and execution times

Assignees

• Microsoft Technology Licensing Llc

Inventors

• Sen Rathijit
• Ramachandra Karthik S
• Halverson Alan D

Classifications

• G06F9/5072

  Grid computing · CPC title

• G06F9/505

  considering the load · CPC title

• G06F16/24532 Primary

  of parallel queries · CPC title

• G06F9/5027

  the resource being a machine, e.g. CPUs, Servers, Terminals · CPC title

• G06F9/5011

  the resources being hardware resources other than CPUs, Servers and Terminals · CPC title