Successful Deployment Strategies - Professional SQL Server 2005 Performance Tuning - page 535

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
The Query Plan
The following query plan is generated from the query in the previous section. The plan is doing a large
number of scans as it retrieves all the rows from the joined tables. However, the goal is to return the first
40 rows as soon as possible but without changing the code. This is where plan guides come in to the
picture.
| --Hash Match(Inner Join, HASH:([C].[CustomerID])=([G].[CustomerID]))
| --Compute
Scalar(DEFINE:([C].[AccountNumber]=[AdventureWorks].[Sales].[Customer].
[AccountNumber] as [C].[AccountNumber]))
| | --Compute
Scalar(DEFINE:([C].[AccountNumber]=isnull('AW'+[AdventureWorks].[dbo].
[ufnLeadingZeros]([AdventureWorks].[Sales].[Customer].[CustomerID] as [C].
[CustomerID]),")))
| | --Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[Customer].[PK_Customer_CustomerID] AS [C]))
| --Hash Match(Inner Join,
HASH:([E].[AddressID])=([G].[AddressID]))
| --Index
Scan(OBJECT:([AdventureWorks].[Person].[Address].[IX_Address_AddressLine1_Address
Line2_City_StateProvinceID_PostalCode] AS [E]))
| --Hash Match(Inner Join,
HASH:([G].[CustomerID])=([F].[CustomerID]))
| --Index
Scan(OBJECT:([AdventureWorks].[Sales].[CustomerAddress].[AK_CustomerAddress_rowguid]
AS [G]))
| --Hash Match(Inner Join,
HASH:([D].[ContactID])=([F].[ContactID]))
| --Clustered Index
Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID] AS [D]))
| --Hash Match(Inner Join,
HASH:([F].[CustomerID])=([A].[CustomerID]))
| --Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[Individual].[PK_Individual_CustomerID]
AS [F]))
| --Merge Join(Inner Join,
MERGE:([A].[SalesOrderID])=([B].[SalesOrderID]), RESIDUAL:([AdventureWorks].[Sales].
[SalesOrderHeader].[SalesOrderID] as [A].[SalesOrderID]=[AdventureWorks].[Sales].
[SalesOrderDetail].[SalesOrderID] as [
| --Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_Sales
OrderID] AS [A]), ORDERED FORWARD)
| --Clustered Index
Scan(OBJECT:([AdventureWorks].[Sales].[SalesOrderDetail].[PK_SalesOrderDetail_Sales
OrderID_SalesOrderDetailID] AS [B]), ORDERED FORWARD)