基數估計差會使 INSERT 從最小日誌記錄中失格嗎？

為什麼第二個INSERT語句比第一個慢約 5 倍？

從生成的日誌數據量來看，我認為第二個不符合最小日誌記錄的條件。但是，Data Loading Performance Guide中的文件表明兩個插入都應該能夠被最小化記錄。因此，如果最小日誌記錄是關鍵的性能差異，為什麼第二個查詢不符合最小日誌記錄的條件？可以做些什麼來改善這種情況？

查詢 #1：使用 INSERT…WITH (TABLOCK) 插入 5MM 行

考慮以下查詢，它將 5MM 行插入到堆中。此查詢執行1 second並生成64MB由 報告的事務日誌數據sys.dm_tran_database_transactions。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that correctly estimates that it will generate 5MM rows
FROM dbo.fiveMillionNumbers
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

查詢 #2：插入相同的數據，但 SQL 低估了行數

現在考慮這個非常相似的查詢，它對完全相同的數據進行操作，但碰巧是從SELECT基數估計太低的表（或在我的實際生產案例中具有許多連接的複雜語句）中提取的。此查詢在事務日誌數據中執行5.5 seconds並生成461MB。

CREATE TABLE dbo.minimalLoggingTest (n INT NOT NULL)
GO
INSERT INTO dbo.minimalLoggingTest WITH (TABLOCK) (n)
SELECT n
-- Any table/view/sub-query that produces 5MM rows but SQL estimates just 1000 rows
FROM dbo.fiveMillionNumbersBadEstimate
-- Provides greater consistency on my laptop, where other processes are running
OPTION (MAXDOP 1)
GO

完整腳本

請參閱此 Pastebin以獲取完整的腳本集以生成測試數據並執行這些場景中的任何一個。請注意，您必須使用處於SIMPLE 恢復模式的數據庫。

業務環境

我們半頻繁地移動數百萬行數據，重要的是讓這些操作盡可能高效，無論是在執行時間還是磁碟 I/O 負載方面。我們最初的印像是創建一個堆表並使用INSERT...WITH (TABLOCK)是一個很好的方法，但現在我們在實際生產場景中觀察到上面展示的情況（儘管查詢更複雜，而不是此處為簡化版）。

為什麼第二個查詢不符合最小日誌記錄的條件？

最小日誌記錄可用於第二個查詢，但引擎選擇在執行時不使用它。

有一個最小門檻值，INSERT...SELECT低於該門檻值它選擇不使用批量載入優化。設置批量行集操作涉及成本，並且僅批量插入幾行不會導致有效的空間利用。

可以做些什麼來改善這種情況？

SELECT INTO使用沒有此門檻值的許多其他方法之一（例如）。或者，您可能能夠以某種方式重寫源查詢，以將估計的行數/頁數提高到超過INSERT...SELECT.

有關更多有用資訊，另請參閱Geoff 的自我回答。

---

可能有趣的瑣事： 僅在不使用批量載入優化時SET STATISTICS IO報告目標表的邏輯讀取。

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