June 3, 2011 at 12:22 am
Would like to understand the scenario with query paln when we rebuild indexes on a table and also ensured latest stats applied with full scan.
I predict query plan will not change after rebulild of the indexes to clear fragmentation. it will follow the same index structure(s) even after defragmentaions just the cost of the query differs.
June 3, 2011 at 2:56 am
Index rebuild recreates statistics with fullscan. When statistics are rebuilt, all dependant plans get invalidated.
I don't know what happens with index reorganize, but I suspect it doesn't rebuild stats.
Hope this helps
Gianluca
-- Gianluca Sartori
June 7, 2011 at 6:49 am
Gianluca nailed it. You get new statistics during an index rebuild. You will not get updated statistics during an index reorg.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply