Plan Caching - Pro SQL Server Internals - page 482

Figure 26-2. Parameter Sniffing: Cached plan for @Country='Canada'

There are a few ways to address the issue. You can force the recompilation of either stored procedure using
EXECUTE WITH RECOMPILE or a statement-level recompile with OPTION (RECOMPILE) clauses. Obviously, a
statement-level recompile is better because it performs the recompilation on a smaller scope. SQL Server sniffs the
parameter values at the time of the recompilation, generating the optimal execution plan for each parameter value.

■ SQL Server correctly estimates cardinality (number of rows) in the table-variables referenced in the statement
when the statement-level recompile is used. Without the statement-level recompile, it always estimates that the
table-variables have just a single row.

Tip

Listing 26-5 shows the statement-level recompile approach.

Listing 26-5. Parameter Sniffing: Statement-level recompile
alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @Country
option (recompile)
end
go

exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';