Then why is this plan showing Full Optimization, and Simple Parameterization at the same time?
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