asked
Index Fragmentation
I'm looking at Index Fragmentation on SQL Server 2008 R2 database tables. Rebuilding the index doesn't seem to have any affect - fragmentation is the same. I've even tried to drop and recreate the index, but still getting the same fragmentation, especially in the case of tables that where records are updated and inserted frequently. The fill factor was originally set to 90 and not changed when the index is rebuilt. What steps am I missing? Why doesn't the fragmentation improve? Any ideas?
It is wholly possible that the index isnt defragmented because it is too small. If there isnt much data then the fragmentation doesnt have a big effect. From
http://msdn.microsoft.com/en-us/library/ms189858.aspx > In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents.
If the indexes are small then fragmentation can remain: >In general, fragmentation on small indexes is often not controllable. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding the index. For more information about mixed extents, see Understanding Pages and Extents. taken from [MSDN | BOL][1] [1]:
http://msdn.microsoft.com/en-us/library/ms189858.aspx
If the index is relatively small, you don't have to care so much about fragmentation. If the index is larger and fragmented, you should benefit from an index rebuild. Is the fragmentation level the same directly after the index rebuild? A table with many inserts, deletes and updates will gradually get their indexes fragmented. You could benefit from having a lower fillfactor, så that you leave room for data to grow within the pages, but no guarantee.
Thanks, all. After looking at your answers and looking at the statistics, the tables where rebuilding indexes doesn't help have very few records in them. When I look at the different index levels, fragmentation goes up, but they have very few records. I'll experiment with fill factor. The database I'm working on is a copy of a production database on a dev server. Thanks, again.
fill factor wont affect this, it is all to do with the size of the index.
