Enforcing system resource usage limits on query requests based on grouping query requests into workgroups and assigning workload groups to resource pools

• US 8,065,682 B2
• Filed: 02/27/2007
• Issued: 11/22/2011
• Est. Priority Date: 02/27/2007
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Baryshnikov, Boris, Rajamani, Ravishankar, Choe, In-Jerng, Surna, Aleksandras, Verbitski, Alexandre
• Primary Examiner(s): An; Meng
• Assistant Examiner(s): Diep; Louis
• Application Number:
  US11/679,520
• Publication Number:
  US 20080209428A1
• Time in Patent Office: 1,729 Days
• Field of Search: 718100-108
• US Class Current: 718/104
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 2209/5011 Pool
• CPC Class Codes: G06F 9/5027 the resource being a machine, e.g. CPUs, Servers, Terminals

Abstract

A database can have multiple requests applied at one time. Each of these requests requires a specific amount of server resources. There can be a differentiation of user-submitted workloads between each other. These workloads are a set of queries submitted by different users. Each query can have specific resource limits. In addition, each set can have specific resource limits.

Claims

1. A system for enforcing system resource usage limits on query requests accessing a database based on grouping query requests into workload groups and based on assigning workload groups to resource pools, the system comprising:

at least one processor; and

one or more computer storage devices having stored thereon computer executable instructions that, when executed by the at least one processor, implement a method, comprising;

defining a plurality of workload groups, wherein each workload group is configured to be assigned one or more query requests and comprises one or more resource policies that govern access permitted to the one or more query requests to use system resources that are made available to the workload group, wherein the resource policies place limits on the use of system resources by the one or more query requests, the plurality of workload groups in a default workload group and other workload groups;

defining a plurality of resource pools, wherein each resource pool is configured to be assigned one or more of the plurality of workload groups and defines an arrangement of system resources that are available to the one or more workload groups, and wherein each resource pool comprises one or more pool policies that govern resource consumption by query requests whenever a plurality of workload groups are assigned to the resource pool, the plurality of resource pools including a default resource pool that defines an arrangement of system resources available to the default workload group;

defining a classifier function that associates incoming query requests with the plurality of workload groups based on attributes of the incoming query requests, the classifier function associating any incoming query request not associated any of the other workload groups to the default workload group;

receiving a particular query request;

associating the particular query request with a particular workload group of the plurality of workload groups based, at least in part, on the classifier function and an attribute of the particular query request, the particular workload group comprising the default workload group whenever it is determined that the particular query request is not associated with any of the other workload groups;

assigning the particular workload group to a particular resource pool of the plurality of resource pools that is associated with a portion of system resources, thereby making the portion of the system resources available to the particular workload group, the particular resource pool comprising the default resource pool when the particular workload group comprises the default workload group; and

governing access permitted to the particular query request to system resources using a particular resource policy of the particular workload group and a particular pool policy of the particular resource pool, wherein the governing includes;

limiting the portion of the system resources available to the particular workload group as per the particular pool policy; and

limiting the use of the portion of system resources available to the particular workload group by the particular query request as per the particular resource policy.

2. The system of claim 1, wherein the particular query request is a first query request, the method further comprising:

subsequently receiving a second query request; and

associating the second query request with the particular workload group based on knowledge of the previous association of the first query request with the particular workload group.

3. The system of claim 1, wherein associating the particular query request with the particular workload group comprises determining that the particular query request is not associated with any of the other workload groups, and associating the particular query request with the default workload group and associating the default workload group with the default resource pool.

4. The system of claim 1, wherein associating the particular query request with the particular workload group comprises associating the particular query request with a system workload group, selected from one or more system workload groups, that comprises query requests that are executed on behalf of the system, and associating the system workload group with at least one system resource pool.

5. The system of claim 1, wherein defining the classifier function comprises receiving user input specifying one or more rules for the classifier function.

6. The system of claim 1, further comprising deteimining that the particular query request is authorized to be made at the system.

7. A method for enforcing system resource usage limits on query requests accessing a database based on grouping query requests into workload groups and based on assigning workload groups to resource pools, the method comprising:

defining a plurality of workload groups, including a default workload group, wherein each workload group is configured to be assigned one or more query requests and comprises one or more resource policies that govern access permitted to the one or more query requests to use system resources that are made available to the workload group, and wherein the resource policies place limits on the use of system resources by the one or more query requests;

defining a plurality of resource pools, including a default resource pool corresponding to the default workload group, wherein each resource pool is configured to be assigned one or more of the plurality of workload groups and defines an arrangement of system resources that are available to the one or more workload groups, and wherein each resource pool comprises one or more pool policies that govern resource consumption by query requests whenever a plurality of workload groups are assigned to the resource pool;

defining a classifier function that associates incoming query requests with one of the plurality of workload groups based on one or more attributes of the incoming query requests, the classifier function associating the incoming query requests with the default workload group when the one or more attributes of the incoming query requests do not associate the incoming query requests with any other workload group;

