Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

rebuilding indexes Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2012 2:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
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
Post #1364283
Posted Tuesday, September 25, 2012 5:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 6,257, Visits: 7,444
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1364360
Posted Thursday, September 27, 2012 3:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
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?
Post #1365546
Posted Friday, September 28, 2012 7:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
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?
Post #1365865
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse