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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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?
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