SQL Parameter Sniffing and what to do about it
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:
This “feature” is not unique to SQL Server. I’ve run into the problem in Oracle (it’s called bind variable peeking). In 11g there is an attempted fix: http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#sthref894
Good post .. I ran into this same issue last week .. But one thing that I do not understand here is why was this an issue only for this *special user*? So the question is when does parameter sniffing really affect performance?
OK.Good
Unbelievable. This one had my jaw hit the ground.
Thank you!