The Plan Guides feature in SQL Server 2005 provides a method for injecting query hints into SQL statements in batches, stored procedures (SP), and so forth. However, it does not require any modification to the query itself. The mechanism uses a look-up mapping table and is very useful when the query for which the plan has to be influenced or forced originates in a non-modifiable application.
sp_create_plan_guide @name = N'PlanGuide1', @stmt = N'SELECT COUNT(*) AS Total FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
WHERE h.SalesOrderID = d.SalesOrderID
and
h.OrderDate BETWEEN ''1/1/2000'' AND ''1/1/2005'' ',
@type = N'SQL', @module_or_batch = NULL, @params = NULL,
@hints = N'OPTION (MERGE JOIN)'
GO
--drop the plan guide
sp_control_plan_guide N'DROP', N'PlanGuide1'
