Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 310

To keep the noise down this procedure will just return the customerId. This is not an extremely useful
procedure, but it will illustrate the plan caching issue.
CREATE PROC usp_OptimizeFor_Test
(
@COUNTRYREGIONCODE NVARCHAR(3)
)
AS
Select h.CustomerId from Sales.SalesOrderHeader h
inner join Sales.SalesOrderDetail d
on h.SalesOrderId = d.SalesOrderId
inner join sales.CustomerAddress ca
on h.customerid = ca.customerid
inner join person.address pa
on ca.AddressId = pa.AddressId
inner join person.StateProvince pr
on pa.StateProvinceId = pr.StateProvinceId
inner join person.CountryRegion r
on pr.CountryRegionCode = r.CountryRegionCode
Where r.CountryRegionCode = @COUNTRYREGIONCODE
If you run the procedure using NA as the country region code parameter, you'll get three rows returned
and the plan uses a nested loop to Join the inner results of the personal address to the customer address
results because the statistics predict that this is a low number of rows.
Hash Match(Inner Join, HASH:([h].[SalesOrderID])=([d].[SalesOrderID]))
|--Hash Match(Inner Join, HASH:([ca].[CustomerID])=([h].[CustomerID]))
| |--Hash Match(Inner Join, HASH:([pa].[AddressID])=([ca].[AddressID]))
| | |--Nested Loops(Inner Join, OUTER REFERENCES:([pr].[StateProvinceID]))
| | | |--Nested Loops(Inner Join)
| | | | |--Clustered Index
| | | | | Seek(OBJECT:([PK_CountryRegion_CountryRegionCode] AS [r]),
| | | | | SEEK:([r].[CountryRegionCode]=N'NA') ORDERED FORWARD)
| | | | |--Index Scan
| (OBJECT:([AK_StateProvince_StateProvinceCode_CountryRegionCode] AS [pr]),
| | | | | WHERE:([CountryRegionCode]=N'NA'))
| | | |--Index Seek(OBJECT:([IX_Address_StateProvinceID] AS [pa]),
| | | | SEEK:([pa].[StateProvinceID]=[StateProvinceID]
| | |--Index Scan(OBJECT:([AK_CustomerAddress_rowguid] AS [ca]))
| |--Index Scan(OBJECT:([IX_SalesOrderHeader_CustomerID] AS [h]))
|--Index Scan(OBJECT:([IX_SalesOrderDetail_ProductID] AS [d]))
Now if you run the stored procedure again using US for the country code parameter, the stored pro-
cedure is cached, so the same plan is used — even when the row set is much higher. You can see that
this call is using the cached plan by noting the events in the profiler. To see for yourself look for the
SP:CacheHit event in the profiler results. Free the proc cache by running this statement:
DBCC FREEPROCCACHE --DON'T RUN IN PRODUCTION ENVIRONMENTS!!
Now run the stored procedure again using the parameter value of US. Now look at the execution plan.
We've removed the details on steps that are identical.