SQL Server Optimizer and Trivial Plan

The first phase of  the optimization is called the Trivial Plan. If the SQL Optimizer decides the submitted query is simple , it will generate a single Trivial Plan.  The Optimizer will not spend additional time in selecting the Optimal Plan.

This code displays the occurrence of the Trivial plan. The first select statement displays the counter ‘trivial plan’ and the occurrence from the sys.dm_exec_query_optimizer_info dmv

 The second executes a simple query, and the third displays the counter and one additional occurrence.

SELECT * FROM sys.dm_exec_query_optimizer_info 
WHERE counter='trivial plan'
GO
use master
GO
select * from spt_monitor
GO
SELECT * FROM sys.dm_exec_query_optimizer_info 
WHERE counter='trivial plan'

Read Also

SQL Server – AUTO CREATE STATISTICS

Filtered statistics, T-SQL best practises for T-SQL Tuesday