Database Reference
In-Depth Information
Similar to parameter sniffing issues, you can address this problem with statement-level recompilation. Listing 26-12
shows a new version of stored procedure.
Listing 26-12.
Plan Reuse: Stored procedure with statement-level recompile
alter 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))
option (recompile)
end
If you call the stored procedures with the code from Listing 26-11, you would obtain the execution plans shown
in Figure
26-8
. As you can see, SQL Server recompiles the query on every call, and therefore it can choose the most
beneficial execution plan for every parameter set. It is worth mentioning that the plans are not cached in cases where
statement-level recompile is used.
