6

I need to rebuild some big indexes and I'm doing some tests with the various options (sort_in_tempdb, maxdop, online) of the ALTER INDEX statement on an test index with 4 levels and 800000 pages on leaf level.
I noticed when I'm running the statement with Online=on the intermediate pages (level 1) of my index are higher fragmented as before (89% in stead of 3%).

The intermediate pages only get defragmented when I'm setting MAXDOP=1. With the options SORT_IN_TEMBP=ON,ONLINE=OFF the level 2 fragmentation jumps from 0 to 100.

This are the statements that caused an increase of fragmentation on level 1:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on,maxdop=1)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=off,online=on)
ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=on)

This statement caused the fragmentation on level 2 go from 0 to 100 but level 1 stays the same:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (sort_in_tempdb=on,online=off)

Is fragmentation on intermediate pages something to worry about and what is causing the increase in fragmentation?

Frederik Vanderhaegen
  • 2,122
  • 1
  • 17
  • 36

1 Answers1

1

Try this:

ALTER INDEX pk_test ON dbo.test REBUILD WITH (PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = ON,  
    ONLINE = ON, 
    ALLOW_ROW_LOCKS = OFF, 
    ALLOW_PAGE_LOCKS = OFF, 
    FILLFACTOR = 90, 
    OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

This way you will have a FILLFACTOR of 90% and you would probably avoid to have the table fragmentation to spike up again.

Keep in mind that your table size will increase of 10%

Francesco Mantovani
  • 1,695
  • 14
  • 28