Graphical query analyzer

• US 6,744,449 B2
• Filed: 06/11/2002
• Issued: 06/01/2004
• Est. Priority Date: 12/16/1998
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Vasandani, Manu, MacLeod, Stewart P., Kiernan, Casey L.
• Primary Examiner(s): Vu, Kim
• Assistant Examiner(s): Truong, Cam-Y
• Application Number:
  US10/167,849
• Publication Number:
  US 20030177137A1
• Time in Patent Office: 721 Days
• Field of Search: 707/2, 707/100, 707/3, 707/102, 345/713, 345/859, 345/772, 705/402, 705/404
• US Class Current: 715/772
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A user specifies one or more queries comprising a batch of SQL statements. Each query submitted is displayed and represented as a tree, with each operation in the execution plan for the query represented by a corresponding tree node. This representation intuitively conveys the DBMS execution strategy which would be used to process the queries. The tree nodes are displayed as icons, with a unique one of such icons corresponding respectively to each of the possible query operations. In addition, the computing cost of each operation (each node) as a percentage of overall query cost is displayed, as is the cost of each query as a percentage of the overall cost of the specified query batch. A user may select an operation (tree node) with a conventional mouse, whereupon a user interface will show more detailed cost statistics relating to the selected operation.

Claims

1. A computer based method for providing a graphical cost analysis of a plurality of user specified queries, the computer based method comprising:

a. determining an optimal execution plan for one of the user specified queries;

b. displaying on a display device a tree structure representation of the one of the user specified queries, the tree structure representation comprising icon nodes connected by arrow representations, the icon nodes each corresponding to one of query operations which comprise the execution plan, each arrow representation having a width bearing a mathematical relationship to an execution cost of one of the query operations corresponding to an icon node from which the arrow representation originates;

c. checking for user selection of one of the icon nodes via a user input device;

d. in the event of user selection of one of the icon nodes, displaying on the display device additional cost statistics for the query operation corresponding to the one of the icon nodes selected; and

e. repeating steps a-d until the steps have been performed for each of the plurality of user specified queries.

2. The computer based method of claim 1 wherein the mathematical relationship is a logarithmic relationship.

3. The computer based method of claim 1 wherein the user input device is a mouse device.

4. The computer based method of claim 1 also comprising the step of displaying a percentage cost of each of the query operations in relation to a total query cost.

5. The computer based method of claim 1 wherein the additional cost statistics include an estimated row count.

6. The computer based method of claim 5 wherein the additional cost statistics include an estimated row size.

7. The computer based method of claim 6 wherein the additional cost statistics include an estimated I/O cost.

8. The computer based method of claim 7 wherein the additional cost statistics include an estimated CPU cost.

9. The computer based method of claim 8 wherein the additional cost statistic include an estimated number of executes.

10. The computer based method of claim 9 wherein the additional cost statistic include an estimated subtree cost.

11. The computer based method of claim 10 wherein the additional cost statistics include an argument indicator.