I read that only [Trivial Plans can be Simple Parameterized][1], and that not all queries (even when the plan is Trivial) [can be Simple Parameterized][2]. Then why is [this plan][3] showing Full Optimization, and Simple Parameterization at the same time? [![NUTS][4]][4] [1]: https://blogs.msdn.microsoft.com/psssql/2013/12/04/how-simple-parameterization-works/ [2]: https://blogs.msdn.microsoft.com/sqlprogrammability/2007/01/11/4-0-query-parameterization/ [3]: https://www.brentozar.com/pastetheplan/?id=HkOMIb-CM [4]: https://i.stack.imgur.com/a6g5N.png
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 ~~~