Sample Header Ad - 728x90

Database Administrators

Q&A for database professionals who wish to improve their database skills

Latest Questions

4 votes
1 answers
261 views
Index Rebuild with WAIT_AT_LOW_PRIORITY still blocks
I'm running... ```sql ALTER INDEX PK_MyTable ON fct.MyTable REBUILD PARTITION = 261 WITH ( ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 30 minutes, ABORT_AFTER_WAIT = SELF ) ), RESUMABLE = ON, MAX_DURATION = 355 ) ; ``` ...and I expect it **not** to cause any blocking, but it does. It is aski...
I'm running...
ALTER INDEX PK_MyTable 
    ON fct.MyTable 
    REBUILD PARTITION = 261 
    WITH (
        ONLINE = ON (
            WAIT_AT_LOW_PRIORITY (
                MAX_DURATION = 30 minutes, 
                ABORT_AFTER_WAIT = SELF
            )
        ), 
        RESUMABLE = ON, 
        MAX_DURATION = 355 
    )
;
...and I expect it **not** to cause any blocking, but it does. It is asking for a LCK_M_SCH_M lock. My statement is blocked by an INSERT INTO statement, which is what I expect; but it is also blocking many other statements that are asking for a LCK_M_SCH_S lock, and I was not expecting that. At the same time there is a BACKUP running of a database that has FILESTREAM. Could this be the reason? What else could cause this blocking? Have I completely misunderstood WAIT_AT_LOW_PRIORITY? RCSI is on.
Henrik Staun Poulsen (2291 rep)
Jul 31, 2025, 08:54 AM • Last activity: Aug 1, 2025, 12:53 AM
Showing page 1 of 1 total questions