10

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?

NUTS

0

1 Answer 1

9

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
1
  • Just making sure: is "Query marked as Cachable" in the output sufficient to indicate simple parameterisation or should I also see the second line? I have a scenario as in original question, but the query seems not simple parameterised (from looking at plan cache), but (again) in the plan there is a warning for Unmatched Indexes as if the query was parameterised indeed. Confusing. Nov 24, 2022 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.