Database Reference
In-Depth Information
select
Num,
convert(varchar(5),Num),
'USA Employee: ' + convert(varchar(5),Num),
40000,
'USA'
from Nums;
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N3)
insert into dbo.Employees(ID, Number, Name, Salary, Country)
select
65536 + Num,
convert(varchar(5),65536 + Num),
'Canada Employee: ' + convert(varchar(5),Num),
40000,
'Canada'
from Nums;
create nonclustered index IDX_Employees_Country
on dbo.Employees(Country);
As a next step, let's create a stored procedure that calculates the average salary for employees in a specific
country. The code to do this is shown in Listing 26-2. Even though we are using a stored procedure in this example,
the same situation could happen with parameterized queries called from client applications.
Listing 26-2.
Parameter Sniffing: Stored Procedure
create proc dbo.GetAverageSalary @Country varchar(64)
as
begin
select Avg(Salary) as [Avg Salary]
from dbo.Employees
where Country = @Country
end
In this case, when the stored procedure is called with
@Country='USA'
, the optimal execution plan is a
Clustered
Index Scan
. However, for
@Country='Canada'
, the better execution plan is a
Nonclustered Index Seek
with
Key Lookup
operations.
Let's call the stored procedure twice: the first time with
@Country='USA'
and the second time with
@Country='Canada'
, as shown in Listing 26-3.
Listing 26-3.
Parameter Sniffing: Calling a stored procedure
exec dbo.GetAverageSalary @Country='USA';
exec dbo.GetAverageSalary @Country='Canada';
As you can see in Figure
26-1
, SQL Server compiles the stored procedure and caches the plan with the first call
and reuses it later. Even though such a plan is less efficient with the
@Country='Canada'
parameter value, it may be
acceptable when those calls are rare, which is expected with such a data distribution.
