Index fragmentation in SQL Server 2016

Question

Hi Team,

We have index maintenance plan which run weekly twice.

But we observing some table always reaching 98% fragmentation and these are most highly important tables.

As you knew that we can't rebuid every hour or everyday

So, any solution?

Answer 1

Hi @Vijay Kumar ,

If any, maybe you can consider Page Splits/Incorrect Fill factor as the cause
When you define an index, you have a server-wide default fill factor that is normally set to 100(or 0, which has the same meaning). The default fill factor is fine in many cases, but it will cause a page split. A correctly chosen fill-factor value can reduce potential page splits by providing enough space for index expansion as data is added to the underlying table
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index?view=sql-server-ver15

Or there may be cases where rebuilding the indexes in parallel (or without specifying MAXDOP) can cause fragmentation.
Try rebuilding the index with option MAXDOP=1

Sort order on the index/table. If table cluster index is not based on identity, when new record is being inserted, the new record could enter in the middle of table. It will screw up the cluster index.

In a short, it is necessary to optimize index:https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15#what-to-consider-before-performing-index-maintenance

Answer 2

How big are these tables? The rule of thumb is that for tables taking up less than 1000 pages, there is little reason to care about fragmentation.

And one can question whether you should care about logical fragmentation if you have your database on SSD, which I would assume that you have. Logical fragmentation means that the physical order of the pages on disk is not aligned with the logical order of the index, and this is bad for read-ahead reads on spinning disk. Not such much on SSD.

Physical fragmentation, that is how much space that is used on the pages, still matters. But this is also more difficult to measure, since it requires a full scan of all index levels, which is expensive for big tables.

Answer 3

Is it really the table, or a non-clustered index which is fragmented?

Having a table with millions of rows becoming that fragmented daily is likely caused by a poor choice of a clustered index. Using a GUID for a clustered index is a very poor choice. https://www.mssqltips.com/sqlservertip/6595/sql-server-guid-column-and-index-fragmentation/

Please post your table design.

Answer 4

The real key here is to ask yourself what problem you're trying to solve. You say you have an index that fragments a lot... have you PROVEN that it's causing a performance issue? I kind of doubt it. Look at all the 'tubes, articles, and bogs on the subject of fragmentation. Not a one of them has PROVEN that logical fragmentation matters. The only time it can matter is on the first scan on spinning rust for the first load of data into memory and fixing that is fixing a much smaller problem than you might expect.

What IS bad about fragmentation is the fact that it's caused by page splits. If you can't prevent that on an index, then the only reason why you might want to rebuild an index is if it drops below about 80% page density. And, even then, you have to be careful. If it's an "exploding broom tip" index where you insert into an "append only" index and then do expansive updates, it's ok to rebuild the index at 0/100 fill factors until you can figure out how to prevent that fragmentation. If you rebuild an index that has fairly evenly distributed fragmentation, then doing either a reorganize or a rebuild at 0/100 fill factors is a form of "Death by SQL".

If you don't actually know if the fragmentation is causing an issue, then stop doing it because fixing it the wrong way is much worse than not doing it at all!