Repost: Watch out for SQL Server Parameter Sniffing
June 24th, 2009
I posted on this about a year ago. I am reposting this as a Public Service Announcement (otherwise known as a PSA).
Do you have a stored procedure where the amount of data returned can vary wildly depending on what parameters are passed in? Does performance change dramatically depending on how much data is being returned? You could be a victim of SQL Server Parameter sniffing.
Luckily there is an easy fix that doesn’t involve medication…but you might have to change some of your code, just a little bit.
Take your standard stored procedure:
1: CREATE PROCEDURE GetUserData
2: @UserName nvarchar(20)
3: AS
4: BEGIN
5: SELECT DisplayName, FirstName, LastName
6: FROM dbo.User
7: WHERE UserName = @UserName
8: END
And make the following (highlighted) changes.
1: CREATE PROCEDURE GetUserData
2: @UserName nvarchar(20)
3: AS
4: BEGIN
5: DECLARE @myUserName nvarchar(20)
6: SET @myUserName = @UserName
7:
8: SELECT DisplayName, FirstName, LastName
9: FROM dbo.User
10: WHERE UserName = @myUserName
11: END
Also note: if you read through the links of my previous post, you will see this issue affects more than just SQL Server.


