/
plan_guide.sql
38 lines (34 loc) · 1.04 KB
/
plan_guide.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
IF(SELECT OBJECT_ID('dbo.spCustomerList')) IS NOT NULL
DROP PROC dbo.spCustomerList
GO
IF(SELECT OBJECT_ID('dbo.spCustomerList')) IS NOT NULL
DROP PROC dbo.spCustomerList
GO
CREATE PROCEDURE dbo.spCustomerList
@CustomerId INT
AS
SELECT soh.SalesOrderNumber
,soh.OrderDate
,sod.OrderQty
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerId
GO
sp_create_plan_guide
@name = N'MyGuide',
@stmt = N'SELECT soh.SalesOrderNumber
,soh.OrderDate
,sod.OrderQty
,sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerId',
@type = N'OBJECT',
@module_or_batch = N'dbo.spCustomerList',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@CustomerId = 1))'
EXEC dbo.spCustomerList @CustomerId = 7920 WITH RECOMPILE;
EXEC dbo.spCustomerList @CustomerId = 30118 WITH RECOMPILE;