locked
Rebuild Indexing and Update Statistics RRS feed

  • Question

  • Hi All,

    i am doing rebuild indexing and update statistics for large size 62GB of database it is taking 8 hours is it normal? can anyone tell me what step i need to do first rebuild indexing or update statistics? currently i am doing first rebuild indexing than update statistics.

    Thanks

    Ali

     

     

    Tuesday, May 11, 2010 12:55 PM

Answers

  • >>can anyone tell me what step i need to do first rebuild indexing or update statistics? currently i am doing first rebuild indexing than update statistics.

    Please ****DO NOT **** do it. Yes, absolutley do not update statistics after rebuilding indexes.

    When you rebuild your indexes statistics are updated with full scan automatically.

    Only if you are defrag your existing indexes using ALTER INDEX REORGANIZE then you may need to update statistics. Again, that also depends on distribution of your data (if you have lots of DML operations then you will need to update statistics) 
    Tuesday, May 11, 2010 1:33 PM
  • Hi Paresh,

    Rebuilding an index will automatically update statistics with full scan , there is no point in updating stats after your rebuild index step.

    Hi Agha,

    What chirag told you is correct , My advice would be to read through this script http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/
     and implement it in your environment . This script reads the number of pages , fragmentation on indexes on each indexes in a database and then it decides whether to rebuild/reorganise/update stats.The general idea behind it would when you have pages greater than 1000 and fragmentation percent more than 40 % , then rebuild indexes else reorg/update stats.


    Thanks, Leks
    Wednesday, May 12, 2010 7:50 AM
    Answerer
  • That doesn't show up in the task or anywhere . But this is how SQL server is designed to behave,when you configure rebuild indexes it update stats with full scan . I can prove you this from Paul Randal's blog, read this and you will understand what are the internal processes happening when an index is rebuilt or reorganized.

    http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

     


    Thanks, Leks
    • Proposed as answer by Chirag Shah Wednesday, May 12, 2010 12:59 PM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 18, 2010 3:46 AM
    Wednesday, May 12, 2010 8:09 AM
    Answerer
  • Thanks Paresh.

    can you please help me regarding rebuild indexing, i am doing this using maintenance plan so do i need to select only rebuild index task option, according to your suggestion this will perform both rebuild index and update statistics right? i am asking this because i didn't see update statistics with full scan option inside rebuild index task box.

    Regards

    Ali


    yes, you don't need to rebuild all the indexes. you need to rebuild indexs if framentation ration grater then 30 percent otherwise you should reorganize that index.

    Yoy can get the script for same on SQLservercentral.com


    -- Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Wednesday, May 12, 2010 4:30 PM

