HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Online Index Rebuild of large table takes exclusive lock

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
exclusiverebuildonlinelargeindextakestablelock

Problem

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 NOT NULL,
[accepts_marketing] [bit] NOT NULL,
[address1] nvarchar MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
[address2] nvarchar MASKED WITH (FUNCTION = 'partial(2, "XXX", 0)') NULL,
[city] nvarchar NULL,
[company] nvarchar NULL,
[country] nvarchar NULL,
[country_code] nvarchar NULL,
[email] nvarchar MASKED WITH (FUNCTION = 'email()') NU

Solution

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.

Context

StackExchange Database Administrators Q#275965, answer score: 5

Revisions (0)

No revisions yet.