3

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.

Improve this question
1

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • hi... thanks for reply... I made my table small for testing purpose , but it has more pages than specified in this script (have 9 page_count). but i seen that, its getting count for index which have Allow_Page_Locks =0 , which is a default set by system... and always have value = 1 (at least in my case ). so it will always get @allowPageLocks = 0 , bcoz of that its not running rebuild but reorganize
    – meghana
    Jan 25, 2012 at 12:25
  • 9 pages is tiny, and fragmentation at that level doesn't really matter anyways. You have issues like this for very small tables (because of limited extents) or very very large tables (because of contiguous space issues).
    – JNK
    Jan 25, 2012 at 13:20
  • @meghana: typically you'd make this something 128 or more to exclude tables less than 1MB (128 x 8k pages = 1MB)
    – gbn
    Jan 25, 2012 at 14:24

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.