Home » Plan Cache

Plan Cache

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 … Read the rest