Query plan selection control using run-time association mechanism

• US 20060224563A1
• Filed: 04/05/2005
• Published: 10/05/2006
• Est. Priority Date: 04/05/2005
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Galindo-Legaria, Cesar A., Krishnamoorthy, Ganapathy Subramaniam, Ismert, Erik, Zabokritski, Evgueni, Kollar, Lubor, Verkhedkar, Sameer Arun, Hanson, Eric N.
• Granted Patent: US 7,640,230 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A system and method for creation, modification and/or use of plan guide(s) is provided. Plan guide(s) can be employed to optimize the performance of query(ies) when it is not possible and/or desirable to change the text of the query directly. Plan guides can be useful, for example, when a small subset of queries in a database application deployed from a third-party vendor is not performing as expected. Plan guides influence optimization of queries by attaching query hints to them. In accordance with an aspect of the subject invention, a query plan execution system is provided. The system includes an execution component, a query optimizer, and, a plan cache. Plan guides and other associated metadata are stored in a plan guide metadata store.

Claims

1. A query plan execution system comprising:

a plan guide metadata store that stores information associated with one or more plan guides, a plan guide stores information that can influence optimization of a query; and

,

an execution environment that determines whether a plan guide exists for a particular entity, and, if a plan guide exists, employs the plan guide when optimizing the query.

2. The system of claim 1, the optimization of the query influenced by attaching query hints stored in the plan guide.

3. The system of claim 1, the plan guide metadata store further stores the one or more plan guides.

4. The system of claim 1, the entity comprising one of a batch of query text, a module, and a stored procedure.

5. The system of claim 1, the plan guide facilitates compilation of a particular query with a particular parameter.

6. The system of claim 1, the optimized query employed to generate a query plan.

7. The system of claim 1, information stored in the plan guide metadata store modified by a plan guide modification system that selectively drops, enables and/or disables one or more plan guides.

8. The system of claim 1, the plan guide metadata store stores a unique name associated for each plan guide associated with a particular database.

9. The system of claim 1, the execution environment parameterizes a statement of the entity using forced parameterization.

10. The system of claim 1, the execution environment parameterizes a statement of the entity using simple parameterization.

11. The system of claim 1, the plan guide is of type object for a query in a stored procedure.

12. The system of claim 1, the plan guide is of type SQL to match a query in a batch.

13. The system of claim 1, the plan guide is of type template for a parameterized form of a query, the plan guide directs the execution environment to force parameterization of the query.

14. The system of claim 1, only plan guides scoped to a current database available to the execution environment.

15. The system of claim 1, the plan guide comprising an option clause which is added to the query before it is optimized and compiled.

16. A method of executing a query within an entity comprising:

receiving information associated with execution of the entity;

determining whether a plan guide exists for a statement of the entity;

modifying a query based on the plan guide, if one exists; and

,

optimizing the modified query to produce a query plan.

17. The method of claim 16, the entity comprising one of a batch of query text, a module, and a stored procedure.

18. The method of claim 16 further comprising at least one of the following:

executing the query plan; and

,

updating a plan cache with the query plan.

19. A computer readable medium having stored thereon computer executable instructions for carrying out the method of claim 16.

20. A data packet transmitted between two or more computer components that facilitates query optimization, the data packet comprising:

a plan guide, the plan guide comprising a plan guide name field, a type field, a parameter field and a hint field.