了解何時從查詢/計劃中刪除 Order By 或 Sort 運算符

雖然我正在閱讀並且根據理解，如果匹配索引以支持查詢具有以相同方式排序的鍵列，則需要避免 SQL 查詢中不需要的 ORDER BY。

對於以下數據庫測試模式-

CREATE PARTITION FUNCTION DemoPartitionFunction (datetime)
AS RANGE RIGHT
FOR VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -7),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -6),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -5),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -4),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -3),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -2),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), -1),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 0),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 1),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 2),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 3),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 4),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 5),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 6),
           DATEADD(dd, DATEDIFF(dd, 0, GETUTCDATE()), 7));

CREATE PARTITION SCHEME DemoPartitionScheme
AS PARTITION DemoPartitionFunction
ALL TO ([DEFAULT]);

CREATE TABLE [dbo].[DemoPartitionedTable](
   [DemoID] [int] IDENTITY(1,1) NOT NULL,
   [SomeData] [sysname] NOT NULL,
   [Lastseen] [datetime] NULL,
   [DataKey1] [char] NOT NULL,
   [DataKey2] [char] NOT NULL,
   [RandomColumn] [nvarchar] NOT NULL,
   [CaptureDate] [datetime] NULL,
   CONSTRAINT [PK_DemoPartitionedTable] UNIQUE NONCLUSTERED 
   (
       [DemoID] ASC,
       [CaptureDate] ASC
   )
   ON DemoPartitionScheme(CaptureDate)
) ON DemoPartitionScheme(CaptureDate);

如果我使用 ORDER BY 執行查詢

SELECT [DemoID], [CaptureDate]
FROM [dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
ORDER BY 
DemoID,
CaptureDate

以下是 IO 和 Time 的統計數據

（受影響的 95703 行）表“工作表”。掃描計數 0，邏輯讀取 0，物理讀取 0，預讀讀取 117，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。表 ‘DemoPartitionedTable’。掃描計數 16，邏輯讀取 330，物理讀取 9，預讀讀取 262，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

（受影響的 1 行）

SQL Server 執行時間：CPU 時間 = 297 毫秒，經過時間 = 750 毫秒。

根據我在上述場景中的理解，我們不需要排序，因為索引已經對這些列進行了排序，所以為什麼我會看到下面的錯誤計劃

所以如果我執行刪除上面的排序

SELECT [DemoID], [CaptureDate]
FROM 
[dbo].[DemoPartitionedTable]
WHERE CaptureDate>=CONVERT(datetime, '20190912', 112) AND
CaptureDate < CONVERT(datetime, '20191013', 112)
--ORDER BY 
--DemoID,
--CaptureDate

（受影響的 95703 行）表“DemoPartitionedTable”。掃描計數 16，邏輯讀取 330，物理讀取 134，預讀讀取 269，lob 邏輯讀取 0，lob 物理讀取 0，lob 預讀讀取 0。

（受影響的 1 行）

SQL Server 執行時間：CPU 時間 = 78 毫秒，經過時間 = 2791 毫秒。SQL Server 解析和編譯時間：CPU 時間 = 0 毫秒，執行時間 = 0 毫秒。

計劃沒有昂貴的排序，但統計數據最差，為什麼？

根據我在上述情況下的理解，我們不需要排序，因為索引已經對這些列進行了排序

這種理解是不正確的，該計劃說明了一個原因。並行索引掃描不按索引順序輸出行，因為每個執行緒在排序順序中的不同位置讀取。如果沒有 ORDER BY 子句，您不能期望任何特定順序的行。

並且索引沒有已經排序的行。ORDER BY 為 (DemoID, CaptureDate)，但索引按 CaptureDate 分區。因此，如果查詢跨越分區綁定，DemoID 值將重新開始。

例如 (DemoID, CaptureDate)-order 中的行：

(1,'20190912'),(2,'20190913'),(3,'20190912'),(4,'20190913')

可以儲存在多個分區上：

partition N
--------------
(1,'20190912')
(3,'20190912')

partition N+1
--------------
(2,'20190913')
(4,'20190913')

因此，即使計劃使用單個執行緒來掃描索引，也需要進行下游排序。

計劃沒有昂貴的排序，但統計數據最差，為什麼？

不，統計數據變得更好了。第二個查詢具有相同的 330 次邏輯讀取，CPU 時間僅為 78 毫秒，而第一個查詢的 CPU 時間為 297 毫秒。經過時間的差異與更多的並行性和物理 IO 有關，這不是查詢計劃的屬性。相反，它取決於您執行查詢時頁面記憶體的狀態。

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