Databases Reference
In-Depth Information
The Plan Guide
Let's create a plan guide such that SQL Server will always return the first 40 rows as soon as possible so
that the user can start accessing the data while the rest of the rows are being processed. An important
point to note here is that SQL Server matches the statements of the query submitted with the query
statement stored in the plan guide, so the statement stored in the plan guide needs to be exactly the same
for it to be effective.
sp_create_plan_guide
@name = N'OrderDetails_Guide',
@stmt = N'SELECT A.RevisionNumber,A.OrderDate,A.DueDate,A.ShipDate,A.Status,
B.OrderQty,B.ProductId,
C.AccountNumber,C.CustomerType,
D.FirstName,D.MiddleName,D.LastName,D.EmailAddress,
E.AddressLine1,E.AddressLine2,E.StateProvinceId,E.PostalCode
FROM
SALES.SALESORDERHEADER A
INNER JOIN SALES.SALESORDERDETAIL B ON A.SALESORDERID = B.SALESORDERID
INNER JOIN SALES.CUSTOMER C ON A.CUSTOMERID = C.CUSTOMERID
INNER JOIN SALES.INDIVIDUAL F ON C.CUSTOMERID = F.CUSTOMERID
INNER JOIN PERSON.CONTACT D ON F.CONTACTID = D.CONTACTID
INNER JOIN SALES.CUSTOMERADDRESS G ON A.CUSTOMERID = G.CUSTOMERID
INNER JOIN PERSON.ADDRESS E ON G.ADDRESSID = E.ADDRESSID',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (FAST 40)'
The Query Plan after Creation of the Plan Guide
Here is the query plan after the plan guide has been created.
|
--Nested Loops(Inner Join, OUTER REFERENCES:([A].[SalesOrderID], [Expr1020]) WITH
UNORDERED PREFETCH)
|
--Nested Loops(Inner Join, OUTER
REFERENCES:([A].[SalesOrderID], [Expr1019]) WITH UNORDERED PREFETCH)
| |
--Nested Loops(Inner Join, OUTER
REFERENCES:([G].[CustomerID], [Expr1018]) WITH UNORDERED PREFETCH)
| | |
--Nested Loops(Inner Join, OUTER
REFERENCES:([F].[ContactID], [Expr1017]) WITH UNORDERED PREFETCH)
| | | |
--Nested Loops(Inner Join, OUTER
REFERENCES:([G].[AddressID], [Expr1016]) WITH UNORDERED PREFETCH)
| | | | |
--Nested Loops(Inner Join, OUTER
REFERENCES:([F].[CustomerID], [Expr1015]) WITH UNORDERED PREFETCH)
| | | | | |
--Compute
Scalar(DEFINE:([C].[AccountNumber]=[AdventureWorks].[Sales].[Customer].[Account
Number] as [C].[AccountNumber]))
| | | | | | |
--Nested Loops(Inner Join, OUTER
REFERENCES:([F].[CustomerID], [Expr1014]) WITH UNORDERED PREFETCH)
| | | | | | |
--Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[Individual].[PK_Individual_CustomerID]
AS [F]))
Search WWH ::
Custom Search