SQL Server Performance Forum – Threads Archive

Index Fragmentation

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

---

Check this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

---

Thanks for the link. I’m using SQL 2005, does all of this 200 link still apply?

---

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.

---

]]>