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 ad-hoc SQL queries
We will learn how to use an object plan guide to optimize a query for a particular value. An object plan guide is created upon a stored procedure or user defined functions. A SQL query statement specified in the plan guide...