All replies

  • >>can anyone tell me what step i need to do first rebuild indexing or update statistics? currently i am doing first rebuild indexing than update statistics.

    Please ****DO NOT **** do it. Yes, absolutley do not update statistics after rebuilding indexes.

    When you rebuild your indexes statistics are updated with full scan automatically.

    Only if you are defrag your existing indexes using ALTER INDEX REORGANIZE then you may need to update statistics. Again, that also depends on distribution of your data (if you have lots of DML operations then you will need to update statistics) 
    Tuesday, May 11, 2010 1:33 PM
  • i am doing this with maintenance plan, i created a plan and draged the rebuild index option and update statistics option than draged the arrow down to update statistics box.

    full scan and all existing statistics option is available in update statistics box not in rebuild index box. how it will update without update statistics option. i dont know do i need to select only rebuild index option for both tasks?

    Thanks

    Ali

    Tuesday, May 11, 2010 1:46 PM
  • Hi All,

    i am doing rebuild indexing and update statistics for large size 62GB of database it is taking 8 hours is it normal? can anyone tell me what step i need to do first rebuild indexing or update statistics? currently i am doing first rebuild indexing than update statistics.

    Thanks

    Ali

     

     

     
    You need to first rebuild/reorganize indexes. Then run update statistics.

    Please get the script to rebuild/reorganize index as per fragmentation.


    -- Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Tuesday, May 11, 2010 2:03 PM
  • But Chirag Shah said that dont update statistics after rebuild index, it will automatically update statistics after rebuild index so what i need to do?

     

    Thanks

    Ali

    Wednesday, May 12, 2010 5:17 AM
  • Hi Paresh,

    Rebuilding an index will automatically update statistics with full scan , there is no point in updating stats after your rebuild index step.

    Hi Agha,

    What chirag told you is correct , My advice would be to read through this script http://www.sqlservercentral.com/scripts/Automated+index+defragmentation+script/68802/
     and implement it in your environment . This script reads the number of pages , fragmentation on indexes on each indexes in a database and then it decides whether to rebuild/reorganise/update stats.The general idea behind it would when you have pages greater than 1000 and fragmentation percent more than 40 % , then rebuild indexes else reorg/update stats.


    Thanks, Leks
    Wednesday, May 12, 2010 7:50 AM
    Answerer
  • Thanks Paresh.

    can you please help me regarding rebuild indexing, i am doing this using maintenance plan so do i need to select only rebuild index task option, according to your suggestion this will perform both rebuild index and update statistics right? i am asking this because i didn't see update statistics with full scan option inside rebuild index task box.

    Regards

    Ali

    Wednesday, May 12, 2010 8:03 AM
  • That doesn't show up in the task or anywhere . But this is how SQL server is designed to behave,when you configure rebuild indexes it update stats with full scan . I can prove you this from Paul Randal's blog, read this and you will understand what are the internal processes happening when an index is rebuilt or reorganized.

    http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

     


    Thanks, Leks
    • Proposed as answer by Chirag Shah Wednesday, May 12, 2010 12:59 PM
    • Marked as answer by Alex Feng (SQL) Tuesday, May 18, 2010 3:46 AM
    Wednesday, May 12, 2010 8:09 AM
    Answerer
  • Ok Thank you very much for your detail answer, now i am statisfied with your answer. :)
    Wednesday, May 12, 2010 12:18 PM
  • Thanks Paresh.

    can you please help me regarding rebuild indexing, i am doing this using maintenance plan so do i need to select only rebuild index task option, according to your suggestion this will perform both rebuild index and update statistics right? i am asking this because i didn't see update statistics with full scan option inside rebuild index task box.

    Regards

    Ali


    yes, you don't need to rebuild all the indexes. you need to rebuild indexs if framentation ration grater then 30 percent otherwise you should reorganize that index.

    Yoy can get the script for same on SQLservercentral.com


    -- Paresh Prajapati
    http://paresh-sqldba.blogspot.com/
    LinkedIn | Tweet Me | FaceBook | Brijj
    Wednesday, May 12, 2010 4:30 PM
  • I prefer REBUILD-ing all indexes every weekend.

    Use FILLFACTOR 80 for dynamic tables.

    I found REORG to be very, very slow, it is also logged operation (REBUILD is logged as well).

    For extreme dynamic table you may have to use FILLFACTOR 70 and REBUILD it not only weekend but during the week as well. The best guideline is checking index fragmention.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Wednesday, May 12, 2010 5:29 PM
    Answerer
  • >>can anyone tell me what step i need to do first rebuild indexing or update statistics? currently i am doing first rebuild indexing than update statistics.

    Please ****DO NOT **** do it. Yes, absolutley do not update statistics after rebuilding indexes.

    When you rebuild your indexes statistics are updated with full scan automatically.

    Only if you are defrag your existing indexes using ALTER INDEX REORGANIZE then you may need to update statistics. Again, that also depends on distribution of your data (if you have lots of DML operations then you will need to update statistics) 

    After you rebuild an index, does its stats get updated with full scan immediately ?
    Wednesday, February 8, 2017 3:22 AM