• Creating or rebuilding indexes online have different behaviors as far as the final lock is concerned.

    When an index is (re)built, a temporary mapping index is populated and, at the end of the population, the index has to be pushed in.

    That last phase requires a table lock: for index creation it's a shared lock, for index rebuild it's a SCH-M lock.

    You can read more about online operations in this whitepaper (for sql 2005, but should be still valid as far as I know) http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/OnlineIndex.doc

    So, your idea should be worth investigating.

    However, I would ask myself whether fragmentation is really a concern and why:

    1) It impacts huge sequential scans, which are not what you typically see in OLTP databases, such as ERPs. Are you missing some indexes?

    2) It doesn't impact data that resides in memory: are you suffering from memory pressure?

    3) It's a major concern on clustered indexes rather than nonclustered indexes: are your clustered indexes designed correctly? A good clustering key is small, unique, unchanging and monotone.

    4) Are you sure that the cause of poor performance is fragmentation? How did you determine that?

    5) Old data doesn't change: have you considered partitioning your tables? Partitions with old data could go to readonly filegroups and have zero fragmentation. This way, read-write partitions could be maintained more easily.

    Hope this helps

    Gianluca

    -- Gianluca Sartori