Parameter Sniffing & Stored Procedures Execution Plan

According to the white paper, Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 published in the Microsoft Site:

“Parameter sniffing” refers to a process whereby SQL Server’s execution environment “sniffs” the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word “current” refers to the parameter values present in the statement call that caused a compilation or a recompilation.

Soultion for Parameter Sniffing is to use:

WITH RECOMPILE   and Local Variables.

Example:

ALTER PROCEDURE spc_My_Demo_Procedure

/*
return the records based on four parameteres
*/

@sercode1 as nvarchar(10),
@Issue1 as nvarchar(10),
@Volume1 as nvarchar(10),
@SubYear1 as nvarchar(10)

WITH RECOMPILE

AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET FORCEPLAN OFF

declare @sercode nvarchar(10)
declare @Issue nvarchar(10)
declare @Volume nvarchar(10)
declare @SubYear nvarchar(10)

set @sercode = @sercode1
set @Issue = @Issue1
set @Volume = @Volume1
set @SubYear = @SubYear1

Links:

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html

http://technet.microsoft.com/en-gb/library/cc966425.aspx

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

http://furrukhbaig.wordpress.com/2007/08/22/parameter-sniffing/