• Make sure that defragmentation will actually buy you something before you move ahead on complex plans to get it done.

    Defragmentation of indexes is really only important if you do range-selects on the leading edge of the index. If you do single-row selects mainly/exclusively, and only rarely do range selects, then fragmented indexes don't matter much, if at all.

    By "range selects", I mean ones that are like, "WHERE MyDateColumn >= @StartDateParameter AND MyDateColumn < @EndDateParameter". Disjointed or non-sequential multi-row selects aren't "range selects". E.g.: "WHERE MyIntColumn IN (1,6,23,-17,70012)" is not a "range selected", it's a "disjointed/non-sequential multi-row select". It'll probably be handled by multiple seeks instead of a range scan, in the execution plan.

    Same applies to ranges that aren't the leading edge of the index. E.g.: "WHERE MyLeadingEdgeIntColumn = 6 AND MyDateColumn >= @StartDateParameter AND MyDateColumn < @EndDateParameter", where the index looks like "CREATE INDEX MyIndex ON MyTable (MyLeadingEdgeIntColumn, MyDateColumn)". Index fragmentation can matter on that, but is less likely to.

    Single-value tests that will return multiple rows are "range selects". If, for example, in the one right above, there are multiple rows where MyLeadingEdgeIntColumn does equal 6, and those are split across a large number of non-contiguous pages, your query will be slowed down by the fragmentation.

    You can check for this in the index DMVs. Look for data on ranges vs single-row seeks. If what you have is seeks, defragmentation won't gain you much, and may not be worth the work.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon