5

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?

4
  • How many pages in level 1 / level 2? Is this fragmentation actually causing any performance issues? How did you correlate any performance issues with the fragmentation? Mar 20 '18 at 14:30
  • @AaronBertrand: I did not have any performance issues in this test. On level 1 I have 1500 pages and level 2 has only 2 pages. I was wondering if this could cause performance issues on a production db Mar 22 '18 at 15:50
  • What fill factor are you using?
    – MelvinLusk
    Aug 9 '19 at 19:14
  • @MelvinLusk I use a fillfactor of 100% Aug 19 '19 at 6:09
0

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%

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.