SQL Server parameter sniffing

While working on a project which has huge database interaction and i was using stored procedure to get good performance. But even though i was using stored procedure the performance was not so good all the time. I mean it was not consistent, for few queries on same stored procedure it was so good and some times it was going worst.

I have done lots of google for it and finally i got the solution.. The easiest solution which you can apply to any existing stored procedure without doing any major changes in it..

Its mostly helpful in situation where you have a stored procedure and the amount of data returned is varying wildly depending on what parameters are passed in.

Some time you will see that the performance of stored procedure change dramatically depending on how much data is being returned.

Then you could be a victim of SQL Server Parameter sniffing.
[YOU ARE AT RIGHT PLACE TO GET SOLUTION]


Normally we writes procedure like:

CREATE PROCEDURE GetUserData
   @UserName nvarchar(20)
    AS
    BEGIN
        SELECT DisplayName, FirstName, LastName 
        FROM dbo.User
        WHERE UserName = @UserName
    END

Now to get good speed constantly, just do some minor changes in it:

CREATE PROCEDURE GetUserData
        @UserName nvarchar(20)
    AS
    BEGIN
        DECLARE @myUserName nvarchar(20)
         SET @myUserName = @UserName
     
        SELECT DisplayName, FirstName, LastName 
        FROM dbo.User
       WHERE UserName = @myUserName
   END

I have done some minor changes in it.
that is, i have just declared local variables and assigned the values of parameters in it and then used those variables in queries instead of using parameters directly.

Now You must be thinking that how come it affect the performance.
Let me explain:

Here is the Microsoft definition:

“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.


So For some strange reason, when you pass the parameters (@UserName in this case) to a local variable (@myUserName) SQL Server will no longer use the value of the parameter (@UserName) to influence the query plan and you will get the performance constantly. :)

Compiled By: Rajesh Rolen