Yes!!! Error is because of 'Parameter Sniffing'.

Recently, I have faced an issue while developing an application using MS SQL 2008 and ASP.NET. I have created a SP which is running fine in from SQL Server Management Studio. But, When I execute it thru the asp.net application, it is timing out. i tried debugging and doing R&D. but in vein. later on i found the reason in the web saying parameter sniffing. Parameter sniffing? Oh yes. this is new to me. what is this by the way?

According to the web about, Parameter sniffing is

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

To explain this programmatically,

CREATE PROCEDURE dbo.GetAssessmentByResource
    @ResourceName VARCHAR(100) 
As
SELECT * FROM Resource WHERE Name LIKE @ResourceName--assuming that 100000+ records

Here, though the output is very few records, because of the full-text scan. this search would be horrible even if it is a compiled one.

I found below simple solution thru web to move away from Parameter Sniffing. this can be solved by temp or dummy variables. instead of access the parameter variables inside the SP. you can get a copy of variable that is used inside the SP especially in where clauses. Example

CREATE PROCEDURE dbo.GetAssessmentByResource
    @ResourceName VARCHAR(100)
As

DECLARE @ResourceNameTemp VARCHAR(100)
SET @ResourceNameTemp = @ResourceName 
SELECT * FROM Resource WHERE Name LIKE @ResourceNameTemp--assuming that 100000+ records

Here, @ResourceNameTemp is temp variable, Hence SP execute faster than before.