Sql-server – Why does a plan with FULL optimization show simple parameterization

execution-plansimple-parameterizationsql servertrivial-plan

I read that only Trivial Plans can be Simple Parameterized, and that not all queries (even when the plan is Trivial) can be Simple Parameterized.

Then why is this plan showing Full Optimization, and Simple Parameterization at the same time?

NUTS

Best Answer

Simple Parameterization is attempted when a trivial plan is found. The parameterization attempt may be considered safe or unsafe.

The key point is that a trivial plan is found and considered safe. If the cost of the trivial plan exceeds the cost threshold for parallelism, the optimizer will go on to later stages of optimization, where parallel plans may be considered. Whether the final result is a serial or parallel plan, it will be simple parameterized if the safe trivial plan found (but not ultimately used) was parameterized.

In the question example, setting the cost threshold for parallelism higher than the cost of the trivial plan will allow the optimizer to stop at that stage.

Looking at the query plan isn't always enough to figure out if your query has actually been Simple Parameterized.

The safest way is to check some DMVs to verify:

/*Unsafe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Unsafe Auto-Params/sec%';

/*Safe auto param*/
SELECT *
FROM sys.dm_os_performance_counters AS dopc
WHERE dopc.counter_name LIKE '%Safe Auto-Params/sec%';

/*Trivial Plans*/
SELECT * 
FROM sys.dm_exec_query_optimizer_info AS deqoi 
WHERE deqoi.counter = 'trivial plan';

Additionally, you can also use undocumented trace flag 8607, but not as an OPTION clause hint. Using the OPTION clause prevents a trivial plan.

DBCC TRACEON(8607, 3604);
/*Wait*/    

/*Run*/     
SELECT u.CreationDate, u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 2;

/*Clean up*/
DBCC TRACEOFF(8607, 3604);

If the plan is considered safe for Simple Parameterization, you'll see a message confirming it here.

********************

** Query marked as Cachable

** Query marked as Safe for Auto-Param
Related Question