USE PLAN

Finally, let’s take a look at the USE PLAN query hint, which was also introduced with SQL Server 2005. The USE PLAN hint takes the use of hints to the extreme by allowing the user to specify an entire execution plan as a target to be used to optimize a query. This hint is useful when you know that a better plan than the query optimizer’s suggestion exists. This can be the case, for example, when a better-performing plan was produced in the past, in a different system, or even in a previous version of SQL Server. The plan should be specified in XML format, and you will generally use SQL Server itself to generate the XML text for the desired plan since it can be extremely difficult to write an XML plan manually.

The USE PLAN hint can force most of the specified plan properties – including the tree structure, join order, join algorithms, aggregations, sorting and unions, and index operations such as scans, seeks, and intersections – so that only...