March 19, 2013 at 12:16 am
Hi Guys,
I'm going to reorganize indexes with > 10%.
Is there a need to update statistics after the re-org?
Understand that if it's a rebuild, SQL server will automatically update the stats.
My question is, does re-org need a update statistics? Is the old statistics still accurate after re-org?
In Micrsoft Note:
Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.
thanks
March 19, 2013 at 12:47 am
chewychewy (3/19/2013)
Hi Guys,I'm going to reorganize indexes with > 10%.
Is there a need to update statistics after the re-org?
No need.
From the 2008 R2 article http://msdn.microsoft.com/en-us/library/ms190397(v=sql.105).aspx:
Do not update statistics after operations such as rebuilding, defragmenting, or reorganizing an index. These operations do not change the distribution of data and have no impact on statistics. Note, however, that the query optimizer updates statistics when you rebuild an index on a table or view by using ALTER INDEX REBUILD or DBCC DBREINDEX. This occurs as a byproduct of the internal implementation of these operations and is not done because statistics need to be updated. The query optimizer does not update statistics after defragmentation or reorganization operations because these operations do not drop and recreate the index.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 20, 2013 at 10:20 am
Just to clarify what opc.three's link says. The reorg does not affect your db statistics, but if they were in a bad state before the reorg they'll still be in a bad state after a reorg is run. As you noted, a rebuild will in effect update the statistics.
March 26, 2013 at 10:19 pm
So if we are reorganizing index nightly. is there a need to update statistics? Currently, there is no job to refresh statistics beside the database setting to update statistics. I have verified statistics on table are not up to date and upto 6 months old.
March 26, 2013 at 11:24 pm
Statistics are not indexes. They just describe the distribution of data. If you have time to update stats nightly then I would do that. Time the reorg plus the stats update against.an index rebuild. You may find a rebuild makes sense if you want to ensure stats are updated.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2013 at 3:31 am
Normally you reorganise the index if the fragmentation is less than 30% (I think MS recommends a 20% threshold) over that and you start to look at rebuilding the index as that will give you the better performance boost, as well as updating the stats.
In terms of data pages the general consensus is its not always worth rebuilding if there are less than 1000 pages, but it can depend.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
March 27, 2013 at 7:25 am
To clarify one point, when i said time the reorg plus the stats update, that means WITH FULLSCAN which is what you would get with a rebuild.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply