Should update statistics force plan recompilation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Should update statistics force plan recompilation

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-10-28 : 17:05:38

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_stats where id1 = 100

Thanks

--rubs

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-28 : 17:33:57

update statistics will not cause recompile of exec plans.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-28 : 18:16:55

Update statistics will invalidate any plan that used those statistics. Next time any such plan is retrieved to execute a query it will be discarded and recompiled.

--
Gail Shaw
SQL Server MVP

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-10-29 : 09:33:42

quote:
Originally posted by GilaMonster

Update statistics will invalidate any plan that used those statistics. Next time any such plan is retrieved to execute a query it will be discarded and recompiled.

--
Gail Shaw
SQL Server MVP

But this is not happening as I shown in the code in the post, can you try the code and post your feedback. Thx

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 11:57:49

I get a recompile event when the query runs after the stats update and an exec plan with a seek and key lookup (SQL 2008)

If you like I can post a screenshot of profiler showing the (expected) recompile event.

--
Gail Shaw
SQL Server MVP

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-10-29 : 12:12:02

That is fine since I am testing on SQL Server 2005 sp3, can you confirm that your "auto update stats" setting is off for database since that can result in seek second time since we are using "off" and doing manual "update stats" as shown in the post?

Kristen
Test

22859 Posts

Posted - 2010-10-29 : 14:30:38

FWIW I'm pretty sure this did not work as expect in SQL 2000; I thought it was fixed in SQL 2005 (but I have no direct knowledge of SQL 2005, but I have a vague recollection that it was "fixed better" in SQL 2008, so maybe not fully-fixed in SQL2005?)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 16:15:36

quote:
Originally posted by rubs_65

That is fine since I am testing on SQL Server 2005 sp3, can you confirm that your "auto update stats" setting is off for database since that can result in seek second time since we are using "off" and doing manual "update stats" as shown in the post?

You might have mentioned auto update off, before I wasted my time testing something irrelevant,

I don't have SQL 2005 installed any longer (except in a virtual on another machine), but the behaviour is unchanged between the versions. If you want me to test on 2005, it'll have to wait.

That said, I've many times done forced stats updates to fix execution plans that are bad due to stale stats, and that well before the autoupdate threshold would be hit. That's SQL 2005 SP2 and SP3

--
Gail Shaw
SQL Server MVP

rubs_65
Posting Yak Master

144 Posts

Posted - 2010-10-29 : 18:19:28

I just tried and able to reproduce the same behavior on SQL Server 2008R2 so nothing is changed, Can you set "auto update stats off for database options and follow the steps again and post your feedback. Thx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources