SQL Server Optimizer and Trivial Plan

09 August,2011 by Tom Collins

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


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server Optimizer and Trivial Plan


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer