Workload analysis tool for relational databases

• US 20030225768A1
• Filed: 06/03/2002
• Published: 12/04/2003
• Est. Priority Date: 06/03/2002
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Chaudhuri, Surajit, Narasayya, Vivek, Zaki, Omer
• Granted Patent: US 7,281,013 B2
• Time in Patent Office: Days
• US Class Current: 707/10
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/958 Data cubes
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A method for providing workload information in a structured workload information data structure format that is organized according to a workload schema to be conducive to a given end usage of the information. The structured workload information can be made accessible using standard database analytical server applications to facilitate ad-hoc querying of the structured workload information to summarize and analyze the database workload or to facilitate exchange of workload information. A structured workload information (SWI) is constructed according to a SWI schema to facilitate a desired end usage of the workload information. The query information is extracted from the workload and stored in a structured workload information (SWI) data structure according to the schema based on the desired end usage of the information such as ad hoc querying or information exchange. The query information may be stored in a relational database having query information organized as a central fact table and a collection of hierarchical dimension tables or as an OLAP cube featuring hierarchical dimensions that arrange the query information in dimensions having objects ordered as a function of granularity or the information may be stored according to an XML schema wherein units of query information are separated by XML tags that identify a type of workload information.

Claims

1. In a relational database system upon which queries are executed, the relational database system having a workload comprising a series of logged queries and associated query information, a method for populating a structured workload information data structure to facilitate a given usage of the workload information by an end user, the method comprising the steps of:

extracting the query information from the workload;

storing the query information in the structured workload information data structure according to a predetermined schema.

2. The method of claim 1 wherein the predetermined schema is selected based on the given usage of the workload information.

3. The method of claim 1 wherein the predetermined schema is hierarchical in nature, such that objects in the schema are arranged in dimensions wherein objects in a dimension are ordered based on a degree of granularity.

4. The method of claim 1 comprising the step of storing the query information in a relational database.

5. The method of claim 4 wherein the relational query information is organized as a central fact table and a collection of hierarchical dimension tables.

6. The method of claim 1 wherein the predetermined schema is an OLAP schema for arranging query information in an OLAP cube.

7. The method of claim 6 wherein the OLAP cube comprises hierarchical dimensions that arrange the query information in dimensions having objects ordered as a function of granularity.

8. The method of claim 6 wherein the OLAP cube has a dimension for at least one of the following types of query information in the database workload:

data objects accessed by the query, a time the query occurred, a user submitting the query, a machine on which the query was submitted, a type of query, physical operators included in the query or an associated query plan, or a nature of predicates in the query.

9. The method of claim 1 wherein the step of extracting information from the workload is performed by accessing a query plan for each query in the workload.

10. The method of claim 1 wherein the query information comprises a plurality of units of information and the step of storing the query information is performed by appending identifying tags to each of the units of information.

11. The method of claim 10 wherein the identifying tags are XML tags that identify at least one of the following types of workload information:

SQL string for the query, query category, a list of tables and columns referenced or updated by the query, total optimizer estimated cost, estimated cardinality of the query, and a sequence of logical and physical operators and their arguments used in a query plan.

12. An apparatus for use on a relational database system upon which queries are executed, the relational database system having a workload comprising a series of logged queries and associated query information, the apparatus for populating a structured workload information data structure to facilitate a given usage of the workload information by an end user, the apparatus comprising:

an information extractor for extracting query information from the workload; and

a structured workload information populator for storing the query information in the structured workload information data structure.

13. The apparatus of claim 12 further comprising an information loader for storing the query information in a relational database.

14. The apparatus of claim 12 wherein the structured workload information (SWI) populator stores the query information according to a predetermined schema that is hierarchical in nature.

15. The apparatus of claim 12 wherein the structured workload information populator stores the query information as units of information having identifying tags appended to thereto that indicate a type of query information contained in the unit of information.

16. A computer readable medium having stored thereon a data structure for 3 facilitating analysis of the workload information, wherein the workload comprises a set of logged queries that have been executed against the database and associated query information, the data structure comprising a first data field containing data representing a first dimension comprising a first type of query information arranged in a hierarchy based on the level of granularity of the query information in the first dimension and a second data field containing data representing a second dimension comprising a second type of query information arranged in a hierarchy based on the level of granularity of the query information in the second dimension.

17. The computer readable medium of claim 16 wherein the first type of query information is a time a query occurred and the hierarchy of the first dimension ranges from year to millisecond.

18. A computer readable medium having instructions stored thereon for performing method steps for populating a structured workload information data structure to facilitate a given end usage of workload information for a relational database system upon which queries are executed, wherein the workload comprises a series of logged queries and associated query information, the method steps comprising:

extracting the query information from the workload;

storing the query information in the structured workload information data structure according to a predetermined schema.

19. The computer readable medium of claim 18 wherein the predetermined schema is selected based on the given end usage of the workload information.

20. The computer readable medium of claim 18 wherein the predetermined schema is hierarchical in nature, such that objects in the schema are arranged in dimensions wherein objects in a dimension are ordered based on a degree of granularity.

21. The computer readable medium of claim 18 wherein the method comprises the step of storing the query information in a relational database.

22. The computer readable medium of claim 18 wherein the relational query information is organized as a central fact table and a collection of hierarchical dimension tables.

23. The computer readable medium of claim 18 wherein the predetermined schema is an OLAP schema for arranging query information in an OLAP cube.

24. The computer readable medium of claim 23 wherein the OLAP cube comprises hierarchical dimensions that arrange the query information in dimensions having objects ordered as a function of granularity.

25. The computer readable medium of claim 23 wherein the OLAP cube has a dimension for at least one of the following types of query information for each query in the database workload:

data objects accessed by the query, a time the query occurred, a user submitting the query, a machine on which the query was submitted, a type of query, physical operators included in the query or an associated query plan, or a nature of predicates in the query.

26. The computer readable medium of claim 18 wherein the step of extracting information from the workload is performed by accessing a query plan for each query in the workload.

27. The computer readable medium of claim 18 wherein the query information comprises a plurality of units of information and the step of storing the query information is performed by appending identifying tags to each of the units of information.

28. The computer readable medium of claim 27 wherein the identifying tags are XML tags that identify at least one of the following types of workload information:

SQL string for the query, query category, a list of tables and columns referenced or updated by the query, total optimizer estimated cost, estimated cardinality of the query, a sequence and logical and physical operators and their arguments used in a query plan.

29. A schema for organizing workload information for a relational database into a structured workload information data structure that facilitates multi-dimensional analysis of the workload comprising a plurality of dimensions for organizing query information in a hierarchical fashion according to the granularity of the query information.

30. The schema of claim 29 comprising a dimension for at least one of the following types of query information in the database workload:

data objects accessed by the query, a time the query occurred, a user submitting the query, a machine on which the query was submitted, a type of query, physical operators included in the query or an associated query plan, or a nature of predicates in the query.

31. A schema for organizing workload information for a relational database into a structured workload information data structure that facilitates exchange of the workload between remotely computers, the schema comprising identifying tags that are appended to various types of query information.

32. The schema of claim 31 wherein the identifying tags are XML tags that identify at least one of the following types of workload information:

SQL string for the query, query category, a list of tables and columns referenced or updated by the query, total optimizer estimated cost, estimated cardinality of the query, a sequence and logical and physical operators and their arguments used in a query plan.