sql-server add tag
meme (imported from SE)
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
meme (imported from SE)
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

~~~

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.