rebuilding indexes

  • 1. Does index rebuild on a partitioned aligned index for an individual partition causes any locks on other partitions?

    2. Does REORGANIZE on a table causes any locks?

    Thanks

  • Lexa (9/25/2012)


    2. Does REORGANIZE on a table causes any locks?

    Can't answer #1, not sure.

    Reorganize will take a SCH-S lock to keep the metadata straight. It will also take S locks on the data as it processes, and should take an IX lock at the table level to keep anything from escalating to table level, but I'm not sure about that last one. Shouldn't be too hard to test but I don't have anything right now with large enough data that I can 'catch it' in stream to directly test.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (9/25/2012)


    Lexa (9/25/2012)


    2. Does REORGANIZE on a table causes any locks?

    Can't answer #1, not sure.

    Reorganize will take a SCH-S lock to keep the metadata straight. It will also take S locks on the data as it processes, and should take an IX lock at the table level to keep anything from escalating to table level, but I'm not sure about that last one. Shouldn't be too hard to test but I don't have anything right now with large enough data that I can 'catch it' in stream to directly test.

    Ignite shows that a rebuild of an index on a partition caused blocking on inserts into that same table but another, "active" partition. Locks were LCK_M_IX on the insert stmts and LCK_M_SCH_M on the ALTER INDEX REBUILD PARTITION XXX. Is that possible? Can a rebuild on one partition cause blocking on another?

  • Lexa (9/27/2012)


    Evil Kraig F (9/25/2012)


    Lexa (9/25/2012)


    2. Does REORGANIZE on a table causes any locks?

    Can't answer #1, not sure.

    Reorganize will take a SCH-S lock to keep the metadata straight. It will also take S locks on the data as it processes, and should take an IX lock at the table level to keep anything from escalating to table level, but I'm not sure about that last one. Shouldn't be too hard to test but I don't have anything right now with large enough data that I can 'catch it' in stream to directly test.

    Ignite shows that a rebuild of an index on a partition caused blocking on inserts into that same table but another, "active" partition. Locks were LCK_M_IX on the insert stmts and LCK_M_SCH_M on the ALTER INDEX REBUILD PARTITION XXX. Is that possible? Can a rebuild on one partition cause blocking on another?

    Has anyone experienced a similar issue?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply