Posts Tagged ‘Plan not changing after stats change.’

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.

–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.