question

grxclausen avatar image
grxclausen 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?
indexingfragmentationfillfactor
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
This thing drove me crazy a couple of years ago when I could not figure out how to reduce the fragmentation of the index on the narrow catalog table which had about 500 records. Then I found the link and the headdesk went away.
1 Like 1 ·
Kev Riley avatar image
Kev Riley answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
Hmmm. 7s faster than me.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
Haha. Wow an almost identical answer! This is scary.....
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Very spooky indeed!
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

grxclausen avatar image grxclausen commented ·
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.
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
fill factor wont affect this, it is all to do with the size of the index.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.