SQL Server Performance

Index Fragmentation

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DanCassell, Feb 15, 2007.

  1. DanCassell New Member

    Hi

    I have a reference table with 791 rows.
    Using dm_db_index_physical_stats it says that the average framentation for this clustered index is 85.71%
    It says in MSDN that if the % is high you should consider rebuilding the index. However, if I rebuild the index, it is still 85.71% fragmented. The Fill Factor is currently 0.

    Does this high percentage make any difference for small tables? Is it only large tables that rebuilding the index makes sense? Should I be changing the Fill Factor instead? If I set the Fill Factor to 1 the average framentation goes down to 1%. Would that make any difference to performance if I did that?

    Thanks
    Dan
  2. ranjitjain New Member

  3. DanCassell New Member

    Thanks for the link. I'm using SQL 2005, does all of this 200 link still apply?
  4. ranjitjain New Member

    Hi,
    Even though the link is about maintaining SQL2000 indexes, still i posted the link for you to get more familiar with de-fragmentation, overview on fragmentation and how it impacts performance.

    In 2005 the basics of indexing is still the same only few commands must have changed and the way online indexing is carried out.

Share This Page