過多的排序記憶體授予

為什麼這個簡單的查詢會獲得如此多的記憶體？

-- Demo table
CREATE TABLE dbo.Test
(
   TID integer IDENTITY NOT NULL,
   FilterMe integer NOT NULL,
   SortMe integer NOT NULL,
   Unused nvarchar(max) NULL,

   CONSTRAINT PK_dbo_Test_TID
   PRIMARY KEY CLUSTERED (TID)
);
GO
-- 100,000 example rows
INSERT dbo.Test WITH (TABLOCKX)
   (FilterMe, SortMe)
SELECT TOP (100 * 1000)
   CHECKSUM(NEWID()) % 1000,
   CHECKSUM(NEWID())
FROM sys.all_columns AS AC1
CROSS JOIN sys.all_columns AS AC2;
GO    
-- Query
SELECT
   T.TID,
   T.FilterMe,
   T.SortMe,
   T.Unused
FROM dbo.Test AS T 
WHERE 
   T.FilterMe = 567
ORDER BY 
   T.SortMe;

對於估計的 50 行，優化器為排序保留了近 500 MB：

這是 SQL Server 中的一個錯誤（從 2008 年到 2014 年包括在內）。

我的錯誤報告在**這裡**。

過濾條件作為殘差謂詞被下推到掃描運算元中，但是根據過濾前基數估計錯誤地計算了為排序授予的記憶體。

為了說明這個問題，我們可以使用*（未​​記錄和不支持的）*跟踪標誌 9130 來防止過濾器被下推到掃描操作符中。授予排序的記憶體現在正確地基於過濾器輸出的估計基數，而不是掃描：

SELECT
   T.TID,
   T.FilterMe,
   T.SortMe,
   T.Unused
FROM dbo.Test AS T 
WHERE 
   T.FilterMe = 567
ORDER BY 
   T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

對於生產系統，需要採取措施避免有問題的計劃形狀（將過濾器推入掃描並在另一列上進行排序）。一種方法是提供關於過濾條件的索引和/或提供所需的排序順序。

-- Index on the filter condition only
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe
ON dbo.Test (FilterMe);

有了這個索引，排序所需的記憶體授予只有928KB：

更進一步，以下索引可以完全避免排序（零記憶體授予）：

-- Provides filtering and sort order
-- nvarchar(max) column deliberately not INCLUDEd
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe_SortMe
ON dbo.Test (FilterMe, SortMe);

在 SQL Server x64 開發人員版的以下版本上測試並確認錯誤：

2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

這已在SQL Server 2016 Service Pack 1中修復。發行說明包括以下內容：

VSTS 錯誤號 8024987

使用下推謂詞進行表掃描和索引掃描往往會高估父運算符的記憶體授予。

測試並確認固定在：

• Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
• Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

兩種 CE 型號。

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