
USE AdventureWorks
GO

sp_control_plan_guide N'DROP', N'GuideOptForUS'
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

-- Start profiler, capture SQL:BatchStarting
-- Use the statement by right-click, extract data
DECLARE @Country NCHAR(2)
SET @Country = 'AU'
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country


sp_create_plan_guide 
@name = N'GuideOptForUS',
@stmt = 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',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry2',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'
go

-- check plan with guide on
sp_control_plan_guide N'ENABLE', N'GuideOptForUS'
go

EXECUTE Sales.GetSalesOrderByCountry2 'AU'

-- and with guide off
sp_control_plan_guide N'DISABLE', N'GuideOptForUS'
go

EXECUTE Sales.GetSalesOrderByCountry2 'AU'
