Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

reorganize index Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 12:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 7, 2014 12:29 AM
Points: 93, Visits: 407
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
Post #1432481
Posted Tuesday, March 19, 2013 12:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1432493
Posted Wednesday, March 20, 2013 10:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612
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.


Post #1433331
Posted Tuesday, March 26, 2013 10:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:23 PM
Points: 21, Visits: 184
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.
Post #1435759
Posted Tuesday, March 26, 2013 11:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1435768
Posted Wednesday, March 27, 2013 3:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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
Post #1435806
Posted Wednesday, March 27, 2013 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:36 PM
Points: 7,097, Visits: 12,600
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
Post #1435900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse