Sample Header Ad - 728x90

Online Index Rebuild of large table takes exclusive lock

9 votes
3 answers
1873 views
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:
Locks from session running UPDATE on same table:
Asked by Clement (576 rep)
Sep 23, 2020, 01:37 PM
Last activity: Nov 20, 2023, 11:32 AM