question

shashidhar avatar image
shashidhar asked

Why index REBUILD does not reduce index fragmentation?

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases, REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation?

sql server 2017
10 |1200 characters needed characters left characters exceeded

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

ThomasRushton avatar image
ThomasRushton answered

Is the table particularly small?

Recommendations are generally not to worry about rebuilding indexes unless they're more than a certain size. See, for example, this SQLSkills page - https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/

2 comments
10 |1200 characters needed characters left characters exceeded

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

Yes, The table size is 80 kb and Fragmentation is 50%.

0 Likes 0 ·

Then forget about it. This table is so small that defragging any index on it is a waste of time and effort.

0 Likes 0 ·
shashidhar avatar image
shashidhar answered

Thanks for your response.

10 |1200 characters needed characters left characters exceeded

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

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.