Plan Caching - Pro SQL Server Internals - page 485

Listing 26-8. Parameter Sniffing: OPTIMIZE FOR UNKNOWN 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 UNKNOWN))
end
go
exec dbo.GetAverageSalary @Country='Canada';
Figure 26-6 illustrates the execution plan. Germany is the most statistically common value in the table, and
therefore SQL Server generates an execution plan that is optimal for such a parameter value.
Figure 26-6. Parameter Sniffing: OPTIMIZE FOR UNKNOWN hint
You can achieve the same results with an OPTIMIZE FOR UNKNOWN hint by using local variables instead of
parameters. This method also works with SQL Server 2005, where the OPTIMIZE FOR UNKNOWN hint is not supported.
Listing 26-9 illustrates that approach. It introduces the same execution plan with a Clustered Index Scan , as shown in
Figure 26-6 .
Listing 26-9. Parameter Sniffing: Using local variables
alter proc dbo.GetAverageSalary @Country varchar(64)
as
begin
declare
@CountryTmp varchar(64)
set @CountryTmp = @Country
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @CountryTmp
end
You can troubleshoot issues introduced by parameter sniffing by analyzing cached plans with the
sys.dm_exec_query_stats view and the sys.dm_exec_query_plan function. We will discuss this in more detail,
including how to obtain execution plans for currently running statements, later in this and in the following chapter.