SQL Parameter Sniffing and what to do about it

May 17th, 2008

Here was an interesting problem we ran into yesterday.  We have about 50 people using a web site that I help build.  All but one of them was logging in with no problems.  But any time our “special” user logged in (lets call him John Doe) he would get a timeout.

That one left us scratching our heads.  Luckily we have top notch security in place, so we were able to log in as him as well (without him knowing, and without knowing his password).

Hint: use forms authentication and write your own membership provider on a mirrored production site that no one outside of IT knows about.

When we log in as him we also get a timeout.  OK, time to fire up SQL Server Query Profiler and see what is going on…which doesn’t take long.  We see the query, a stored procedure really, taking +30 seconds to run.  When we log in as a different user it takes less than one second.  In fact we can log in as any other user and it takes less than one second.

Next step, dissect the query.  We pull the query out of the stored proc (it isn’t that complicated of a query) and run it from Management Studio with John Doe’s info.  ~1 second.

WHAT??!!!

My coworker did some web searching and stumbled on the answer (Google to the rescue, giving us answers even when we really don’t know the question): SQL Parameter Sniffing.

WHAT??!!!

The rundown is that when SQL Server compiles the query and creates the execution plan, if you pass parameters from the stored procedure call directly into the query, SQL Server will use the values passed in to “influence” the query plan.  That can be a bad thing.  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.

Luckily the fix is easy.  Take your stored procedure that looks like this (generalizing a query here:

   1: CREATE PROCEDURE MyProcedure
   2:     @UserName nvarchar(20)
   3: AS
   4: BEGIN
   5:     -- Insert statements for procedure here
   6:     SELECT DisplayName, FirstName, LastName 
   7:     FROM dbo.User
   8:     WHERE UserName = @UserName
   9: END

And change it to this:

   1: CREATE PROCEDURE MyProcedure
   2:     @UserName nvarchar(20)
   3: AS
   4: BEGIN
   5:     DECLARE @myUserName nvarchar(20)
   6:     SET @myUserName = @UserName
   7:     -- Insert statements for procedure here
   8:     SELECT DisplayName, FirstName, LastName 
   9:     FROM dbo.User
  10:     WHERE UserName = @myUserName
  11: END

Look on lines 5,6, and 10 for changes.

What did I really do?  I once again proved that any programming problem can be solved with another layer of indirection.  Really. 

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.

If you don’t believe me (and really, you shouldn’t ever believe the word of just one person), do a Google search on SQL Parameter Sniffing

We tried it, tested it, did a performance check on it, it worked.  But this now goes onto the top of my “Strange fixes for SQL Server” stack.

Other references: