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.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @TerritoryId
END

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.

CREATE PROCEDURE TerritorySales
@TerritoryId int
AS
BEGIN
DECLARE @i_TerritoryId int

SET @i_TerritoryId = @TerritoryId

SELECT sale
FROM dbo.SaleDetails
WHERE TerritoryId = @i_TerritoryId
END

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

I hope it helps.

Praveen

Advertisements

About saxenapraveen

I work with Microsoft and have 12 years of experience in developing,designing and leading 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:

WordPress.com Logo

You are commenting using your WordPress.com 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: