USE AdventureWorks
GO


DROP PROCEDURE Sales.GetSalesOrderByCountry2
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry2 (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END
GO

select * from sys.plan_guides
GO

-- clean up the existing plan guides
declare c cursor
 for select * from sys.plan_guides
open c
while (@@cursor_status <> 0)
  begin
    
  end
 


-- Common uses of plan guides

-- One use is to force plans
-- A plan will not be cached if its greater than 8000 bytes long
-- So most forced plans will never be cached
-- See plan forcing

-- The following is an example of a plan guide created for a simple SQL statement:
-- Adds a join hint

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


-- now use it
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'

-- parameterized queries
-- optimize for a ertain value of the parameter

sp_create_plan_guide N'PlanGuide2',
N'SELECT *
FROM Sales.SalesOrderHeader h, Sales.Customer c, Sales.SalesTerritory t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country',
N'OBJECT',
N'Sales.GetSalesOrderByCountry2',
NULL,
N'OPTION (OPTIMIZE FOR(@Country = N''US''))'


-- Eliminating existing hints in code
sp_create_plan_guide 
@name = N'PlanGuide1',
@stmt = N'SELECT t1.a, t2.b FROM Tab1 t1, Tab1 t2 WHERE t1.a = t2.a
OPTION(LOOP JOIN)
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = NULL
GO

-- forcing non-parallel execution
sp_create_plan_guide 
@name = N'PlanGuide3', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY 
OrderDate DESC
',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

-- forcing join types

sp_create_plan_guide 
@name = N'PlanGuide4',
@stmt = N'SELECT FirstName, LastName, City, VacationHours
FROM HumanResources.Employee e, Person.Contact c, Person.Address a,
 HumanResources.EmployeeAddress ea
WHERE e.EmployeeID = ea.EmployeeID
AND ea.AddressID = a.AddressID
AND e.ContactID = c.ContactID
',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (HASH JOIN)'
GO

-- Forcing parameterization

SELECT Product.ProductNumber, Product.ReorderPoint,
 SUM(ProductInventory.Quantity) AS Quantity
FROM Production.Product, Production.ProductInventory
WHERE Product.ProductID = ProductInventory.ProductID
AND Product.ProductNumber = N'BE-2908'
AND Product.ReorderPoint > 0
GROUP BY Product.ProductID, Product.ProductNumber, Product.ReorderPoint

-- You do this by creating a template plan guide

DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT Product.ProductNumber,
 Product.ReorderPoint, SUM(ProductInventory.Quantity) AS Quantity
FROM Production.Product, Production.ProductInventory
WHERE Product.ProductID = ProductInventory.ProductID
AND Product.ProductNumber = N''BE-2908''
AND Product.ReorderPoint > 0
GROUP BY Product.ProductID, Product.ProductNumber,
 Product.ReorderPoint',
@stmt OUTPUT, 
@params OUTPUT
EXEC sp_create_plan_guide N'PlanGuideTemplate1', 
@stmt, 
N'TEMPLATE',
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)'

/* Best practices:

1. Plan guides "pin" an object, i.e. can't change until you drop the plan guide
   (note: Check if the SMO scripter knows about this)

2. Plan guides can be done anywhere you use a query hint. Plan forcing only works with SELECT and SELECT INTO

3. Text must match *character for character*

4. No encrypted objects. No DDL triggers.

5. A "few dozen" queries is limit of plan guides.

6. Do not hand-edit XML showplans.

7. Plan guides are only supported in the Standard & Enterprise.
   Not supported in Express and Workgroup

*/