Index Rebuild with WAIT_AT_LOW_PRIORITY still blocks
4
votes
1
answer
261
views
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.
Asked by Henrik Staun Poulsen
(2291 rep)
Jul 31, 2025, 08:54 AM
Last activity: Aug 1, 2025, 12:53 AM
Last activity: Aug 1, 2025, 12:53 AM