Online Index Rebuild of large table takes exclusive lock

Question

I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option.

This has worked well for smaller tables; however, when I run it on this large table it seems to take exclusive locks on the table. I had to stop it after 5 minutes because all transactional activity was timing out.

From session with SPID 199:

ALTER INDEX PK_Customer ON [br].[Customer] 
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

From another session:

A bit further below in the same results:

• Object 978102525 is the clustered index.
• Object 1125579048 is the table.

I understand that online rebuild can take locks for a 'short' duration at the start and end of the process. However, these locks are taken for several minutes, which is not exactly a 'short' duration.

Additional info

While the rebuild is running, I ran SELECT * FROM sys.index_resumable_operations; but it returned 0 rows, as if the rebuild hadn't started at all.

The smaller tables also have a PK potentially > 900 bytes and the same ALTER statement worked without any long blocking so I don't think it's related to PK size. These smaller tables also had a similar amounts of nvarchar(max) columns. The only real difference I can think of is that this table has many more rows.

Table definition

Here is the full definition of br.Customer. There are no foreign keys or non clustered indices.

CREATE TABLE [br].[Customer](
    [Id] [bigint] NOT NULL,
    [ShopId] [nvarchar](450) NOT NULL,
    [accepts_marketing] [bit] NOT NULL,
    [address1] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [address2] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [city] [nvarchar](max) NULL,
    [company] [nvarchar](max) NULL,
    [country] [nvarchar](max) NULL,
    [country_code] [nvarchar](max) NULL,
    [email] [nvarchar](max) MASKED WITH (FUNCTION = 'email()') NULL,
    [first_name] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [last_name] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [note] [nvarchar](max) NULL,
    [phone] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [province] [nvarchar](max) NULL,
    [province_code] [nvarchar](max) NULL,
    [state] [nvarchar](max) NULL,
    [tax_exempt] [bit] NOT NULL,
    [verified_email] [bit] NOT NULL,
    [zip] [nvarchar](max) NULL,
    [multipass_identifier] [nvarchar](max) NULL,
    [created_at_local] [datetimeoffset](7) NOT NULL,
    [updated_at_local] [datetimeoffset](7) NOT NULL,
    [tags] [nvarchar](max) NULL,
    [address_phone] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [address_firstname] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
    [address_lastname] [nvarchar](max) MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [ShopId] ASC,
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [br].[Customer] ADD  DEFAULT ('0001-01-01T00:00:00.000+00:00') FOR [created_at_local]
GO

ALTER TABLE [br].[Customer] ADD  DEFAULT ('0001-01-01T00:00:00.000+00:00') FOR [updated_at_local]
GO

sp_WhoIsActive

I investigated further today (24 Sep) and ran SP_WHOISACTIVE @get_locks = 1, which clearly shows all UPDATE/INSERT/DELETE transactions blocked by the session running the ALTER INDEX.

Locks held on Customer table by query running the ALTER INDEX:

<Object name="Customer" schema_name="br">
      <Locks>
        <Lock resource_type="METADATA.INDEXSTATS" request_mode="Sch-S" request_status="GRANT" request_count="1" />
        <Lock resource_type="METADATA.INDEXSTATS" index_name="PK_Customer" request_mode="Sch-S" request_status="GRANT" request_count="1" />
        <Lock resource_type="METADATA.STATS" request_mode="Sch-S" request_status="GRANT" request_count="1" />
        <Lock resource_type="OBJECT" request_mode="S" request_status="GRANT" request_count="96" />
        <Lock resource_type="OBJECT" request_mode="X" request_status="GRANT" request_count="96" />
        <Lock resource_type="OBJECT.INDEX_OPERATION" request_mode="Sch-M" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="PK_Customer" schema_name="br">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="Sch-S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>

Locks from session running UPDATE on same table:

<Object name="Customer" schema_name="br">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" request_count="1" />
      </Locks>
    </Object>

Answer 1

MASKED WITH is a quite new feature, and so is RESUMABLE = ON, and ONLINE = ON for that matter.

So I think you have run into a small bug, that will take a few months to get fixed.

Answer 2

Microsoft Support has confirmed this is a bug in Sql Azure (not sure if it impacts Sql Server). My understanding is that if one drops some columns from the table then the next time that we rebuild the index, sql server will attempt reclaim the deleted column space (I'm vague on purpose here because I'm not entirely sure that means) and this process happens with an exclusive lock on the table, even if the ONLINE = ON option is supplied. They are working on a fix.

Answer 3

From the ALTER INDEX documentation:

In case the base table contains LOB column(s) resumable clustered index rebuild requires a Sch-M lock in the Starting of this operation

You are doing resumable rebuild and the table contains LOB columns (nvarchar(max)). We can clearly see that the rebuild holds schema modification lock, which will block everything else. I would remove RESUMABLE = ON from your ALTER INDEX command and see, if it still blocks as much.