MSSQLWIKI

Karthick P.K on SQL Server

Posts Tagged ‘SQL Server trival plan’

Trivial Plan

Posted by Karthick P.K on May 29, 2009

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

Posted in Performance | Tagged: , , | 15 Comments »

 
%d bloggers like this: