如何調查 BULK INSERT 語句的性能？
我主要是使用實體框架 ORM 的 .NET 開發人員。但是，因為我不想在使用 ORM 時失敗，所以我試圖了解數據層（數據庫）中發生的情況。基本上，在開發過程中，我啟動分析器並檢查程式碼的某些部分根據查詢生成了什麼。
如果我發現一些非常複雜的東西（ORM 甚至可以從相當簡單的 LINQ 語句中生成糟糕的查詢，如果不仔細編寫的話）和/或繁重的（持續時間、CPU、頁面讀取），我會將其放入 SSMS 並檢查其執行計劃。
它適用於我的數據庫知識水平。但是， BULK INSERT 似乎是一種特殊的生物，因為它似乎不會產生 SHOWPLAN。
我將嘗試說明一個非常簡單的範例：
表定義
CREATE TABLE dbo.ImportingSystemFileLoadInfo ( ImportingSystemFileLoadInfoId INT NOT NULL IDENTITY(1, 1) CONSTRAINT PK_ImportingSystemFileLoadInfo PRIMARY KEY CLUSTERED, EnvironmentId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo REFERENCES dbo.Environment, ImportingSystemId INT NOT NULL CONSTRAINT FK_ImportingSystemFileLoadInfo_ImportingSystem REFERENCES dbo.ImportingSystem, FileName NVARCHAR(64) NOT NULL, FileImportTime DATETIME2 NOT NULL, CONSTRAINT UQ_ImportingSystemImportInfo_EnvXIs_TableName UNIQUE (EnvironmentId, ImportingSystemId, FileName, FileImportTime) )**注意：**表上沒有定義其他索引
批量插入 （我在分析器中擷取的內容，僅一批）
insert bulk [dbo].[ImportingSystemFileLoadInfo] ([EnvironmentId] Int, [ImportingSystemId] Int, [FileName] NVarChar(64) COLLATE Latin1_General_CI_AS, [FileImportTime] DateTime2(7))指標
- 已插入 695 項
- 中央處理器 = 31
- 讀取 = 4271
- 寫入 = 24
- 持續時間 = 154
- 總表數 = 11500
對於我的應用程序，沒關係，雖然讀取看起來相當大（我對 SQL Server 內部知識知之甚少，所以我比較了 8K 頁面大小和我擁有的小記錄資訊）
**問題：**如何調查此 BULK INSERT 是否可以優化？或者它沒有任何意義，因為它可以說是將大數據從客戶端應用程序推送到 SQL Server 的最快方式？
據我所知，您可以以與優化正常插入非常相似的方式優化批量插入。通常，簡單插入的查詢計劃資訊量不大，因此不必擔心沒有計劃。我將介紹幾種優化插入的方法，但其中大多數可能不適用於您在問題中指定的插入。但是，如果您將來需要載入大量數據，它們可能會有所幫助。
1.按分群鍵順序插入數據
SQL Server 通常會在將數據插入具有聚集索引的表之前對其進行排序。對於某些表和應用程序，您可以通過對平面文件中的數據進行排序並讓 SQL Server 知道數據是通過以下
ORDER參數排序的，從而提高性能BULK INSERT：訂單（{列
$$ ASC | DESC $$}$$ ,… n $$) 指定數據文件中的數據如何排序。如果要導入的數據根據表上的聚集索引（如果有）進行排序，則可以提高批量導入性能。
由於您使用
IDENTITY列作為聚集鍵，因此您無需擔心這一點。2.
TABLOCK盡可能使用如果保證只有一個會話向表中插入數據，則可
TABLOCK以為BULK INSERT. 這可以減少鎖爭用，並在某些情況下導致最少的日誌記錄。但是，您要插入到具有已包含數據的聚集索引的表中，因此如果沒有跟踪標誌 610，您將不會獲得最少的日誌記錄，這將在本答案後面提到。如果
TABLOCK不可能，因為你不能改變程式碼，並不是所有的希望都失去了。考慮使用sp_table_option：EXEC [sys].[sp_tableoption] @TableNamePattern = N'dbo.BulkLoadTable' , @OptionName = 'table lock on bulk load' , @OptionValue = 'ON'另一種選擇是啟用跟踪標誌 715。
3. 使用適當的批量大小
有時您可以通過更改批量大小來調整插入。
ROWS_PER_BATCH = rows_per_batch
表示數據文件中數據的大致行數。
預設情況下，數據文件中的所有數據作為單個事務發送到伺服器，並且查詢優化器不知道批處理中的行數。如果您指定 ROWS_PER_BATCH（值 > 0），則伺服器使用此值來優化批量導入操作。為 ROWS_PER_BATCH 指定的值應與實際行數大致相同。有關性能注意事項的資訊，請參閱本主題後面的“備註”。
以下是文章後面的引述：
如果單個批次中要刷新的頁面數量超過內部門檻值，則可能會發生緩衝池的完整掃描，以確定在批次送出時要刷新哪些頁面。此完整掃描可能會損害批量導入性能。當大型緩衝池與慢速 I/O 子系統結合使用時，可能會出現超過內部門檻值的情況。為避免大型機器上的緩衝區溢出，請不要使用 TABLOCK 提示（這將刪除批量優化）或使用較小的批量大小（保留批量優化）。
由於電腦各不相同，我們建議您使用數據負載測試各種批量大小，以找出最適合您的方法。
就我個人而言，我只會在一個批次中插入所有 695 行。但是，在插入大量數據時，調整批量大小會產生很大的不同。
4.確保您需要該
IDENTITY列我對您的數據模型或要求一無所知，但不要陷入為
IDENTITY每個表添加列的陷阱。Aaron Bertrand 有一篇關於這方面的文章，稱為要改掉的壞習慣：在每個表上放置一個 IDENTITY 列。需要明確的是，我並不是說您應該IDENTITY從該表中刪除該列。但是，如果您確定IDENTITY不需要該列並將其刪除，這可能會提高插入性能。5.禁用索引或約束
如果您將大量數據載入到表中而不是已有的數據，那麼在載入之前禁用索引或約束並在載入之後啟用它們可能會更快。對於大量數據，SQL Server 一次建構索引而不是將數據載入到表中通常效率較低。看起來您將 695 行插入到一個有 11500 行的表中，所以我不推薦這種技術。
6. 考慮 TF 610
跟踪標誌 610 允許在一些附加場景中進行最少的日誌記錄。對於具有
IDENTITY聚集鍵的表，只要您的恢復模型是簡單的或批量記錄的，您將獲得對任何新數據頁的最少記錄。我相信此功能預設情況下未啟用，因為它可能會降低某些系統的性能。在啟用此跟踪標誌之前，您需要仔細測試。推薦的 Microsoft 參考似乎仍然是The Data Loading Performance Guide跟踪標誌 610 下最小日誌記錄的 I/O 影響
當您送出一個記錄最少的批量載入事務時，所有載入的頁面必須在送出完成之前刷新到磁碟。任何未被早期檢查點操作擷取的刷新頁面都會產生大量隨機 I/O。將此與完全記錄的操作進行對比，後者在日誌寫入時創建順序 I/O，並且不需要在送出時將載入的頁面刷新到磁碟。
如果您的負載場景是不跨越檢查點邊界的 btree 上的小型插入操作，並且您的 I/O 系統速度較慢，那麼使用最少的日誌記錄實際上會降低插入速度。
據我所知，這與跟踪標誌 610 無關，而是與最少的日誌記錄本身有關。我相信早先關於
ROWS_PER_BATCH調整的引述也涉及到了同樣的概念。總之，您可能無法調整
BULK INSERT. 我不會擔心您在插入時觀察到的讀取計數。每當您插入數據時，SQL Server 都會報告讀取。考慮以下非常簡單的問題INSERT：DROP TABLE IF EXISTS X_TABLE; CREATE TABLE X_TABLE ( VAL VARCHAR(1000) NOT NULL ); SET STATISTICS IO, TIME ON; INSERT INTO X_TABLE WITH (TABLOCK) SELECT REPLICATE('Z', 1000) FROM dbo.GetNums(10000); -- generate 10000 rows輸出
SET STATISTICS IO, TIME ON：表’X_TABLE’。掃描計數 0，邏輯讀取 11428
我報告了 11428 次讀取，但這不是可操作的資訊。有時可以通過最少的日誌記錄來減少報告的讀取次數，但當然不能將差異直接轉化為性能提升。