Why Index Fragmentation Remains High

In one of companies I worked for, we used to do nightly index rebuilds. What was baffling is even after the nightly rebuilds, the fragmentation for some of the tables remain high.

I did some digging; here’s an explanation on why some index fragmentations remain high.

Both Paul Randal and Kalen Delaney (highly respected, very reputable SQL Server developers/trainers) suggest to rebuild the index only if there’s at least 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.

I checked the "questionable" tables in our databases, and yes, Paul and Kalen were right on the ball. These pages had <1000 pages each.

  • tableA has 9 pages and initial fragmentation of 875. After rebuilding, fragmentation flip flops between 66% and 77%, and never improves beyond 66%
  • tableB has almost 95000 pages and initial fragmentation of 69%. After rebuilding indexes, fragmentation drops to 0.01%. Succeeding rebuilds keep fragmentation to 0.01%

This makes sense, because for smaller tables an index will not really help – SQL Server will usually prefer to do a table scan.

I will post the script that I used to determine and apply selective index rebuilds.

 

Follow Up: Script is posted in

A More Effective Selective Index Rebuild/Reorganize Strategy

Advertisements

2 comments

  • A good automated script that I came across recently can be found here. It allows you to set criteria for how many pages (I.e. 1000) for the index to be over to qualify for reindexing. It also lets you set different ranges of index fragmentation and set up different actions for each range (I.e. rebuild, reorganize, update stats).

    Worked great on our 250GB production database.

    Like

  • also, if the index is HEAP, its frag will remain high after running index rebuild.

    So I index rebuild but I still see indexes with high fragmentation with page count > 1000 and those are not HEAP indexes; tables with high index fragmentation after running the process are big, the smallest one has about 1 million records.

    Trying to understanding why index rebuild does not help?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s