Inside the SQL Server Query Optimizer - part 4 Trivial Plan

Welcome back Guys!

Today, after going into the details of the cardinality estimate in a few posts ago, we will continue with the next step taken by the optimizer.
So here the fourth part of the series Inside the SQL Server Query Optimizer.

We will talk about Trivial plan.
As usual I will try to explain myself in the simplest way possible.
But tell me what doesn't seem clear to you 
If you lost the previous parts you can click here:

• Inside the SQL Server Query Optimizer - part 1 Introduction and the input tree
• Inside the SQL Server Query Optimizer - part 2 All about the Simplification
• Inside the SQL Server Query Optimizer - part 3 Cardinality estimation 

Enjoy the reading!

What is a Trivial plan?

A trivial plan is a mode to avoid the cost of the full “cost based optimization”.
SQL Server Optimizer use a trivial plan when detect a Query with a simple structure

For example:

SELECT ID FROM ORDTES WHERE ID = 1

You can see if a trivial plan is used looking to the execution plan and then clicking on the SELECT statement.  A property “optimization level” is present.

When a trivial plan is used?

"A trivial plan" is used in few cases so it's easier see when it is not used.

Trivial plan is not used as soon as you use inequalities.
While this query generates a trivial plan:

       
Select *
From ordtes t
Where numdoc  < 2               
 

These two below lead to a not trivial plan

       
Select *
From ordtes t
Where numdoc  < 2 or numdoc > 2

Select *
From ordtes t
Where numdoc <> 2
 

If a plan is not trivial you could read FULL

Trivial plan is not used as soon as you use JOIN (LEFT or INNER) , APPLY (cross or outer) and Sub-Queries

       
Select * from ordtes t
join OrdRig r on r.idordtes = t.id
Where numdoc = 1

Select * from ordtes t
cross Apply (select r.idordtes from OrdRig r where r.idordtes = t.id) as r
Where numdoc =1

Select (select top 1 r.idordtes from OrdRig r where r.idordtes = t.id) from ordtes t
Where numdoc =1

And also a curiosity: using SUM function lead to TRIVIAL plan while using MAX function lead to a non trivial plan.

       
Select MAX(numdoc) 
From ordtes t
Where t.anndoc = 2018
      

 

We talked about the PRO but there also some CONS?

Yes, there is an important CONS that you should keep in your mind!

When the optimizer choose to use trivial plan in order to avoid the cost of the full optimization of the Query, it doesn't care about any constraint.

It is a problem?
Well yes is some cases could  be a problem!
Look to this example.

Suppose we have our ordtes table.
Define now a constraint anno_valido which dictates that the values of the field anndoc must be between 1980 and 2099:

ALTER TABLE ordtes ADD CONSTRAINT anno_valido CHECK ( anndoc > 1980 AND anndoc <= 2099 );
       

 

If now we execute the query below that have a trivial plan you can see the optimizer use an index scan to extract data.

In reality it would not be necessary to read the table because the constraint assures us that the maximum year is 2099.

       
Select numdoc 
From ordtes
Where anndoc = 2100
 

 
Instead executing the same query with the trace flag 8757 that disable the use of the trivial plan we can see from the execution plan that the OrdTes table is correctly not read (constant scan)

Select numdoc 
From ordtes
Where anndoc = 2100
Option (querytraceon 8757)

 

Finally, as a note, if you want to see what queries you have inside the execution plan that use a trivial plan you can execute the T-SQL command below: 

;WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  st.text,
        qp.query_plan,
        qs.*
FROM   
(
    SELECT top 50 *
    FROM    sys.dm_exec_query_stats
    ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE
  query_plan.exist('//p:StmtSimple[@StatementOptmLevel[.="TRIVIAL"]]/p:QueryPlan/p:ParameterList') = 1

 

Ok that's all for today.
Now you know all about trivial plans.
So in the next post we will see many interesting things about optimiziation techniques so don't miss the next posts!

Luca Biondi @ SQLServerPerformance blog!

 


 

 

 

 

 

 

 

Previous post: SQL Server, Sub-Queries vs. Cross Apply

A song with the mood of the day: Stop the rock