patternsqlMinor
Online Index Rebuild of large table takes exclusive lock
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
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:
From another session:
A bit further below in the same results:
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
The smaller tables also have a PK potentially > 900 bytes and the same
Table definition
Here is the full definition of
```
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
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.
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.