Creating a plan guide of type OBJECT for a query in a stored procedure
Definition Description
https://msdn.microsoft.com/en-us/library/ms179880(v=sql.90).aspx The following example creates a plan guide that matches a query executed in the context of an application-based stored procedure, and applies the OPTIMIZE FOR hint to the query. Here is the stored procedure: IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL DROP PROCEDURE Sales.GetSalesOrderByCountry; GO CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60)) AS BEGIN SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID WHERE t.CountryRegionCode = @Country; END GO Here is the plan guide created on the query in the stored procedure: EXEC sp_create_plan_guide @name = N'Guide1', @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID INNER JOIN Sales.SalesTerritory AS t ON c.TerritoryID = t.TerritoryID WHERE t.CountryRegionCode = @Country', @type = N'OBJECT', @module_or_batch = N'Sales.GetSalesOrderByCountry', @params = NULL, @hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';
