I am using an Index Defrag Script, which provides a nice way to rebuid or reorganize indexes based on fragmentation percentages. I like to rebuild my database indexes if their fragmentation is over 30%, and reorganize if < 30%.
If an index doesn't allow page locks (@AllowPageLocks=0) or contains LOB objects, then the script will reorganize instead of rebuild, even if the index fragmentation value exceeds the rebuild threshold value.
I have an index with 35% fragmentation which doesn't rebuild. I just need to know , what is reason behind this logic. i mean what is reason behind to reorganize if index table contains LOB objects and (@AllowPageLocks =0) , allow page locks.
Best Answer
Is the table too small?
Fragmentation figures are meaningless for small tables that span up only a few extents
This is captured by
@minPageCount
in the script. The default is 8: meaning it will be skipped if one extent in size.