Parameter sniffing.

Basically, SQL Server has three ways to deal with constant values in a query:

a) If it's a constant (e.g. WHERE Age = 35), that value is used to optimize. SQL Server will find a plan that is most efficient for that value.

b) If it's a variable (e.g. WHERE Age = @Age), the run-time value is not yet knwon when the query is optimized. Statistical information about the average spread of values in the Age column is used to optimze. The plan SQL Server creates will work good for most values, but might not be optimal for atypical values. If, for instance, a large majority of rows will have an age over 65 and only a small minority will have a younger age (as would be the case in a database of dementia patients), the plan will be optimal when @Age is 80, but less so when @Age is 43.

c) If it's a parameter in a procedure (e.g. CREATE PROC Something @Age int AS .... WHERE Age = @Age), then SQL Server will use ("sniff") the value passed to the parameter when the plan gets created, and find a plan that is optimal for that value. Since the plan goes into cache for future reuse, subsequent executions of the procedure will use the same plan, even if they are called with a different parameter value. In the above case, if the first execution of the procedure happens to use @Age = 43, then it is possible that all future executions with a higher value for @Age are very slow, because SQL Server picked a plan that is optimal for values of @Age where only a small minority of rows match.

In practice, parameter sniffing does more good than it does harm. But in cases where you find it does harm, you can use the RECOMPILE hint to force SQL Server to receompile the plan every time the stored procedure is executed. The benefit is that each execution uses a plan that is optimal for the @Age value passed in (since SQL Server will still sniff the parameter); the down side is that SQL Server has to spend a lot of resources for all those recompilations.