Guru's Guide to SQL Server Architecture and Internals, The

Parameter Sniffing

Prior to compiling an execution plan for a stored procedure, SQL Server attempts to “sniff” (i.e., discern) the values of the parameters being passed into it and use those values when compiling the plan. When these values are being used to filter a query (i.e., as part of a WHERE or HAVING predicate), this allows the optimizer to produce a more precise execution plan tailored to the values being passed into the stored procedure (using the statistics histograms for the columns they're used to filter) rather than based solely on the average density of the relevant table columns. Generally speaking, this is a good thing and results in improved performance over older versions of SQL Server.

You can get into trouble, however, when ...