Database Reference
In-Depth Information
Plan Reuse
Plans cached by SQL Server must be valid for any combination of parameters during future calls that reuse the plan.
In some cases, this can lead to situations where a cached plan is suboptimal for a specific set of parameter values.
One of the code patterns that often leads to such situations is the implementation of stored procedures that
search for data based on a set of optional parameters. The typical implementation of such a stored procedure is
shown in Listing 26-10. This code also creates two nonclustered indexes on the Employees table.
Listing 26-10. Plan Reuse: Creation of stored procedure and indexes
create proc dbo.SearchEmployee
(
@Number varchar(32) = null
,@Name varchar(100) = null
)
as
begin
select Id, Number, Name, Salary, Country
from dbo.Employees
where
((@Number is null) or (Number=@Number)) and
((@Name is null) or (Name=@Name))
end
go
create unique nonclustered index IDX_Employees_Number
on dbo.Employees(Number);
create nonclustered index IDX_Employees_Name
on dbo.Employees(Name);
A plan cached by SQL Server should work with any combination of input parameters, regardless of their values at
the time when the query was compiled. If you call stored procedures multiple times using the code from Listing 26-11,
SQL Server decides to generate a plan with an IDX_Employees_Number Index Scan and Key Lookup Operations.
Listing 26-11. Plan Reuse: Stored procedure calls
exec dbo.SearchEmployee @Number = '10000';
exec dbo.SearchEmployee @Name = 'Canada Employee: 1';
exec dbo.SearchEmployee @Number = '10000', @Name = 'Canada Employee: 1';
exec dbo.SearchEmployee @Number = NULL, @Name = NULL;
Figure 26-7 demonstrates Listing 26-11 execution plans for the stored procedure calls. As you can see, the query
does not use the IDX_Employees_Number Nonclustered Index Seek operation, even when the @Number parameter has a
NOT NULL value, because this plan would not be valid when @Number is NULL . When it is provided, SQL Server performs
a Key Lookup operation for every row in the table, which is highly inefficient.
 
Search WWH ::




Custom Search