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 ...

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.