Index Rebuild with WAIT_AT_LOW_PRIORITY still blocks

Question

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.

• J. Mini: At the point in time when the blocking happens, what percent_complete does sys.index_resumable_operations show?
• Henrik Staun Poulsen: @J.Mini; It is at a 100%

Answer 1

Do you have async stats update enabled?

One case where I've seen it is that async stats update is blocked by the wait_at_low_priority index rebuild, and in turn blocks everything else.

You can fix it in SQL 2022 by enabling this config

ALTER DATABASE SCOPED CONFIGURATION
SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON

But make sure that this is indeed your problem - I recommend enabling the blocked process report Extended event, and monitor what actually happened.

I've blogged about this here: https://straightforwardsql.com/posts/async-stats-update-causing-blocking/

• Henrik Staun Poulsen: this looks like it is spot on. Unfortunately we're still at SQL Server 2019, where ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY is unknown, AFAIK.