單行 INSERT…SELECT 比單獨的 SELECT 慢得多

給定以下堆表，其中 400 行編號從 1 到 400：

DROP TABLE IF EXISTS dbo.N;
GO
SELECT 
   SV.number
INTO dbo.N 
FROM master.dbo.spt_values AS SV
WHERE 
   SV.[type] = N'P'
   AND SV.number BETWEEN 1 AND 400;

和以下設置：

SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

以下SELECT語句在大約6 秒內完成（展示、計劃）：

DECLARE @n integer = 400;

SELECT
   c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
   N.number <= @n
   AND N2.number <= @n
   AND N3.number <= @n
OPTION
   (OPTIMIZE FOR (@n = 1));

注意：@OPTIMIZE FOR該子句只是為了生成一個合理大小的重現，以擷取實際問題的基本細節，包括可能由於各種原因而出現的基數錯誤估計。

將單行輸出寫入表時，需要19 秒（展示、計劃）：

DECLARE @T table (c bigint NOT NULL);

DECLARE @n integer = 400;

INSERT @T
   (c)
SELECT
   c = COUNT_BIG(*) 
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE 
   N.number <= @n
   AND N2.number <= @n
   AND N3.number <= @n
OPTION
   (OPTIMIZE FOR (@n = 1));

除了插入一行之外，執行計劃看起來相同。

所有額外的時間似乎都被 CPU 使用所消耗。

為什麼INSERT語句這麼慢？

SQL Server 選擇使用行級鎖掃描循環連接內側的堆表。全掃描通常會選擇頁級鎖定，但表的大小和謂詞的組合意味著儲存引擎會選擇行鎖，因為這似乎是最便宜的策略。

基數錯誤估計是故意引入的OPTIMIZE FOR，即堆掃描的次數比優化器預期的要多得多，並且它不會像通常那樣引入假離線。

這種因素的組合意味著性能對執行時所需的鎖數量非常敏感。

當沒有讀取未送出數據的危險並且沒有行外數據時，該SELECT語句受益於允許跳過行級共享鎖（僅採用意圖共享的頁級鎖）的優化。

該INSERT...SELECT語句不會從這種優化中受益，因此在第二種情況下，每秒會獲取和釋放數百萬個 RID 鎖，以及意圖共享的頁面級鎖。

大量的鎖定活動導致了額外的 CPU 和執行時間。

最自然的解決方法是確保優化器（和儲存引擎）獲得合適的基數估計，以便他們做出正確的選擇。

如果這在實際案例中不實用，可以將INSERTandSELECT語句分開，並將結果SELECT保存在變數中。這將允許SELECT語句從鎖跳過優化中受益。

更改隔離級別也可以通過不使用共享鎖或確保快速進行鎖升級來實現。

SELECT作為最後的興趣點，通過使用未記錄的跟踪標誌 8691 強制使用假離線，可以使查詢執行得比優化情況更快。

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