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>
