none
How to read fragmentation of Indexes in Sql server

Answers

  • Hello Ravi,

    If you are asking for a script to detect fragmentation percent, you can make use of the DMF sys.dm_physical_index_stats. Based on the avg_fragmentation_in_percent values you can decide whether your index needs a reorganize or rebuild(yet there are few other factors which influence the improvements the activity yields). Below article will give you the script as well basic idea about the avg_fragmentation_in_percent.

    http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, October 20, 2013 5:08 PM

All replies

  • Hello Ravi,

    If you are asking for a script to detect fragmentation percent, you can make use of the DMF sys.dm_physical_index_stats. Based on the avg_fragmentation_in_percent values you can decide whether your index needs a reorganize or rebuild(yet there are few other factors which influence the improvements the activity yields). Below article will give you the script as well basic idea about the avg_fragmentation_in_percent.

    http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

    Thanks

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Sunday, October 20, 2013 5:08 PM
  • How to read fragmentation of Indexes in Sql server????

    Hello Ravi,

    You should just not look at Avg_fragmentaion_in_percent col  but also look for page_count column,even if fragmentation is 90 % and page_count is just 80 or 100 or 200 , rebuilding index might not reduce fragmentation.

    Generally if page count is <500 there is no point in rebuilding fragmenetd index ,or even if you rebuild it it hardly makes any difference.

    HTH


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

    Monday, October 21, 2013 5:09 AM
  • Hi Shanky,

    Can you explain about 

    page_count ???

    I am not very much clear.


    ravi shekhar

    Monday, October 21, 2013 5:38 AM
  • Hello Ravi,

    sys.dm_physical_index_stats will have Page_count in the result. This column has is important to access the fragmentation of the index. If Page_Count is less than 1000, you may ignore the avg_fragmentation_in_percent value. 

    The (general)rule is as follows:

    If the page count is more than 1000

    If fragmentation is less than 5 % - Leave as it is

    If fragmentation is more than 5 % and less than 30% - Reorganize the index

    If fragmentation is more than 30% - Rebuild index

    Else leave it

    You can use Ola's script for the index maintenance which is widely accepted by the industry.

    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.

    Monday, October 21, 2013 5:49 AM
  • Hi Shanky,

    Can you explain about 

    page_count ???

    I am not very much clear.


    ravi shekhar

    Hello,

    If you run

    select * from sys.dm_index_physical_stats(db_id(),object_id,null,null,null)
    You will see page_count column

    BOL:http://technet.microsoft.com/en-us/library/ms188917.aspx

    Page_count: is

    Total number of index or data pages.

    For an index, the total number of index pages in the current level of the b-tree in the IN_ROW_DATA allocation unit.

    For a heap, the total number of data pages in the IN_ROW_DATA allocation unit.

    For LOB_DATA or ROW_OVERFLOW_DATA allocation units, total number of pages in the allocation unit.

    Now if page count  is very less, fragmentation would hardly matter because serch willl be utilized only few pages (100 is few page as compared to 10K and 100K index page)  which will be very quick to reach to data required to satisfy query.Even if there is fragmentaton in few pages it hardly matters.

    I have seen this in many case you can also try to rebuild index with page_count 100 or 200 you will see even after rebuild fragmentation will not changes or will change slightly.But if you rebuild index for page_count 100K or 10K it will affect fragmentation greatly


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

    Monday, October 21, 2013 5:51 AM