使用表值建構子將多個值插入到具有聚集索引的表中時，預排序是否有價值？

這與這個問題有關：Efficient INSERT INTO a Table With Clustered Index

從那個問題來看，聽起來 SQL Server 有時會在將多個值插入到具有聚集索引的表中以減少碎片時對數據進行預排序。當我使用表值構造函式在插入語句中插入多個值時，SQL Server 是否也會這樣做？

例如，如果我有一個event_log聚集在一timestamp列上的表，並且我插入如下數據：

INSERT INTO event_log (timestamp, data)
VALUES
('03:00:02', 'data string...'),
('03:00:01', 'data string...'),
('03:00:00', 'data string...')

SQL Server 是否會為我預先排序以確保數據有效進入而不會產生碎片，或者我是否應該在生成 SQL 之前對我的應用程序中的數據進行排序？

我已經建構了一個測試平台來看看會發生什麼：

USE tempdb;

CREATE TABLE dbo.TestSort
(
   Sorted INT NOT NULL
       CONSTRAINT PK_TestSort
       PRIMARY KEY CLUSTERED
   , SomeData VARCHAR(2048) NOT NULL
);

INSERT INTO dbo.TestSort (Sorted, SomeData)
VALUES  (1797604285, CRYPT_GEN_RANDOM(1024))
   , (1530768597, CRYPT_GEN_RANDOM(1024))
   , (1274169954, CRYPT_GEN_RANDOM(1024))
   , (-1972758125, CRYPT_GEN_RANDOM(1024))
   , (1768931454, CRYPT_GEN_RANDOM(1024))
   , (-1180422587, CRYPT_GEN_RANDOM(1024))
   , (-1373873804, CRYPT_GEN_RANDOM(1024))
   , (293442810, CRYPT_GEN_RANDOM(1024))
   , (-2126229859, CRYPT_GEN_RANDOM(1024))
   , (715871545, CRYPT_GEN_RANDOM(1024))
   , (-1163940131, CRYPT_GEN_RANDOM(1024))
   , (566332020, CRYPT_GEN_RANDOM(1024))
   , (1880249597, CRYPT_GEN_RANDOM(1024))
   , (-1213257849, CRYPT_GEN_RANDOM(1024))
   , (-155893134, CRYPT_GEN_RANDOM(1024))
   , (976883931, CRYPT_GEN_RANDOM(1024))
   , (-1424958821, CRYPT_GEN_RANDOM(1024))
   , (-279093766, CRYPT_GEN_RANDOM(1024))
   , (-903956376, CRYPT_GEN_RANDOM(1024))
   , (181119720, CRYPT_GEN_RANDOM(1024))
   , (-422397654, CRYPT_GEN_RANDOM(1024))
   , (-560438983, CRYPT_GEN_RANDOM(1024))
   , (968519165, CRYPT_GEN_RANDOM(1024))
   , (1820871210, CRYPT_GEN_RANDOM(1024))
   , (-1348787729, CRYPT_GEN_RANDOM(1024))
   , (-1869809700, CRYPT_GEN_RANDOM(1024))
   , (423340320, CRYPT_GEN_RANDOM(1024))
   , (125852107, CRYPT_GEN_RANDOM(1024))
   , (-1690550622, CRYPT_GEN_RANDOM(1024))
   , (570776311, CRYPT_GEN_RANDOM(1024))
   , (2120766755, CRYPT_GEN_RANDOM(1024))
   , (1123596784, CRYPT_GEN_RANDOM(1024))
   , (496886282, CRYPT_GEN_RANDOM(1024))
   , (-571192016, CRYPT_GEN_RANDOM(1024))
   , (1036877128, CRYPT_GEN_RANDOM(1024))
   , (1518056151, CRYPT_GEN_RANDOM(1024))
   , (1617326587, CRYPT_GEN_RANDOM(1024))
   , (410892484, CRYPT_GEN_RANDOM(1024))
   , (1826927956, CRYPT_GEN_RANDOM(1024))
   , (-1898916773, CRYPT_GEN_RANDOM(1024))
   , (245592851, CRYPT_GEN_RANDOM(1024))
   , (1826773413, CRYPT_GEN_RANDOM(1024))
   , (1451000899, CRYPT_GEN_RANDOM(1024))
   , (1234288293, CRYPT_GEN_RANDOM(1024))
   , (1433618321, CRYPT_GEN_RANDOM(1024))
   , (-1584291587, CRYPT_GEN_RANDOM(1024))
   , (-554159323, CRYPT_GEN_RANDOM(1024))
   , (-1478814392, CRYPT_GEN_RANDOM(1024))
   , (1326124163, CRYPT_GEN_RANDOM(1024))
   , (701812459, CRYPT_GEN_RANDOM(1024));

第一列是主鍵，如您所見，這些值以隨機（ish）順序列出。以隨機順序列出值應該使 SQL Server ：

