locked
Fragmentation RRS feed

  • Question

  • Hi Team 

    In one of database some table index Fragmentations are utilizing above 90%. for this i have rebuild the Index that purticular indexes. Again after two hours its repeating some another tables.

    Kindly  please suggest me what is the long term solution for this.


    subu

    Monday, March 16, 2015 1:21 PM

Answers

  • Hello

    See the MSDN articel :Reorganize and Rebuild Indexes


    https://msdn.microsoft.com/en-us/library/ms189858.aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:49 AM
    Wednesday, March 18, 2015 11:56 AM
  • Subbu,

    You need to learn few basics about fragmentation and below article will help you

    Why is index still fragmented after rebuild

    >In one of database some table index Fragmentations are utilizing above 90%

    There is nothing like index utilization 90 % this is totally wrong.

    >Again after two hours its repeating some another tables.

    What does this mean, repeating ? Please take time and study the basics


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Michelle Li Tuesday, March 17, 2015 1:08 PM
    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:49 AM
    Monday, March 16, 2015 3:58 PM

All replies

  • Decreasse the Fill factor is one option...

    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, March 16, 2015 1:26 PM
  • Tony,

    Thanks for your reply.

    But its not recommended I saw in some of the links. Is there any other please suggest me.

     


    subu

    Monday, March 16, 2015 1:35 PM
  • "In one of database some table index Fragmentations are utilizing above 90%. for this i have rebuild the Index that purticular indexes. Again after two hours its repeating some another tables"

    I am not really sure what your problem is. If you are saying that for different tables the fragmentation is high at different points then its fine. it is supposed to be.

    If yu are saying the same tables/indexes have high after reindexing then...

    what is the page count of these indexes? If it is less than 1000 then you can safely ignore. You dont have to reindex them.

    If the page count is greater than 1000, then check what is the fillfactor. You need to finetune the fill factor.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, March 16, 2015 1:41 PM
    Answerer
  • You've been given the same basic response to your many variations of this question.  Don't blindly rebuild indexes.  Ignore small tables and indexes.  Choose the appropriate strategy to rebuild your indexes to minimize the burden on your server.  And most importantly, figure out why this index becomes so fragmented and respond accordingly.  The long term solution will depend on understanding that last point.
    Monday, March 16, 2015 1:47 PM
    Answerer
  • Hi Scoot,

    You are 100% Correct i stick there only. Why this index becomes so fragmented and How can we check kindly suggest me on the same.

    Thank for your reply.


    subu

    Monday, March 16, 2015 3:07 PM
  • You didn't answeres the question yet; how many data pages do the index / table have?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 16, 2015 3:17 PM
  • Hi Olaf,

    Just now i have checked one more index was showing 99% fragmention. For this i have checked with the count only 


    SELECT avg_page_space_used_in_percent
    ,avg_fragmentation_in_percent
    ,index_level
    ,record_count
    ,page_count
    ,fragment_count
    ,avg_record_size_in_bytes
    FROM sys.dm_db_index_physical_stats(DB_ID(''),OBJECT_ID(''),NULL,NULL,'DETAILED')
    GO

    Using above script but one table went 1107 so for that perticulat index only fragmentation make it as 85% after then rebuild the index now its every thing was fine.

    For next time on-wards i will check page count then i will take action occurdingly. Thanks for your suggestion.

    I any thing wrong please correct me.


    subu

    Monday, March 16, 2015 3:27 PM
  • Subbu,

    You need to learn few basics about fragmentation and below article will help you

    Why is index still fragmented after rebuild

    >In one of database some table index Fragmentations are utilizing above 90%

    There is nothing like index utilization 90 % this is totally wrong.

    >Again after two hours its repeating some another tables.

    What does this mean, repeating ? Please take time and study the basics


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Proposed as answer by Michelle Li Tuesday, March 17, 2015 1:08 PM
    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:49 AM
    Monday, March 16, 2015 3:58 PM
  • Hello

    See the MSDN articel :Reorganize and Rebuild Indexes


    https://msdn.microsoft.com/en-us/library/ms189858.aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    • Marked as answer by Michelle Li Tuesday, March 24, 2015 2:49 AM
    Wednesday, March 18, 2015 11:56 AM
  • You may have index maintenance in place. You can use Ola's great script to implement the same.

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


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]

    Wednesday, March 18, 2015 12:43 PM
    Answerer