receiving a particular query request;

associating the particular query request with a particular workload group of the plurality of workload groups based, at least in part, on the classifier function and an attribute of the particular query request;

assigning the particular workload group to a particular resource pool of the plurality of resource pools that is associated with a portion of system resources, thereby making the portion of the system resources available to the particular workload group; and

governing access permitted to the particular query request to system resources using a particular resource policy of the particular workload group and a particular pool policy of the particular resource pool, wherein the governing includes;

limiting the portion of the system resources available to the particular workload group as per the particular pool policy; and

limiting the use of the portion of system resources available to the particular workload group by the particular query request as per the particular resource policy.

8. The method of claim 7, wherein the particular query request comprises a user request.

9. The method of claim 7, wherein the particular resource pool is a first resource pool, and wherein the first resource pool and a second resource pool overlap, such that the first and second resource pools are both assigned a first portion of the computer system resources.

10. The method of claim 9, wherein the first resource pool includes a second portion of the computer system resources that is unique to the first resource pool, and wherein the second resource pool includes a third portion of the computer system resources that is unique to the second resource pool.

11. The method of claim 7, wherein the particular resource pool includes at least one pool policy that defines a prioritization between the particular workload group and at least one different workload group when the particular workload group and the different workload group are both assigned to the particular resource pool.

12. The method of claim 7, wherein governing access permitted to the particular query request further includes:

stopping the particular query request;

permitting another query request to use the portion of system resources that were previously used by particular query request; and

subsequently resuming the particular query request.

13. The method of claim 7, further comprising:

receiving a second query request; and

associating the second query request with the particular workload group,

wherein limiting the use of the portion of system resources available to the particular workload group by the particular query request as per the particular resource policy, both at an individual request level and at an overall level, comprises limiting access to the particular request based on the second query request also using the portion of system resources available to the particular workload group.

14. The method of claim 7, defining a plurality of resource pools comprises defining at least one resource pool has a minimum portion of system resources not shared by any other resource pool, as well as an effective maximum portion of system resources available to the at least one resource pool.

15. The method of claim 14, wherein the effective maximum portion is 100 minus the sum of one or more minimum portions of other resource pools.

16. The method of claim 14, wherein free system resources between the minimum portion and the effective maximum portion are shared with one or more other resource pools.

17. The method of claim 7, wherein associating the particular query request with a particular workload group comprises automatically creating the particular workload group.

18. The method of claim 7, wherein the attribute of the particular query request comprises a type of user that created the request.

19. The method of claim 18, wherein the classifier classifies query requests from a first type of user to a first workload group and query requests from a second type of user to a second workload group.

20. The method of claim 7, wherein the resource policies place limits on the use of system resources by corresponding query requests both at an individual request level that places limits on the individual request singly, as well as at an overall level that places limits on all the corresponding query requests as a group.

21. One or more computer storage devices having stored thereon computer executable instructions that, when executed by at least one processor of a computer system, implement a method for enforcing system resource usage limits on query requests accessing a database based on grouping query requests into workload groups and based on assigning workload groups to resource pools, the method comprising:

defining a plurality of workload groups, wherein each workload group is configured to be assigned one or more corresponding query requests and comprises one or more resource policies that govern access permitted to the one or more corresponding query requests to use system resources that are made available to the workload group, wherein the resource policies place limits on the use of system resources by the one or more corresponding query requests, the plurality of workload groups including a default workload group to which query requests not associated with any other workload group are assigned;

defining a plurality of resource pools, wherein each resource pool is configured to be assigned one or more corresponding workload groups of the plurality of workload groups and defines an arrangement of system resources that are available to any of the one or more corresponding workload groups, and wherein each resource pool comprises one or more pool policies that govern resource consumption by query requests in circumstances when a plurality of workload groups are assigned to the resource pool, the plurality of resource pools including a default resource pool that defines an arrangement of system resources available to query requests associated with the default workload group;

defining a classifier function that associates incoming query requests with one of the plurality of workload groups based on attributes of the incoming query requests;

receiving a particular query request;

associating the particular query request with a particular workload group of the plurality of workload groups based, at least in part, on the classifier function and an attribute of the particular query request, the particular workload group comprising the default workload group when no attribute of the particular query request associates the particular query request with any other workload group in the plurality of workload groups;

assigning the particular workload group to a particular resource pool of the plurality of resource pools that is associated with a portion of system resources, thereby making the portion of the system resources available to the particular workload group, the particular resource pool comprising the default resource pool when no attribute of the particular query request associates the particular query request with any other workload group; and

governing access permitted to the particular query request to system resources using a particular resource policy of the particular workload group and a particular pool policy of the particular resource pool, wherein the governing includes;

limiting the portion of the system resources available to the particular workload group as per the particular pool policy; and

limiting the use of the portion of system resources available to the particular workload group by the particular query request as per the particular resource policy.