SQL Parameter sniffing

​SQL Parameter sniffing

For last few days we were having a problem with a few stored procedures that stop functioning for some set of values using SQL Server 2012. The stored procedure work correctly with one set of parameters, while with another set would timeout and fail.

The initial way that we bypassed this problem was that when this circumstance was found, we would alter the stored procedure and this seemed to fix it. As you can imagine it wasn't an ideal situation for an enterprise application because the problem would need to be found and reported before we would even know it existed but we struggled to find any proposed solutions to the problem.

After much investigation it was suggested that this could be caused by parameter sniffing. SQL Server does this by tracking what happens to the parameters passed into the stored procedure when creating the stored procedure and working out the execution plan. So the execution plan generated by SQL server was good most of the time but the plan would contain situations where the procedure would fail.

Below is a stored procedure that allows parameter sniffing. This is not one of the stored procedures that did have the problem, but a stored procedure where parameter sniffing occurs:

ALTER PROCEDURE [dbo].[ReportJobSeekerDetails]

@MarketPlaceID BIGINT,

@RoleName NVARCHAR(20),

@CountryId SMALLINT = NULL,

@StateId BIGINT = NULL,

@UserStatus TINYINT = NULL,

@RegStartDate DATETIME = NULL,

@RegEndDate DATETIME = NULL,

@StartRowNumber INT,

@PageSize INT,

@SortExpression NVARCHAR(100)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

SET NOCOUNT OFF;

IF @PageSize > 100

SET @PageSize = 100

SELECT COUNT(*) 'RowCount'

FROM

aspnet_users au

INNER JOIN aspnet_membership am ON au.userID = am.userid

INNER JOIN aspnet_usersinroles ur ON ur.userID = au.UserId

INNER JOIN UserPublicProfile upp ON upp.UserID = ur.ID

LEFT JOIN Country c ON upp.CountryID = c.Id

LEFT JOIN State s ON upp.StateID = s.ID

WHERE (@CountryId IS NULL OR c.Id = @CountryId)

AND (@StateId IS NULL OR upp.StateId = @StateId)

AND (@UserStatus IS NULL OR am.IsLockedOut = @UserStatus)

AND (@RegStartDate IS NULL OR am.CreateDate > @RegStartDate)

AND (@RegEndDate IS NULL OR am.CreateDate < @RegEndDate)

END

With the following addition of variables it prevents SQL Server from having the ability to perform parameter sniffing:

ALTER PROCEDURE [dbo].[ReportJobSeekerDetails]

@MarketPlaceID BIGINT,

@RoleName NVARCHAR(20),

@CountryId SMALLINT = NULL,

@StateId BIGINT = NULL,

@UserStatus TINYINT = NULL,

@RegStartDate DATETIME = NULL,

@RegEndDate DATETIME = NULL,

@StartRowNumber INT,

@PageSize INT,

@SortExpression NVARCHAR(100)= NULL,

@TotalCount INT OUTPUT

AS

BEGIN

DECLARE @CountryIdLocal SMALLINT = @CountryId

DECLARE @StateIdLocal BIGINT = @StateId

DECLARE @UserStatusLocal TINYINT = @UserStatus

DECLARE @RegStartDateLocal DATETIME = @RegStartDate

DECLARE @RegEndDateLocal DATETIME = @RegEndDate

SELECT @TotalCount = COUNT(au.UserName)

FROM dbo.aspnet_users au

INNER JOIN dbo.aspnet_membership am ON au.userID = am.userid

INNER JOIN dbo.aspnet_usersinroles ur ON ur.userID = au.UserId

INNER JOIN UserPublicProfile upp ON upp.UserID = ur.ID

LEFT JOIN Country c ON upp.CountryID = c.Id

LEFT JOIN State s ON upp.StateID = s.ID

WHERE (@CountryIdLocal IS NULL OR c.Id = @CountryIdLocal)

AND (@StateIdLocal IS NULL OR upp.StateId = @StateIdLocal)

AND (@UserStatusLocal IS NULL OR am.IsLockedOut = @UserStatusLocal)

AND (@RegStartDateLocal IS NULL OR am.CreateDate > @RegStartDateLocal)

AND (@RegEndDateLocal IS NULL OR am.CreateDate < @RegEndDateLocal)

END

By implementing the small change demonstrated above on a couple of troublesome stored procedures, this fixed our problem with our stored procedures randomly not working with certain parameters. This proved that the problem was caused by parameter sniffing and left me wondering how many other people are out there with the same problem.

Happy Coding!!