最大化批量數據處理的吞吐量

我正在使用 SQL Server 版本 14.0.3035.2

我有一堆資訊需要從表 A 中處理並將修改後的數據插入到表 B 中。我編寫了一個應用程序來查詢數據，進行一些二進制操作，並將結果插入到目標數據庫中。

我確信放緩是在閱讀期間。

當我第一次開始執行該應用程序時，它非常快。在執行的後半部分，它慢得像爬行。區別在於 Source 中 IsFetched = 1 位的行數和目標表中的行數

兩個表共享一個驅動器用於數據文件，也共享一個驅動器用於日誌文件（日誌專用磁碟，數據專用磁碟）

為了最大化我的吞吐量，我讓應用程序並行執行批處理。我獲取數據並將其標記為“已觸及”的查詢如下所示

UPDATE TOP(100) _s
           SET IsFetched = 1
           OUTPUT
               INSERTED.[Id],
               INSERTED.[BinaryData]
           FROM Source _s
           where _s.IsFetched = 0

我正在讀取的表具有架構

CREATE TABLE [dbo].[Source](
[SourceID] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL,
[IsFetched] [bit] NOT NULL,


CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED 
(
   [SourceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Source] ADD  CONSTRAINT [DF_Source_IsFetched]  DEFAULT ((0)) FOR [IsFetched]
GO

我在 IsFetched 上有一個非聚集索引。

CREATE NONCLUSTERED INDEX [Idx_Fetched] ON [dbo].[---]
(
   [IsFetched] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

我正在用架構寫一個表

CREATE TABLE [dbo].[Target](
[SourceId] [int] NOT NULL,
[BinaryData] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

正如預期的那樣，獲取數據時存在鎖定。但是，獲取數據所需的時間似乎比需要的要長得多。我以前使用過這種模式並實現了更高的吞吐量（每秒數千行）。現在我的最大吞吐量約為每秒 200-300 行。二進制數據不是那麼大，所以我認為這不是一次讀取太多數據的問題。

我發現更改並行度和批量大小對提高速度沒有太大作用，但我能得到的最快速度是大約 20 度的並行度，每個事務 10 行。

我的表似乎並沒有太碎片化，但我的磁碟似乎是罪魁禍首

我應該認為您的工作量根本沒有使用 IsFetched 上的索引。每個連續的 UPDATE 將從表的開頭開始並向前讀取，直到它處理了 100 行（您的 TOP 值）。隨後的迭代必須讀取先前獲取的行以獲取新行。從概念上講，第一次迭代讀取 100 個新行並更新它們。第二次迭代讀取前 100 行並跳過它們，然後讀取並處理 100 個新行。第三個讀取這 200 行，跳過它們，讀取 100 個新行並處理它們，依此類推。

為什麼查詢不使用索引？查詢執行計劃是在首次送出查詢時生成的。此時表中的所有行的 IsFetched = 0。因此使用索引無助於區分要處理的行和要跳過的行。實際上，使用該索引將是一種成本，因為執行必須為其他列的值讀取其他結構。優化器無法解釋此 UPDATE 將連續執行多次這一事實。

解決方案是使用基於 SourceID 的不同架構，該架構是唯一的（並且是集群的）。在每次迭代中儲存已處理的 SourceID 的最大值，然後從該值開始下一次迭代。

首先是一些設置和測試數據

drop table if exists Source;
drop table if exists Target;
go

create table Source(SourceID int primary key);
create table Target(SourceID int primary key);
go

insert Source(SourceID) values (1), (2), (3), (4), (5), (6), (7), (8), (9);
go

我將展示要迭代的程式碼，然後再對其進行描述。

declare @batch int = 4;      -- for 9 test rows this gives me 2 full + 1 partial batches
declare @highest int = -1;   -- set to any value lower than what is in your data
declare @var table (SourceID int primary key clustered);
declare @c int = @batch;     -- essentially a flag showing if the previous
                            -- iteration found rows


while @c >= @batch    -- stop once a not-full batch is processed as that
                     -- marks the end of the data
begin
   print 'Highest: ' + cast(@highest as varchar(99));  -- debug

   insert top (@batch)
   into target(SourceID)
   output
       inserted.[SourceID]
       into @var
   select
       SourceID
   from Source
   where SourceID > @highest
   order by SourceID;    -- important to order so each iteration is guaranteed to get
                         -- a contiguous block

   set @c = @@ROWCOUNT;  -- flag whether to iterate

   -- select * from Target;    -- interesting for debug, do NOT use in production!

   set @highest = (select max(SourceID) from @var);
end    -- end iteration

輸出消息是

Highest: -1              -- this is the start with the dummy initial value

(4 rows affected)        -- we find rows 1, 2, 3 & 4 and process them
Highest: 4               -- start the second iteration from "> 4"

(4 rows affected)        -- rows 5, 6, 7 & 8 done
Highest: 8               -- third iteration from "> 8"

(1 row affected)         -- only row 9 left.

假設鍵列 (SourceID) 上有一個索引。每次迭代都會執行並索引查找到最後一次停止的位置，然後從那裡掃描葉頁，讀取 @batch 行。因此，如果索引是覆蓋或者是聚集索引（根據定義是覆蓋），它顯然效果最好。如果沒有索引，這將恢復為表掃描，並且您不會比以前更好。

我在@var 上放了一個索引。插入將按照 slustered 順序進行，因此不會出現頁面拆分。MAX() 查詢將是單行查找，這很好。如果可以的話，非持久記憶體表也可以很好地發揮這個作用。

為了便於調試，我將 @batch 設為變數。它可能是硬編碼的。

變數@c 實際上是一個標誌。定義為整數並像我一樣設置它可以避免不必要的 IF 語句。

在 SELECT 上有一個 ORDER BY 很重要。沒有它，我們不能保證返回的行是相鄰的。例如，給定上面的數據和@highest = -1，第 3、5、7 和 9 行滿足 WHERE 子句，這不是我們想要的。

SourceID 值不要求是連續的。如果序列中存在漏洞，如果數據已被刪除，則可以。

一旦迭代處理的行數少於所需的行數，我就會停止。

請注意，此操作不需要 IsFetched。如果這是它的唯一目的，它可以從模式中刪除。

這裡有一篇很好的文章，包括對一個大表進行基準測試。Swart 的解決方案預先讀取以查找批處理的結尾，然後在第二條語句中處理該批處理。Mine 在處理批次後追溯確定批次的結束。他掃描源表兩次，我的一次加上索引查找。他的解決方案將始終在表末尾執行“無操作”迭代，當表基數是批量大小的精確倍數時。我認為我的效率稍高一些，但在實際工作負載中您不太可能注意到。

如果表源不斷地填充新行並且在執行之間沒有被截斷，您可以在執行結束時將 @highest 的值保存到表中，並在下次執行期間從該表中填充它。如果未來的執行可以插入低於目前最大值的 SourceID 值，或者如果存在並發寫入，則可以調整算法以適應。

我的經驗是，幾千行的批處理大小可以在吞吐量、阻塞和資源消耗之間取得最佳平衡。當然，在您的環境中進行測試，看看什麼適合您。

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