如何解決 Azure SQL 數據庫中的 INSERT 爭用？

我正在使用具有非常熱的日誌記錄表的 Azure SQL 數據庫。每當插入、更新或刪除任何其他表中的行時，Web 應用程序都會將一行寫入此日誌記錄表。行非常小，表上只有一個 NC 索引和 PK，但這些插入可以堆疊，有時甚至超時，然後應用程序必須使用其指數退避重試邏輯。

架構師決定在這個日誌表上實現分區來解決插入速度問題。每個表都有 ProjectId (UNIQUEIDENTIFIER) 作為 PK 的一部分。他在 Project 表中添加了 BIGINT IDENTITY ProjectNo 列，分區函式為 ProjectId 查找 ProjectNo，然後使用 ProjectNo % 200 選擇該項目使用的所有行的分區。

CREATE TABLE [dbo].[ChangeLog](
   [IncrementId]           [bigint] IDENTITY(1,1)  NOT NULL,
   [ProjectId]             [uniqueidentifier]      NOT NULL,
   [ProjectNoPartitionKey] [tinyint]               NOT NULL,
   [CreatedBy]             [int]                   NOT NULL,
   [CreatedOn]             [datetime2](0)          NOT NULL,
   [EntityId]              [uniqueidentifier]      NULL, -- but no null values
   [EntityName]            [varchar](50)           NULL, -- but no null values
   [RevisionId]            [uniqueidentifier]      NULL,
   [RootId]                [uniqueidentifier]      NULL,
   [Status]                [tinyint]               NOT NULL,
   [OperationType]         [tinyint]               NOT NULL,
CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED 
(
   [IncrementId] ASC,
   [ProjectNoPartitionKey] ASC,
   [ProjectId] ASC
);

CREATE NONCLUSTERED INDEX [IX_ChangeLog_ProjectNoPartitionKey_ProjectId_EntityName_IncrementId_INCLUDE_All] ON [dbo].[ChangeLog]
(
   [ProjectNoPartitionKey] ASC,
   [ProjectId] ASC,
   [EntityName] ASC,
   [IncrementId] ASC
)
INCLUDE([EntityId],[RootId],[RevisionId],[Status],[OperationType],[CreatedBy],[CreatedOn]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF);

不幸的是，項目之間的日誌記錄表中的行數存在大量偏差。200 個分區中最大的 4 個分區佔日誌記錄表中所有行的 25%。因此，雖然它降低了超時頻率，但它們仍然經常發生，足以引起關注。該表的寫入頻率約為讀取頻率的 175 倍。

增加分區數量似乎不會有太大幫助。有人告訴我，在記憶體中製作日誌記錄表將是最好的解決方案，但我們都沒有使用過該功能。建議？

我肯定會建議修改您的 PK 以使用OPTIMIZE_FOR_SEQUENTIAL_KEY（正如Erik在評論中所建議的那樣。

啟用優化順序鍵非常簡單。這使它成為一個相對省力、低影響的起點。

ALTER INDEX PK_ChangeLog 
   ON dbo.ChangeLog SET(OPTIMIZE_FOR_SEQUENTIAL_KEY  = ON);

此功能是專門為解決您所看到的場景而設計的 - 因此，在重新集群表並與那些更努力、更高風險的更改進行鬥爭之前，首先使用專門建構的設置是正確的起點！不能保證它會是解決您問題的靈丹妙藥，但它應該會有所幫助。

SQL Server 產品組的 Pam Lahoud 寫了一篇關於該功能的精彩幕後文章。來自 Pam 的文章：

隨著最後一頁插入爭用，隨著插入執行緒數量的增加，頁面鎖存器的隊列增加，這反過來又增加了延遲。吞吐量也會降低，但如果某些東西減慢了持有閂鎖的執行緒之一，這可能會觸發護航，吞吐量會突然跌落懸崖。

…

使用此選項時，您可能不會看到 PAGELATCH 等待的減少，實際上您甚至可能會看到使用稱為 BTREE_INSERT_FLOW_CONTROL 的新等待類型的等待增加。儘管有這些等待，您應該能夠實現更好的吞吐量，並擴展到更多的並發執行緒，而不會碰到眾所周知的懸崖。

如果您在啟用該功能後仍然看到吞吐量有限，那麼您可能需要改變您的索引策略以消除當並發插入都要求寫入表末尾時的“最後一頁爭用”。

對於您的情況，這可能有點矯枉過正（即使您在 Azure 上，某些概念仍然適用），但如果您關心最快的 INSERT 可能，這是一個非常有趣的演講。

也看看他們的其他課程，你可能會在那裡找到有用的東西。

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