Hi,
I assumed that whenever we run update statistics on the database tables it would invalidate all the plans in cache involving those tables and SQL Server would generate a new execution plan for queries involving those tables but this is not a behavior we
are getting on test system.
Let me know if this assumption is not true and we need to clear cache after running update statistics to make sure SQL Server generates optimal plan with new statistics.
Following is the sample code I tried on SQL Server 2005sp3:
drop table test_stats
create table test_stats(id int not null primary key, id1 int, id2 int, id3 char(1000))
create index idx_dd1 on test_stats(id1)
--The following statement would generate execution plan with full scan on test_stats
select * from test_stats where id1 = 100
set nocount on
declare @i int
set @i = 1
while (@i < 10000)
begin
insert into test_stats values(@i,@i,@i,@i)
set @i = @i+1
end
update statistics test_stats with fullscan
--The following statement still generates execution plan with full scan on test_stats even after updating statistics
select * from test_stats where id1 = 100
dbcc freeproccache
--The following statement generates execution plan with index seek after clearing cache
select * from test_st
View Complete Post