由於 varchar(max) 將溢出排序到 tempdb

在 32GB 的伺服器上，我們正​​在執行最大記憶體為 25GB 的 SQL Server 2014 SP2，我們有兩個表，在這裡您可以找到兩個表的簡化結構：

CREATE TABLE [dbo].[Settings](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [resourceId] [int] NULL,
   [typeID] [int] NULL,
   [remark] [varchar](max) NULL,
   CONSTRAINT [PK_Settings] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Resources](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [resourceUID] [int] NULL,
CONSTRAINT [PK_Resources] PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY]
GO

具有以下非聚集索引：

CREATE NONCLUSTERED INDEX [IX_UID] ON [dbo].[Resources]
(
   [resourceUID] ASC
)

CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Settings]
(
   [resourceId] ASC,
   [typeID] ASC
)

數據庫配置為compatibility level120。

當我執行此查詢時，會有溢出到tempdb. 這就是我執行查詢的方式：

exec sp_executesql N'
select r.id,remark
FROM Resources r
inner join Settings on resourceid=r.id
where resourceUID=@UID
ORDER BY typeID',
N'@UID int',
@UID=38

如果不選擇該[remark]欄位，則不會發生溢出。我的第一反應是由於嵌套循環運算符上的估計行數較少而發生溢出。

因此，我將 5 個日期時間和 5 個整數列添加到設置表中，並將它們添加到我的選擇語句中。當我執行查詢時，沒有發生溢出。

為什麼只有在選擇時才會發生溢出[remark]？這可能與這是一個varchar(max). 我該怎麼做才能避免溢出tempdb？

添加OPTION (RECOMPILE)到查詢中沒有任何區別。

這裡將有幾種可能的解決方法。

您可以手動調整記憶體授予，儘管我可能不會走那條路。

在獲取最大長度列之前，您還可以使用 CTE 和 TOP 將排序降低。它看起來像下面這樣。

WITH CTE AS (
SELECT TOP 1000000000 r.ID, s.ID AS ID2, s.typeID
FROM Resources r
inner join Settings s on resourceid=r.id
where resourceUID=@UID
ORDER BY s.typeID
)
SELECT c.ID, ca.remark
FROM CTE c
CROSS APPLY (SELECT remark FROM dbo.Settings s WHERE s.id = c.ID2) ca(remark)
ORDER BY c.typeID

這裡是概念驗證 dbfiddle 。樣本數據仍將不勝感激！

如果您想閱讀 Paul White 的出色分析，請閱讀此處。

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