locked
Index Rebuild RRS feed

  • Question

  • Hi,

    I am planning to rebuild index on my database.(SQL Server 2005 Standard Edition)

    When i query sys.indexes some tables showing 90% and some tables showing 0% fill factor.

    In this case how can i rebuild indexes.

    Actually i am planning to use script from http://www.sqldbpros.com/2010/11/sql-server-rebuild-indexes-the-fastest-way/

    is there any tasks to perform after completion of index rebuild?

    Wednesday, August 7, 2013 8:07 PM

Answers

  • Hello,

    Just a request for different question please create seperate theread.

    >>Is it necessary to again run UPDATE STATISTICS T-SQL after rebuilding the indexes?

    Rebuilding an index will update statistics with the equivalent of a full scan – doesn’t matter whether you use DBCC DBREINDEX or ALTER INDEX … REBUILD, the effect is the same. It can do this because it sees a complete picture of the index while its doing the rebuild.(with some exception)

    Sometimes for big table even though AUTO_UPDATE_STATISTICS = TRUE we still require to update statistics,as during inset update its does not gets fully updated.

    I ALSO SEE YOU ARE NOT READING PREVIOUS POST PROPERLY PLEASE RAED EACH COMMENT PROPERLY I CAN GUESS FROM UR QUESTIONS


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by VijayKSQL Monday, August 12, 2013 6:30 AM
    Thursday, August 8, 2013 2:59 PM

