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?

  • 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? – Aaron Bertrand 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 – Frederik Vanderhaegen Mar 22 '18 at 15:50
  • What fill factor are you using? – MelvinLusk Aug 9 at 19:14
  • @MelvinLusk I use a fillfactor of 100% – Frederik Vanderhaegen Aug 19 at 6:09

Your Answer

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

Browse other questions tagged or ask your own question.