SQL Server Performance

Reading stats after rebuilding an Index

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Kwisatz78, Aug 28, 2009.

  1. Kwisatz78 New Member

    Hi all prior to an index rebuild I had the following stats
    avg_fragmentation%fragment_countavg_fragment_size_in_pagespage_count
    53.7070524412971.7054741712212After I ran the rebuild I got the following stats:
    avg_fragmentation%fragment_countavg_fragment_size_in_pagespage_count
    93.1072818216891.0651272941799I am confused with this as the fragmentation for this index seems to have increased after the rebuild, can anyone advise on what has happened?
    Thanks
  2. Luis Martin Moderator

    More % less fragmentation.
    Scan Density [Best Count: Actual Count]Is a percentage. It is the ratio Best Count to Actual Count. This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.
    Best Count is the ideal number of extent changes if everything is contiguously linked. Actual Count is the actual number of extent changes.
  3. Kwisatz78 New Member

    Hi there
    Sorry but I don't believe what you have written to be correct. The DMV sys.dm_db_index_physical_stats column avg_fragmentation_in_percent should be as close as possible to 0% for contiguous pages.
    As for what you have written about Best Count I am unsure what that is?
    Thanks
  4. moh_hassan20 New Member


    The reasnable value of avg_fragmentation_in_percent is a value between 5-30%.
    Any value greater than 30% , indicates high fragmentation.

    That is applied for non heap tables.

    can i know if the table of that index has a cluster index . if yes, what avg_fragmentation_in_percent for that clustered index.?
  5. Luis Martin Moderator

    My mistake. Apologize.
  6. Kwisatz78 New Member

    Hi Moh thanks for the reply
    Yes the table does have a clustered index and has the following stats:
    avg_fragmentation% = 2.5
    Page_count = 10372
    This seems good to me, have you any ideas?
  7. moh_hassan20 New Member

    Use the CREATE INDEX statement with drop_existing rather than ALTER INDEX
    don't shrink database after reindex

    Use fill factor 80-90 , not the default which is 100%
    create all index of that table again and feedback the result of avg_fragementation
  8. virmahi New Member

    Hi Hassan,
    There is a maintenance sheduled for our database which is run daily and the steps are as :-
    1) Merge Partition
    2) Set Next Partititon
    3). Split Partition
    4). Reindex
    5) Backup Log
    6). Shrink DB
    As you wrote that Shrinking shouldn't be done after Reindexing...what I ned to cange in the above order? Please advice.
    Regards,
    Virmahi
  9. melvinlusk Member

    Unless you're really short on disk space, I wouldn't worry about shrinking the DB. It's a catch 22; if you shrink the DB after rebuilding indexes, it will cause them to fragment. If you shrink before reindexing, the rebuild will extend the size of the database to make room for the empty data pages.
  10. virmahi New Member

    Fragmentation can cause performance issues. So do you advice me to change the order in any way.... Please tell me as what should be the order to avoid the fragmentation.
  11. moh_hassan20 New Member

    hi Virmahi
    sorry for delay in answer.
    your steps 1-5 are in logical order ,except avoid step 6 shrink db , as suggest in my reply above and by melvinlusk
    sql server re-use the empty space.
    set auto grow of database to a fixed value in MB , enough to your expected grow per month,
    for capacity planning of storage , don't exceed 80 % of the hard disk - and add extra disks as needed.

Share This Page