All replies

  • Try the below script for your maintenance of indexes.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Shanky_621MVP Thursday, August 8, 2013 2:45 AM
    Wednesday, August 7, 2013 10:00 PM
  • You have update the statistics after rebuilding the indexes, use this command exec sp_updatestats for updating the stat.

    There is a very simplest way to do this task, no need to do a single line of code.

    Create a Maintenance Plan

    Add to activities on that Rebuild Index task --> right click--> edit, select all the databases you wanted to rebuild the indexes

    Add another activity Update Statistics Task --> right click--> edit, and choose all the databases for the indexes you re-build.

    You can schedule as per your convenience.

    Cheers

    Amar Deep Singh

    Wednesday, August 7, 2013 10:10 PM
  • You have update the statistics after rebuilding the indexes, use this command exec sp_updatestats for updating the stat.

    There is a very simplest way to do this task, no need to do a single line of code.

    Create a Maintenance Plan

    Add to activities on that Rebuild Index task --> right click--> edit, select all the databases you wanted to rebuild the indexes

    Add another activity Update Statistics Task --> right click--> edit, and choose all the databases for the indexes you re-build.

    You can schedule as per your convenience.

    Cheers

    Amar Deep Singh

    Hello Amar,

    Rebuilding Indexes with with full scan automatically update stats.So I suppose no need to do that.why waste resources on again doing the task

    Dear Vijay,

    I suppose you should go for suggestion provided by Latheesh.Try that script its been used worldwide.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Thursday, August 8, 2013 2:45 AM added line
    Thursday, August 8, 2013 2:44 AM

  • Rebuilding Indexes with with full scan automatically update stats.

    A small correction, rebuilding indexes also updates the statistics with full scan. But there might be some non-column stats for which you separately need to perform the stats maintenance.

    This posting is provided “AS IS” with no warranties, and confers no rights.

    If this reply answers your question, please mark it as Answered for others to find it easily.
    If this reply help you resolving the problem, please vote the post as Helpful.

    Thursday, August 8, 2013 3:41 AM
  • Hi,

    Everything is went fine but after index rebuilt, my database size is very large. What shall i do?

    Thursday, August 8, 2013 5:32 AM
  • In SQL server 2005 Index rebuild operation is minimally logged  and only page allocations are logged instead of row inserts into new index ,so i dont think your transaction log would have grown too much .

    What do you mean by size is very large how much has it increased has data file increased a lot or log file?.I suppose you would have done index rebuilt offline as your DB is Standard 2005


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, August 8, 2013 5:48 AM
  • Hi,

    I have done index rebuild offiline. Because we are using sql 2005 std edition. Before index rebuild my db size is around 52 GB but after rebuild the size is around 62 gb. log file is same as old one but .mdf file is grown to 10 GB

    Thursday, August 8, 2013 6:20 AM
  • As what I see this is what Rebuild index does with no SORT_IN_TEMPDB (this feature is available in ent) not quite sure as i have not seen the script, so for sorting space required will be equal to Index space say 20 Gb index is there so almost 20G space will be used while sorting, and this space is many times not released by DB so this might cause your DB file to increase .Your index is first dropped and then it is created again.And the space which comes out is not released immediately.

    Alternative is put your DB in simple recovery and then rebuild,use reorganize Index often


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, August 8, 2013 6:41 AM
  • Hi,

    There is a some confusion. Just rebuild/reorganize index in testdb (SimpleRecover). Actually size =54210.88 MB and Available Space =503 MB but after rebuild the size =58210.88 MB and Available Space 4903.88 MB.

    Now what shall i do to reduce this?

    Thursday, August 8, 2013 7:19 AM
  • Hi,

    There is a some confusion. Just rebuild/reorganize index in testdb (SimpleRecover). Actually size =54210.88 MB and Available Space =503 MB but after rebuild the size =58210.88 MB and Available Space 4903.88 MB.

    Now what shall i do to reduce this?

    I think 4 G of space free space is such that you can live with that.Why do you want to shrink it.Also let me tell you MDF file shrinking causes massive fragmentation so the whole point of index rebuild will be useless.Then again you have to rebuild indexes after some days, why not avoid this .

    Even though if you want to shrink You can use .but i doubt it will allow you to shrink.

    DBCC shrinkfile(1,value )
    --value is value upto which you want to shrink 1 is database ID for MDF file 


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, August 8, 2013 7:41 AM
  • Hi,

    Why i am worrying in the sense, this weekend i am planning to rebuild/reorganize index in our production environment as part of maintenance.

    we having some many database in that some database size around 500 to 600 GB.

    After rebuild if the sizes goes to 550 to 650, what to do? downtime not a constraint for me.

    which impact on daily backup jobs.

    Please suggest me better way to do it. 

    Thursday, August 8, 2013 7:55 AM
  • Better way is change recovery model to simple before rebuild and then after rebuild make recovery model to full.Make sure you initiate full backup after whole process completes

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, August 8, 2013 8:19 AM
  • Hi,

    Why i am worrying in the sense, this weekend i am planning to rebuild/reorganize index in our production environment as part of maintenance.

    we having some many database in that some database size around 500 to 600 GB.

    After rebuild if the sizes goes to 550 to 650, what to do? downtime not a constraint for me.

    which impact on daily backup jobs.

    Please suggest me better way to do it. 


    Also keep in mind that, there might be execution different than your test to prod in-case if they differs in-terms of h/w & S/w & how you are executing along what option you are using so. you can use Olaf's script as suggested.

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, August 8, 2013 10:01 AM
  • Is it necessary to put DB single use mode while doing OFFLINE index rebuild?

    This is the procedure i am following:

    Step1: Taking full db backup

    Step2: Change to simple recovery model

    Step3.Change DB into single user mode

    Step4: perform Index rebuild/Reorganize

    Step5: Put Db back to multiusermode

    Step6: Put DB back to full recovery mode.

    Step7: Perform Full DB/Log  backup

    Please suggest me is it correct procedure?

    Thursday, August 8, 2013 11:41 AM
  • Is it necessary to put DB single use mode while doing OFFLINE index rebuild?

    This is the procedure i am following:

    Step1: Taking full db backup

    Step2: Change to simple recovery model

    Step3.Change DB into single user mode

    Step4: perform Index rebuild/Reorganize

    Step5: Put Db back to multiusermode

    Step6: Put DB back to full recovery mode.

    Step7: Perform Full DB/Log  backup

    Please suggest me is it correct procedure?

    NO its not required to put DB in single user mode for index rebuild ,I dont know from where you got this doubt

    Before rebuilding index change recovery model of DB to simple.

    Run whatever script you have for index rebuild

    After rebuilding change recovery model to full

    Take full backup

    alter database db_name set recovery simple go --after rebuild is done alter database db_name set recovery full

    go

    Also please read about index rebuild and reorganize from this post

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, August 8, 2013 11:49 AM
  • Hi,

    Is it useful if i use SORT_IN_TEMPDB = ON? because i am using SQL Server 2005 Standard Edition

    ALTER INDEX [IX_abcd] ON [dbo].[abcd] REORGANIZE WITH (SORT_IN_TEMPDB = ON)

    ALTER INDEX [IX_abcd] ON [dbo].[abcd] REBUILD WITH (SORT_IN_TEMPDB = ON)


    • Edited by VijayKSQL Thursday, August 8, 2013 2:03 PM MODIFIED
    Thursday, August 8, 2013 2:01 PM
  • Vijay,

    You canot use SORT_IN_TEMPDB option in index reorganize statement

    SORT_IN_TEMPDB is enterprise only feature in sql server 2005 I am not quite sure about it.

    For more details on Sort_in_tempdb read this

    http://technet.microsoft.com/en-us/library/ms188281(v=sql.90).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, August 8, 2013 2:39 PM
  • Hi ,

    Is it necessary to again run UPDATE STATISTICS T-SQL after rebuilding the indexes?

    Because  already my database options set to AUTO CREATE STATISTICS = TRUE and AUTO UPDATE STATISTICS = TRUE in Management Studio in  PRODUCTION environment.

    Thursday, August 8, 2013 2:44 PM
  • Hello,

    Just a request for different question please create seperate theread.

    >>Is it necessary to again run UPDATE STATISTICS T-SQL after rebuilding the indexes?

    Rebuilding an index will update statistics with the equivalent of a full scan – doesn’t matter whether you use DBCC DBREINDEX or ALTER INDEX … REBUILD, the effect is the same. It can do this because it sees a complete picture of the index while its doing the rebuild.(with some exception)

    Sometimes for big table even though AUTO_UPDATE_STATISTICS = TRUE we still require to update statistics,as during inset update its does not gets fully updated.

    I ALSO SEE YOU ARE NOT READING PREVIOUS POST PROPERLY PLEASE RAED EACH COMMENT PROPERLY I CAN GUESS FROM UR QUESTIONS


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by VijayKSQL Monday, August 12, 2013 6:30 AM
    Thursday, August 8, 2013 2:59 PM