What is Trivial Plan?
A trivial plan results when the query optimizer determines that given tables referenced in the query and the indexes existing on them, only one plan is possible.
Obviously, a recompilation would be futile in such a case. A query that has generated a trivial plan may not always generate a trivial plan, of course.
For example, new indexes might be created on the underlying tables, and so multiple access paths become available to the query optimizer. Additions of such indexes
would be detected and a correctness-related recompilation might replace the trivial plan with a non-trivial one.
SQL Server doesn’t recompile trivial plans if we have update stats on the underlying table and will not fire Auto updatestats for the query with Trivial Plan.
Let us see the below example query
create database AutoS
use autos
create table tab(col1 int)
declare @i int
set @i = 0
while @i < 1000
begin
insert into tab(col1) values (@i) –We are inserting 1000 Rows with value 1 to 999
set @i = @i + 1
end
set @i = 0
while @i < 1000
begin
insert into tab(col1) values (9999) –We are inserting 1000 Rows with value 9999
set @i = @i + 1
end
create statistics t_col1 on tab(col1) with fullscan
dbcc show_statistics (‘dbo.tab’,’t_col1′)
–So we have created a table with 2000 rows. 1000 of them have the values 9999 and 1000 of them have values 0 to 999.
–Create a Simple store Procedure
create procedure test @a int
as
begin
select * from tab where col1 = @a
end
set statistics profile on
SET STATISTICS XML ON
exec test 9999 — Check the XML Plan you will see it is trivial plan(StatementOptmLevel=”TRIVIAL”)
set statistics profile off
delete from tab –Now delete all the rows from the table
Execute the store Procedure again
exec test 9999
If Optimizer has not choosed the Trival Plan which is already cached, auto updatestats would have been fired for the table(Trace flag 8721 will dump information when
AutoStat has been run)and would have caused Recompile of SP.
Regards
Karthick P.K
MSSQLWIKI 