or
Erik Darling
sql-server
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
Top Answer
Erik Darling
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

~~~
Why does a plan with FULL optimization show simple parameterization?

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.