Rebuild of Indexes

  • DBA-640728

    SSChampion

    Points: 12896

    hi guys i have a job that uses a maintenance plan that does a rebuilt for all my indexes in my DB every day, i also have to do an update in statistics with full scan, but i read that once you do a rebuild then it also does and update in all the statistics with full scan.

    is this true?

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    I was doing some rebuild of indexes on one of my test systesm yesterday and yes it does update the stats associated with the index.

    😉

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    If you rebuild the index then it will update the statistics. If you just reorganize them, then the statistics will not be updated.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    Andras Belokosztolszki (8/6/2008)


    If you rebuild the index then it will update the statistics. If you just reorganize them, then the statistics will not be updated.

    Regards,

    Andras

    And a reference for the above: http://www.sqlskills.com/blogs/paul/2008/01/27/SearchEngineQA10RebuildingIndexesAndUpdatingStatistics.aspx

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Ray Mond

    SSCertifiable

    Points: 7099

    You would still need to update the column statistics for the tables, if any. Only statistics for the indexes get updated during the index rebuild.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    That is true. Now if you have auto statistics turned on and a query touches those columns it would update those statistics at that time. Does that sound correct?

    🙂

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • DBA-640728

    SSChampion

    Points: 12896

    To the ones that were rebuilt does it update the statistics with FULL SCAN?

  • mark johnson-159789

    SSC-Addicted

    Points: 416

    Based on the article by Paul Randall, it does.

    Mark Johnson
    MCP, MCTS Sql Server 2005,MCTS Sql Server 2008, OCP

  • Ray Mond

    SSCertifiable

    Points: 7099

    Now if you have auto statistics turned on and a query touches those columns it would update those statistics at that time. Does that sound correct?

    The statistics are updated only if a reasonable amount of modifications have occured to the table. Also, the statistics may be based on a sample of the data, instead of a full scan as required by the OP. Only column statistics for the smaller tables will be updated using a full scan.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • DBA-640728

    SSChampion

    Points: 12896

    thanks!then is not necessary create a separate job for a update statistics with full scan after the rebuild since it is already doing so.

    Questions: is using the rebuild in the maintenance subplan wizard good for all indexes in the selected DB?

    Should i also leave auto update stats and auto create stats on? if i already have the job to rebuilt the indexes every night?

  • Ola Hallengren

    Hall of Fame

    Points: 3960

    It is not a good idea to update the statistics after an index rebuild. The index rebuild operation has rebuilt, not only the index, but also the statistics.

    Ola Hallengren

    http://ola.hallengren.com

  • Ray Mond

    SSCertifiable

    Points: 7099

    I think you need to distinguish between index statistics and column statistics.

    Index statistics are those statistics for a specific index, that get generated regardless of the AUTO_UPDATE_STATISTICS setting. These statistics are automatically updated when you perform an index rebuild.

    Column statistics are statistics for columns that are not the leading column in any indexes for a table. They are usually generated automatically by SQL Server, when a WHERE predicate references that column, if the AUTO_UDPATE_STATISTICS setting is active. They can also be generated manually. However, they are not automatically updated when you rebuild indexes. They are only updated when a certain threshold of changes have been made to the table (SQL2000) or column (SQL2005), and for large tables, it is likely that only a sample of the rows are used to update the statistics.

    So should you leave the AUTO_CREATE_STATISTICS setting on? Yes, if you want SQL Server to create column statistics automatically.

    What if you turned off the AUTO_UPDATE_STATISTICS setting?

    - you'll need to manually update column statistics.

    - your index and column statistics has to be representative of the table data until the next update. If there is a large number of modifications to the table, your statistics may become inaccurate, resulting in sub-optimal execution plans.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply