連續處理時的索引碎片

SQL 伺服器 2005

我需要能夠在一個 900M 的記錄表中連續處理大約 350M 的記錄。我用來選擇要處理的記錄的查詢在處理時變得嚴重碎片化，我需要停止處理以重建索引。偽數據模型和查詢…

/**************************************/
CREATE TABLE [Table] 
(
   [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
   [ForeignKeyId] [INT] NOT NULL,
   /* more columns ... */
   [DataType] [CHAR](1) NOT NULL,
   [DataStatus] [DATETIME] NULL,
   [ProcessDate] [DATETIME] NOT NULL,
   [ProcessThreadId] VARCHAR (100) NULL
);

CREATE NONCLUSTERED INDEX [Idx] ON [Table] 
(
   [DataType],
   [DataStatus],
   [ProcessDate],
   [ProcessThreadId]
);
/**************************************/

/**************************************/
WITH cte AS (
   SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId]
   FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
   WHERE [DataType] = 'X'
   AND [DataStatus] IS NULL
   AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
   AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId;

SELECT * FROM [Table] WITH ( NOLOCK )
WHERE [ProcessThreadId] = @ProcessThreadId;
/**************************************/

數據內容…

而列

$$ DataType $$鍵入為 CHAR(1)，大約 35% 的記錄等於“X”，其餘記錄等於“A”。

只有記錄在哪裡$$ DataType $$等於 ‘X’，大約 10% 將有一個 NOT NULL$$ DataStatus $$價值。 這

$$ ProcessDate $$和$$ ProcessThreadId $$將為處理的每條記錄更新列。
這$$ DataType $$列更新（“X”更改為“A”）大約 10% 的時間。
這$$ DataStatus $$列的更新時間少於 1%。 現在我的解決方案是選擇所有記錄的主鍵來處理到一個單獨的處理表中。我在處理它們時刪除了鍵，以便在索引片段時處理更少的記錄。

但是，這不符合我想要的工作流程，以便連續處理這些數據，無需人工干預和大量停機時間。我確實預計每季度會因家務雜務而停機。但是現在，如果沒有單獨的處理表，我什至無法處理一半的數據集，而不會出現碎片變得嚴重到需要停止和重建索引的情況。

對於索引或不同的數據模型有什麼建議嗎？有我需要研究的模式嗎？

我完全控制了數據模型和流程軟體，所以沒有什麼是不可能的。

您正在做的是使用表格作為隊列。您的更新是出列方法。但是表上的聚集索引對於隊列來說是一個糟糕的選擇。使用表作為隊列實際上對錶設計提出了相當嚴格的要求。您的聚集索引必須是出隊順序，在這種情況下可能是([DataType], [DataStatus], [ProcessDate]). 您可以將主鍵實現為非聚集約束。刪除 non-clustered index Idx，因為聚集鍵發揮其作用。

另一個重要的難題是在處理過程中保持行大小不變。您已將 聲明ProcessThreadId為 a VARCHAR(100)，這意味著該行在“處理”時會增長和縮小，因為欄位值從 NULL 更改為非 null。行上的這種增長和收縮模式會導致頁面拆分和碎片。我無法想像一個執行緒 ID 是“VARCHAR(100)”。使用固定長度的類型，也許是INT.

附帶說明一下，您不需要分兩步出隊（UPDATE 後跟 SELECT）。您可以使用 OUTPUT 子句，如上面連結的文章中所述：

/**************************************/
CREATE TABLE [Table] 
(
   [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
   [ForeignKeyId] [INT] NOT NULL,
   /* more columns ... */
   [DataType] [CHAR](1) NOT NULL,
   [DataStatus] [DATETIME] NULL,
   [ProcessDate] [DATETIME] NOT NULL,
   [ProcessThreadId] INT NULL
);

CREATE CLUSTERED INDEX [Cdx] ON [Table] 
(
   [DataType],
   [DataStatus],
   [ProcessDate]
);
/**************************************/

declare @BatchSize int, @ProcessThreadId int;

/**************************************/
WITH cte AS (
   SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId] , ... more columns 
   FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
   WHERE [DataType] = 'X'
   AND [DataStatus] IS NULL
   AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
   AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId
OUTPUT DELETED.[PrimaryKeyId] , ... more columns ;
/**************************************/

此外，我會考慮將成功處理的項目移動到不同的存檔表中。您希望您的隊列表在零大小附近徘徊，您不希望它們增長，因為它們保留了不需要的舊條目的“歷史”。您也可以考慮將分區[ProcessDate]作為替代方案（即，一個目前活動分區充當隊列並儲存具有 NULL ProcessDate 的條目，另一個分區用於所有非空。或者如果您想實現高效，則為非空多個分區刪除（切換出）已超過規定保留期的數據。如果事情變熱，您可以[DataType]根據它是否具有足夠的選擇性進行分區，但該設計將非常複雜，因為它需要通過持久計算列（複合列）進行分區粘合在一起

$$ DataType $$和$$ ProcessingDate $$).

我首先將ProcessDateandProcessthreadid欄位移動到另一個表。

現在，您從這個相當廣泛的索引中選擇的每一行也需要更新。

如果將這兩個欄位移動到另一個表，主表上的更新量將減少 90%，這應該會處理大部分碎片。

在 NEW 表中仍然會有碎片，但在數據量少得多的窄表上管理起來會更容易。

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