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))