Plan guides

There might be situations where you need to apply a hint to a query but you are unable or unwilling to change your query code or your application. A common case where this occurs is if you are working with third-party code or applications that you cannot change.

Plan guides, a feature introduced with SQL Server 2005, can help you in these cases. Plan guides essentially work by keeping a list of queries on the server, along with the hints you want to apply to them, basically separating the hint specification from the query itself. To use a plan guide, you need to provide SQL Server with the query you want to optimize and either a query hint using the OPTION clause or an XML plan using the USE PLAN hint, which will be explained in the next section. When the query is optimized, SQL Server will apply the hint requested in the plan guide definition. You can also specify NULL as a hint in your plan guide to remove an existing hint in your application. Plan guides can also...