Learn T-SQL Querying

Parameter sniffing

Given that query plan reuse is so important, why wouldn't SQL Server parameterize every query that comes in by default? One of the reasons is to avoid query performance issues that may result from parameter sniffing. Parameter sniffing is something SQL Server does in order to optimize a parameterized query. The first time a stored procedure or other parameterized query executes, the input parameter values are used to drive the optimization process and produce the execution plan, as discussed in the Query optimization essentials section.

That execution plan will then be cached and reused by subsequent executions of the procedure or query. For most queries, this is a good thing because using a specific value will result in ...