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