What is parameter sniffing and how can it affect execution plans?

Parameter sniffing is a process where SQL Server examines the values of parameters passed to stored procedures and uses those values along with the available statistics from an index or table to help determine an execution plan. When the statistics are up to date and an accurate representation of the types of data that will be passed to the query, parameter sniffing helps optimize queries better. Without parameter sniffing, average values from the statistics are used in place of specific values from the parameter. In some cases when parameter sniffing occurs, either the value of the parameter passed in, or the statistics themselves, is not representative of the values that will normally be passed. This results in a sub-optimal execution plan.

In general parameter sniffing should be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.