Plan Caching - Pro SQL Server Internals - page 483

As you can see in Figure 26-3 , SQL Server recompiles the statement on every call, and it generates the most
efficient execution plan for every parameter value.
Figure 26-3. Parameter Sniffing: Statement-level recompile
The statement-level recompile may be a good option when the queries do not execute very often or in the case of
complex queries, when the compilation time is just a fraction of the total execution time. However, it is hardly the best
approach for OLTP queries, which are constantly running in the system due to the extra CPU load that recompilation
introduces.
Another option is using an OPTIMIZE FOR hint, which forces SQL Server to optimize a query for the specific
parameter values provided in the hint. Listing 26-6 illustrates such an approach.
Listing 26-6. Parameter Sniffing: OPTIMIZE FOR hint
alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @Country
option (optimize for(@Country='USA'))
end
go
exec dbo.GetAverageSalary @Country='Canada';
exec dbo.GetAverageSalary @Country='USA';
As you can see in Figure 26-4 , SQL Server ignores the parameter value during the compilation and optimizes the
query for the @Country='USA' value.