Parameter sniffing in Sql server

Hi All,

I work for a project which involves very huge data reporting using SSRS. Business users reported a bug which was very difficult to analyze.
Whenever there was a new report added to report server and a certain user requests report data for smaller territory like New Jersey to see his sales figures for his territory. The report renders very quick. But whenever a user requests for a bigger territory like Texas it takes more time almost 10 times (phew!).

when i started looking into the issue and found something interesting which i have only read in my academic days.

SP was like this.

@TerritoryId int
FROM dbo.SaleDetails
WHERE TerritoryId = @TerritoryId

Even though all looks fine, there is a catch here. @TerritoryId is the input parameter which is used directly into the where condition. So here is what happens with Sql server.

1. developer executes the stored proc. Sql server compiles the SP and save it.
2. User calls the SP with certain parameters (say TerritoryId which is smaller). it sure will have less data.
3. Sql server creates an execution plan which is optimized for this input parameter (smaller territory) and saves it.
4. Next time a user call the same SP for a bigger territory (more data). Sql server still uses the same execution plan which was saved earlier (optimized for small data). It takes unusually more time.

Solution to this problem (called Parameter sniffing) is very simple.

1. Assign the input parameter to a local variable.
2. use that local variable in the sql query.

@TerritoryId int
DECLARE @i_TerritoryId int

SET @i_TerritoryId = @TerritoryId

FROM dbo.SaleDetails
WHERE TerritoryId = @i_TerritoryId

Even though it all looks one and the same thing, it surely make a difference in certain data intensive queries.

I hope it helps.



About saxenapraveen

I have 14 years of experience in enterprise software development,designing and leading team to provide reliable and scalable solutions for cloud and device ready businesses.

Posted on November 28, 2011, in Sql Server and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: