6

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:

cl

A bit further below in the same results:

enter image description here

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

enter image description here

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>
2
  • Are the other tables also using Column Masking? Sep 25 '20 at 7:53
  • Ah @HenrikStaunPoulsen I think you might be onto something here! This one has masking where other tables don't. I tried the same ALTER with two other very large tables, one with masking and one without. The one with masking caused blocking. The one without masking didn't. Paul, could it be a bug when mixing ONLINE=ON on tables with dynamic data masking?
    – Clement
    Sep 25 '20 at 9:19
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.

2
  • See my answer. This is a confirmed bug and is not related to dynamic data masking
    – Clement
    May 13 at 10:02
  • I'm glad you found an explanation. If you have dropped columns, (with testers never do), you have a use-case that has not been tested. May 13 at 15:54
1

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.

-2

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.

1
  • Removing the RESUMABLE = ON has had no effect. Still blocking all transactional activity.
    – Clement
    Sep 24 '20 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.