How to Disable Parameter Sniffing in SQL Server 2005

I have seen several questions in the MSDN SQL Server Database Engine Forum recently about problems people are having with statistics not staying up-to-date in some cases in SQL Server 2005. This can also manifest itself as problems with queries suddenly changing their performance characteristics dramatically.

One reason that this can happen is out of date statistics, but another reason is something called parameter sniffing. Parameter sniffing is normally a good thing, since it allows the query optimizer to use the actual value of a passed in parameter to a stored procedure to come up with an optimal execution plan. When the SP is compiled, the query optimizer uses that value to come up with a execution plan, and then that execution plan goes in the procedure cache.

If that parameter is an atypical value, that causes the query to return many more rows than normal (causing an index scan instead of an index seek, for example), then you will be stuck with that bad plan until the statistics are updated for the table(s) used by that query.

One way around this is to "disable" parameter sniffing, by declaring a local variable inside the SP, and then setting that local variable to the value of the parameter. Then you use the local variable in the query instead of the parameter. This prevents SQL from sniffing the parameter.

 

-- How to disable parameter sniffing
CREATE PROCEDURE [dbo].[GetNewsForFeedExample]
(
    @FeedID int,
)
AS
    SET NOCOUNT ON;
    
    -- Declare local variable, using same datatype as passed in parameter.
    DECLARE @FeedID1 int
    SET @FeedID1 = @FeedID  -- Assign parameter value to local variable

    -- Use local variable in query. Now SQL Server cannot "sniff" the parameter
    SELECT c1, c2, c3
    FROM dbo.FeedTable
    WHERE FeedID = @FeedID1
 
-- Another technique
CREATE PROCEDURE [dbo].[GetNewsForFeedExample]
(
    @FeedID int,
)
AS
    SET NOCOUNT ON;

    -- Use OPTIMIZE FOR hint
    SELECT c1, c2, c3
    FROM dbo.FeedTable
    WHERE FeedID = @FeedID
    OPTION(OPTIMIZE FOR (@FeedID = 250))

 

This entry was posted in SQL Server 2005. Bookmark the permalink.

7 Responses to How to Disable Parameter Sniffing in SQL Server 2005

  1. Unknown says:

    sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold sell wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold wow gold sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key sell wow cd key trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow gold trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade wow trade

  2. Unknown says:

    maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maple story mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maplestory mesos maple story gold maple story gold maple story gold maple story gold maple story gold maple story gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maplestory gold maple story money maple story money maple story money maple story money maple story money maple story money maplestory money maplestory money maplestory money maplestory money maplestory money maplestory money buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maple story mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos buy maplestory mesos maple story maple story maple story maple story maple story maple story maplestory maplestory maplestory maplestory maplestory maplestory sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell maple story mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos sell ms mesos maple story shop maple story shop maple story shop maple story shop maple story shop maple story shop cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos cheap msmesos

  3. Unknown says:

    sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell Final Fantasy XI gils sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil sell FFXI Gil Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade Final Fantasy XI gil trade sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold sell lotro gold lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade lotro gold trade sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us sell lotro gold us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us the lord of the rings online us sell lotro gold eu sell lotro gold eu sell lotro gold eu sell lotro gold eu sell lotro gold eu sell lotro gold eu sell lotro gold eu sell lotro gold eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu the lord of the rings online eu

  4. Unknown says:

    lotro lotro lotro lotro lotro lotro lotro gold lotro gold lotro gold lotro gold lotro gold lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold buy lotro gold lotr lotr lotr lotr lotr lotr lotr gold lotr gold lotr gold lotr gold lotr gold lotr gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold lord of the rings online gold

  5. Unknown says:

    http://www.flooring-China.com/China-access-floor.htm China raised floorhttp://www.flooring-China.com/China-bamboo-flooring.htm China bamboo flooring pricehttp://www.flooring-China.com/raised-access-floor.htm raised access floorhttp://www.flooring-China.com raised floor, China bamboo flooring

  6. Unknown says:

    There is raised floor in John\’s office, but he likes China bamboo flooring. John\’s friend, Tom, saw access floor at some store. He thinks this type of floor is suitable to be installed in his computer room.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s