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

Hash Match [IDENTICAL]
|--Hash Match [IDENTICAL]
| |--Hash Match [IDENTICAL]
| (1)| |--Merge Join(Inner Join,
| | | MERGE:([pr].[StateProvinceID])=([pa].[StateProvinceID]),
| | | RESIDUAL:([Person].[Address].
| | | |--Nested Loops [IDENTICAL]
| | | | |--Clustered Index Seek [IDENTICAL]
| | | | |--Clustered Index Scan [IDENTICAL] (EXCEPT WITH 'US')
| (2)| | |--Index Scan(OBJECT:([IX_Address_StateProvinceID] AS [pa]),
| | | | ORDERED FORWARD)
| | |--Index Scan [IDENTICAL]
| |--Index Scan [IDENTICAL]
|--Index Scan [IDENTICAL]

This plan is optimized for a much larger row set. In fact, there are 61,395 rows being returned with the
US parameter. You can see that the plan is optimized for this type of volume with the use of the Merge
Join operator instead of the Nested Loop in the difference labeled (1). The other difference labeled (2)
is a minor one, but is a bookmark lookup. This plan should have been run the first time the procedure
was run, but was not because the procedure was run and cached the first time with the Namibian NA
parameter. Table 9-32 shows the differences in performance.

Table 9-32: Demonstration of Plan Caching Issues

Parameter Cached as 'NA' Cached as 'US'

CPU I/O Duration CPU I/O Duration

'NA' 111 379 204 180 408 210

'US' 280 605 2807 290 406 2222

This is why you have to jump in for the optimizer. Since you've established that the US parameter-
ized version of this procedure is the heavy percentage use case, you'll need to ensure that the plan for
this parameter is the one that is optimized. To do this, just add the following hint to the bottom of the
procedure and rerun the DDL statement to create the procedure.

OPTION ( OPTIMIZE FOR (@COUNTRYREGIONCODE = 'US') );

Now if you flush the procedure cache and rerun the procedure with the NA parameter you'll get
the plan for the US parameter. This OPTIMIZE FOR statement communicates directly to the
optimizer that it should create a plan with the expectation that the parameter will be US. This plan
will not be optimized for the NA or any other parameter, but here you are making the choice to apply
your knowledge of the usage of this procedure to give the optimizer better information about how this
statement should be executed.

If you are not having problems with plan caching, you may at some point experience issues with dead-
locking or blocking by other processes or bad queries. We'll dig into these issues in the next section.