Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.

and thus some executions will result in more time and some run quickly.

Easiest Approach to solve this is to use Temporary variable to store the parameters before using them in where Clause. For Example the above Stored Procedure Re-Written as below:


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
Declare @Category1 nvarchar(60)
set @Category1 = @Category
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category1 +'%' or @Category1 is null)


2nd Way of dealing this situation is using Recompile query hint.
This way is more helpfull if SP is used very less and above method does not work.