Microsoft® SQL Server 2008 R2 Unleashed

Forcing Recompilation of Query Plans

In some situations, a stored procedure might generate different query plans, depending on the parameters passed in. At times, depending on the type of query and parameter values passed in, it can be difficult to predict the best query plan for all executions. Consider the following stored procedure:

Assume that a nonclustered index exists on the advance column in the titles table. A search in which advance is between 1,000 and 2,000 might be highly selective, and the index statistics might indicate that fewer than 5% of the rows fall within that range, and thus an index would be the best way to find the rows. ...