Optimizing a query using an object plan guide
Plan guide is a feature in SQL Server that you can use to tune queries, which are developed/deployed by third parties and for which you are not allowed to modify the code. With a plan guide, it's possible to attach query hints to the queries that are executed against the database server. A SQL Server attaches the query hints, as specified by the plan guide, to the query before executing it. In this way, an ad-hoc query or a query in the stored procedure can be tuned without changing it in the source code.
There are three types of plan guides, as follows:
- Object plan guide: Used with stored procedures and user-defined functions
- SQL plan guide: Used with ad-hoc SQL queries
- Template plan guide: Used with ...