[AdaptiveIndexDefrag] Index_level filter conflict with @scanMode = DETAILED

[paolov]

At lines 1175 and 1188 the filter on index_level = 0 should not be present if @scanMode = DETAILED.

I had an index in a table where level 0 showed fragmentation below 1% but level 1 at 98%. Because of the where condition on index_level, that index was never computed for rebuild. After running ALTER INDEX REBUILD manually, all levels went down below 1%.

As dm_db_index_physical_stats always return level 0 fragmentation for scanmode LIMITED, I suggest to remove the filter on index_level

Here the current line (there are 2 occurences of that) AND ps.index_level = 0 -- leaf-level nodes only, supports @scanMode

[pmasl]

So we're targeting leaf level fragmentation only which is why its filtering on 0, and then affects subsequent logic. For 0 fragmentation of the leaf-level, it's curious you had such big fragmentation on an upper level. Instead of removing, I'll keep it and think of how to surface the max fragmentation for an index, to signal it has to be picked up at the whole or partition level. Feel free to also suggest in this regard. Thank you!