When Does SQL Server Auto‑Parameterize and How to Prevent Bad Execution Plans?

Database Parameterization Modes

SQL Server supports two parameterization modes: simple (the default) and forced . In simple mode, a query is re‑compiled only when the exact SQL text repeats; otherwise it is re‑compiled each execution unless the engine automatically parameterizes it.

When Does Automatic Parameterization Occur?

Under simple mode, SQL Server automatically parameterizes an ad‑hoc query when the query has a single, efficient execution plan (typically an index seek). This allows plan reuse, but the article highlights that automatic parameterization can sometimes cause unexpected behavior.

Problems Caused by Automatic Parameterization

Automatic parameterization can lead to plan reuse that is not optimal for all parameter values, resulting in issues such as inaccurate row‑count estimates and the classic parameter sniffing problem.

Example Demonstrating Automatic Parameterization

First, create a test table and populate it:

create table TestAutoParameter (
    id int not null,
    col2 varchar(50)
);
GO
declare @i int = 0;
while @i < 100000
begin
    insert into TestAutoParameter values (@i, NEWID());
    set @i = @i + 1;
end;
GO
create unique index idx_id on TestAutoParameter(id);
GO

Running a query such as select * from TestAutoParameter where id = 33333 is automatically parameterized because the unique index guarantees a single efficient plan (index seek). If the unique index is replaced with a non‑unique one, the same query is no longer automatically parameterized, illustrating the dependence on index uniqueness.

Issues with Automatic Parameterization

While automatic parameterization reduces recompilations, it can cause the parameter sniffing problem: the first compiled plan is reused for subsequent executions with different parameter values, leading to consistently wrong row‑count estimates.

How to Fix Incorrect Plan Reuse Caused by Automatic Parameterization

The solution is to prevent plan reuse for the problematic query. Adding the OPTION(RECOMPILE) hint forces SQL Server to re‑compile the query each time, ensuring accurate statistics:

select COUNT(1) from Test20160810 
where CreateDate > '2016-06-01' and CreateDate < ... 
OPTION(RECOMPILE);

This eliminates the stale plan and corrects the row‑count estimates.

Summary

The article uses a real‑world case to show how simple‑mode automatic parameterization works, the problems it can introduce, and a straightforward fix using OPTION(RECOMPILE). It emphasizes the importance of theoretical knowledge to diagnose such performance quirks.

Side Note

Practical issues often require a solid theoretical foundation; many problems are resolved by recalling relevant concepts from books or documentation.