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