大表線上索引重建需要排他鎖

我正在嘗試在 Azure SQL 數據庫上重建一個大表 (77GB) 的聚集索引。表上有很高的並發事務活動，所以我正在使用該ONLINE=ON選項。

這對於較小的桌子很有效；但是，當我在這個大表上執行它時，它似乎在表上採取了排他鎖。我不得不在 5 分鐘後停止它，因為所有事務活動都超時了。

從與 SPID 199 的會話中：

ALTER INDEX PK_Customer ON [br].[Customer] 
REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

從另一個會話：

在相同的結果中再往下一點：

• 對象 978102525 是聚集索引。
• 對象 1125579048 是表。

我了解線上重建可能會在流程開始和結束時“短暫”鎖定。但是，這些鎖定會持續幾分鐘，這並不是一個“短”的持續時間。

附加資訊

在重建執行時，我執行了SELECT * FROM sys.index_resumable_operations;但它返回了 0 行，就好像重建根本沒有開始一樣。

較小的表也有可能大於 900 字節的 PK，並且相同的ALTER語句在沒有任何長時間阻塞的情況下工作，所以我認為它與 PK 大小無關。這些較小的表也有相似數量的nvarchar(max)列。我能想到的唯一真正的區別是這個表有更多的行。

表定義

這是 的完整定義br.Customer。沒有外鍵或非聚集索引。

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

我今天（9 月 24 日）進一步調查並執行SP_WHOISACTIVE @get_locks = 1，它清楚地顯示UPDATE/INSERT/DELETE了執行ALTER INDEX.

通過執行以下查詢在 Customer 表上持有的鎖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>

UPDATE來自在同一張表上執行的會話的鎖：

<Object name="Customer" schema_name="br">
     <Locks>
       <Lock resource_type="OBJECT" request_mode="IX" request_status="WAIT" request_count="1" />
     </Locks>
   </Object>

Microsoft 支持已確認這是 Sql Azure 中的一個錯誤（不確定它是否會影響 Sql Server）。我的理解是，如果從表中刪除一些列，那麼下次我們重建索引時，sql server 將嘗試回收已刪除的列空間（我在這裡故意含糊不清，因為我不完全確定這意味著）和即使提供了 ONLINE = ON 選項，此過程也會發生在表上的排他鎖上。他們正在努力解決問題。

MASKED WITH是一個相當新的功能，因此也是如此RESUMABLE = ON，ONLINE = ON就此而言。

所以我認為你遇到了一個小錯誤，需要幾個月的時間才能修復。

引用自：https://dba.stackexchange.com/questions/275965