為什麼在我的查詢計劃中 DistinctSort 後跟 TopNSort？

我有以下定義：

CREATE TABLE [dbo].[JobItems] (
   [ItemId]            UNIQUEIDENTIFIER NOT NULL,
   [ItemState]         INT              NOT NULL,
   [ItemCreationTime]  DATETIME         NULL DEFAULT GETUTCDATE(),
   [ItemPriority]      TINYINT          NOT NULL DEFAULT(0),
   [ItemRefreshTime]   DATETIME         NULL,
   -- lots of other columns
   CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
   ON [dbo].[JobItems]([ItemId] ASC);

CREATE INDEX [GetTaskToProcessIndex]
   ON [dbo].[JobItems]([ItemState], [ItemPriority], [ItemCreationTime])

和以下查詢：

SELECT TOP(1) ItemId FROM JobItems
WHERE ItemState = 5 OR
  ( ( ItemState = 11 ) AND ( DATEDIFF( SECOND, ItemRefreshTime, GETUTCDATE() ) > 14 ) )
ORDER BY ItemPriority ASC, ItemCreationTime ASC

我執行這個查詢並檢查實際的執行計劃，這是發生了什麼：

1. 完成索引搜尋以檢索帶有ItemState=5.
2. 完成索引查找以檢索項目，ItemState=11然後對每一行進行單獨的查找以檢索ItemRefreshTime，並使用嵌套循環過濾兩次查找的結果。
3. 集合 1 和 2 包含ItemId,ItemCreationTime和ItemPriority連接然後…
4. 神奇DistinctSort的事情發生在ORDER BY ItemId ASC最後
5. TopNSort發生在ORDER BY ItemPriority ASC, ItemCreationTime ASC

TopNSort並DistinctSort各取 32% 左右，所以我很樂意擺脫DistinctSort- 我什至不明白它的目的。

這是什麼神奇TopNSort有用的DistinctSort東西，為什麼會出現？

我可以通過在您的問題中執行 DDL 然後修改統計資訊來重現您在 SQL Server 2012（本地）上描述的計劃，以便 SQL Server 認為該表比實際大得多。

UPDATE STATISTICS [dbo].[JobItems] WITH ROWCOUNT = 10000000, pagecount = 10000000

然後使用OPTION (MAXDOP 1, CONCAT UNION, ORDER GROUP).

這是一個索引聯合計劃。連接運算符實現UNION ALL. Distinct Sort 將語義更改為UNION操作以防止多次返回同一行。（如果表沒有索引鍵作為唯一的行標識符，則在此處使用物理刪除以避免錯誤地對碰巧具有相同列值的不同行進行重複數據刪除）

下面的查詢中可能需要這樣做的一個範例。（注意這兩個參數設置為相同的值，因此索引聯合計劃將搜尋相同的行兩次）

DECLARE @ItemState1   INT = 5
       , @ItemState2 INT = 5

SELECT ItemId
FROM   JobItems
WHERE  ItemState = @ItemState1
       OR ( ( ItemState = @ItemState2 )
            AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )

Top N Sort 然後對數據進行重新排序以實現TOP 1.

在您的情況下，出於多種原因，邏輯上不需要區分排序。ItemState = 5和ItemState = 11分支是互斥的（這可以在編譯時確定），另外即使TOP 1 ... ORDER BY ItemPriority ASC, ItemCreationTime ASC存在錯誤的重複，語義也不會受到影響。

編寫查詢的另一種方法（使用索引提供更好的計劃以避免任何排序）是

SELECT TOP(1) ItemId
FROM   (SELECT ItemId,
              ItemPriority,
              ItemCreationTime
       FROM   JobItems
       WHERE  ItemState = 5
       UNION ALL
       SELECT ItemId,
              ItemPriority,
              ItemCreationTime
       FROM   JobItems
       WHERE  ( ( ItemState = 11 )
                AND ( DATEDIFF(SECOND, ItemRefreshTime, GETUTCDATE()) > 14 ) )) T
ORDER  BY ItemPriority ASC,
         ItemCreationTime ASC 

ItemRefreshTime如果實際上在找到滿足殘差謂詞的單行之前可能需要相當多的內容，您可以考慮將其作為包含列添加到索引中以避免鍵查找。

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