1. 對數據進行排序，預插入
2. 不對數據進行排序，導致表碎片化。

該CRYPT_GEN_RANDOM()函式用於每行生成 1024 字節的隨機數據，以允許這張表消耗多個頁面，從而讓我們看到碎片插入的效果。

執行上述插入後，您可以像這樣檢查碎片：

SELECT * 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TestSort'), 1, 0, 'SAMPLED') ips;

在我的 SQL Server 2012 Developer Edition 實例上執行它顯示平均碎片率為 90%，這表明 SQL Server 在插入期間沒有排序。

這個特定故事的寓意可能是，“當有疑問時，排序，如果它是有益的”。話雖如此，向插入語句添加 andORDER BY子句並不能保證插入將按該順序發生。例如，考慮如果插入平行會發生什麼。

在非生產系統上，您可以在插入語句中使用跟踪標誌 2332 作為選項來“強制”SQL Server 在插入之前對輸入進行排序。 @PaulWhite有一篇有趣的文章，Optimizing T-SQL queries that change data , and other details。請注意，該跟踪標誌不受支持，不應在生產系統中使用，因為這可能會使您的保修失效。在非生產系統中，為了您自己的教育，您可以嘗試將其添加到INSERT語句的末尾：

OPTION (QUERYTRACEON 2332);

將其附加到插入後，查看計劃，您將看到一個明確的排序：

如果 Microsoft 將此作為受支持的跟踪標誌，那就太好了。

Paul White 讓我意識到，當 SQL Server認為排序運算符會有所幫助時，它會自動將排序運算符引入計劃中*。對於上面的範例查詢，如果我在子句中使用 250 個項目執行插入values，則不會自動實現排序。但是，對於 251 項，SQL Server 會在插入之前自動對值進行排序。為什麼截止是 250/251 行對我來說仍然是一個謎，除了它似乎是硬編碼的。如果我將插入SomeData列中的數據大小減少到僅一個字節，則截斷仍然*是250/251 行，即使兩種情況下的表大小都只是一頁。有趣的是，查看插入SET STATISTICS IO, TIME ON;顯示帶有單個字節的插入SomeData排序時值需要兩倍的時間。

沒有排序（即插入 250 行）：

SQL Server 解析和編譯時間：
CPU 時間 = 0 毫秒，經過的時間 = 0 毫秒。
SQL Server 解析和編譯時間：
CPU 時間 = 16 毫秒，經過時間 = 16 毫秒。
SQL Server 解析和編譯時間：
CPU 時間 = 0 毫秒，經過的時間 = 0 毫秒。
表“測試排序”。掃描計數 0，邏輯讀取 501，物理讀取 0，
預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob
預讀讀數為 0。

（250 行受影響）

（1 行受影響）

SQL Server 執行時間：
CPU 時間 = 0 毫秒，經過的時間 = 11 毫秒。

使用排序（即插入 251 行）：

SQL Server 解析和編譯時間：
CPU 時間 = 0 毫秒，經過的時間 = 0 毫秒。
SQL Server 解析和編譯時間：
CPU 時間 = 15 毫秒，經過的時間 = 17 毫秒。
SQL Server 解析和編譯時間：
CPU 時間 = 0 毫秒，經過的時間 = 0 毫秒。
表“測試排序”。掃描計數 0，邏輯讀取 503，物理讀取 0，
預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob
預讀讀數為 0。
表“工作台”。掃描計數 0，邏輯讀取 0，物理讀取 0，
預讀讀取 0，lob 邏輯讀取 0，lob 物理讀取 0，lob
預讀讀數為 0。

（251 行受影響）

（1 行受影響）

SQL Server 執行時間：
CPU 時間 = 16 毫秒，經過時間 = 21 毫秒。

一旦你開始增加行大小，排序後的版本肯定會變得更有效率。將 4096 字節插入SomeData時，在我的測試台上，排序插入的速度幾乎是未排序插入的兩倍。

---

作為旁注，如果您有興趣，我VALUES (...)使用此 T-SQL 生成了該子句：

;WITH s AS (
   SELECT v.Item
   FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Item)
)
, v AS (
   SELECT Num = CONVERT(int, CRYPT_GEN_RANDOM(10), 0)
)
, o AS (
   SELECT v.Num
       , rn = ROW_NUMBER() OVER (PARTITION BY v.Num ORDER BY NEWID())
   FROM s s1
       CROSS JOIN s s2
       CROSS JOIN s s3
       CROSS JOIN v 
)
SELECT TOP(50) ', (' 
       + REPLACE(CONVERT(varchar(11), o.Num), '*', '0') 
       + ', CRYPT_GEN_RANDOM(1024))'
FROM o
WHERE rn = 1
ORDER BY NEWID();

這會生成 1,000 個隨機值，僅選擇第一列中具有唯一值的前 50 行。我將輸出複制並粘貼到上面的INSERT語句中。

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