Post reply

How and when we need to rebuild indexes, reorganise indexes , defragindexes

  • February 14, 2009 at 12:21 am

    #126471

    Hi,

    I would like to know How and when we need to rebuild indexes, reorganise indexes , defragindexes in SQL Server 2005. Please suggest a link or article which explains this.

    With Regards

    Dakshina Murthy

  • February 14, 2009 at 1:53 am

    #944047

    You may want to start by searching this forum, a ton on info on those subjects. 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 14, 2009 at 7:34 am

    #944077

    dakshinamurthy

    I would like to know How and when we need to rebuild indexes, reorganise indexes , defragindexes in SQL Server 2005. Please suggest a link or article which explains this.

    ALTER INDEX....REBUILD - For rebuilding indexes

    ALTER INDEX....REORGANISE - For Reorganizing them

    Run : sys_dm_db_index_physical_stats and check for avg_fragmentation_in_percent column

    Typically its: lesser then 30% reorganize indexes, greater than 30% rebuild indexes

    I would do it once in a week or off time daily if you have any . And also it depends on how frequent your data gets fragmented, i mean heavy inserts per day? analyze your data and plan it.I would set a job in my regular maintenance plan once decided whether to rebuild or re organize.

    Be cautious about your log file before rebuilding them- plan according to your DR plan

  • February 16, 2009 at 2:32 am

    #944373

    Hi,

    Thanks for the help. I have excuted the Rebuild Command for certain tables (20 + ) whose avg_fragmentation_in_percent varies from 35 to 80 for certain tables. Even after rebuilding indexes, the avg_fragmentation_in_percent has not changed for the tables, what i have to do or is there any thing which we have to look in to, please suggest.

    With Regards

    Dakshina Murthy

  • February 16, 2009 at 2:35 am

    #944374

    What is the size of your table that has 35-80 fragmentation? because if the size of the table is small enough then its quite common to have that fragmentation and you do not need to defrag

  • February 16, 2009 at 2:52 am

    #944378

    The size is in KBs,

  • February 16, 2009 at 3:04 am

    #944382

    How many pages? Can you query it please? If the table is 1000 pages or more and if still the fragmentation is same then I would worry about it. Any thing smaller than that, I would not worry about it.

  • February 16, 2009 at 3:14 am

    #944388

    Hi,

    The page counts are less than 1000. Thanks a lot, it was really helpfull for me. Thanks for the instant reply and help.

    With Regards

    Dakshina Murthy

  • February 16, 2009 at 3:28 am

    #944394

    My Pleasure!!! You dont have to worry if it is lesser than 1000 pages. The pages would be loaded to the memory anyway:)

  • March 15, 2011 at 11:02 am

    #1298994

    Maechismo_8514, I have a question about your comment "the pages would be loaded to the memory anyway"

    I typically REORG fragmented indexes that are > 50 pages or more because in our environment I've noticed it makes a significant difference. However, what do you mean that they will be loaded to the memory?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Viewing 10 posts - 1 through 9 (of 9 total)

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