SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


reorganize index


reorganize index

Author
Message
chewychewy
chewychewy
Right there with Babe
Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)Right there with Babe (748 reputation)

Group: General Forum Members
Points: 748 Visits: 520
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41510 Visits: 14413
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
RML51
RML51
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1995 Visits: 1612
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.



EL_oh_EL
EL_oh_EL
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 190
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41510 Visits: 14413
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
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5167 Visits: 3232
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41510 Visits: 14413
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search