我正在计划和测试新的 SQL Server 2022 安装，以从较旧的 2014 年数据库迁移。我在性能测试期间注意到与一些庞大的查询存在一些主要差异。
这是我用于测试的查询：
INSERT INTO @volume
SELECT max(SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4)) AS TRADEYEAR,
sum(CASE [CANCEL] WHEN '1' THEN - cast(abs([TOT_QTY]) as bigint)
ELSE cast(abs([TOT_QTY]) as bigint) END) AS TOT_QTY,0
FROM dbo.CLEACUM LEFT OUTER JOIN
dbo.IBM_SECMASTER ON dbo.CLEACUM.TDE_SYMBOL = dbo.IBM_SECMASTER.DCS_CUSIP LEFT OUTER JOIN
dbo.VOL_IBM_CODES_IDA ON dbo.IBM_SECMASTER.SEC_CLASS >= dbo.VOL_IBM_CODES_IDA.NUMMIN AND
dbo.IBM_SECMASTER.SEC_CLASS <= dbo.VOL_IBM_CODES_IDA.NUMMAX AND dbo.IBM_SECMASTER.SEC_TYPE >= dbo.VOL_IBM_CODES_IDA.TYPEMIN AND
dbo.IBM_SECMASTER.SEC_TYPE <= dbo.VOL_IBM_CODES_IDA.TYPEMAX AND dbo.VOL_IBM_CODES_IDA.DATEMAX >= ABS(CONVERT(float, DATEDIFF(day,
dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25)) AND dbo.VOL_IBM_CODES_IDA.DATEMIN <= ABS(CONVERT(float, DATEDIFF(day,
dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25)) LEFT OUTER JOIN
dbo.VOL_CUSIPEquivalence ON SUBSTRING(dbo.CLEACUM.TDE_SYMBOL, 1, 6) = dbo.VOL_CUSIPEquivalence.CUSIP LEFT OUTER JOIN
dbo.VOL_MaturityLabels ON dbo.VOL_MaturityLabels.DATEMAX >= ABS(CONVERT(float, DATEDIFF(day, dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE)
/ 365.25)) AND dbo.VOL_MaturityLabels.DATEMIN <= ABS(CONVERT(float, DATEDIFF(day, dbo.CLEACUM.MATURITY_DATE, dbo.CLEACUM.TRADE_DATE) / 365.25))
LEFT OUTER JOIN VOL_BusinessDaysPerYear on left(CLEACUM.TRADE_DATE,4)=[VOL_BusinessDaysPerYear].YEAR
WHERE (dbo.CLEACUM.ACCT_NO LIKE '10%') AND (dbo.CLEACUM.SEC_TYPE < 300) AND (dbo.CLEACUM.ACCT_NO NOT IN
(SELECT ACCOUNT_NO
FROM dbo.VOL_ExcludedAccounts)) AND (SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4) > datepart(yyyy,getdate())-5)
and (cast(TRADE_DATE as date)<= VOLUME_SAME_PERIOD_DAY)
group by SUBSTRING(dbo.CLEACUM.TRADE_DATE, 1, 4)
CLEACUM 桌子相当大。3.2GB 磁盘空间上大约有 17M 行。没什么疯狂的，但对于扫描整个表的这样的查询来说仍然很大。
当我在 2014 年运行、全新启动或运行数天时，查询最多 30 秒完成。当我在 2022 年跑步时，需要 50 多分钟才能完成。下一次运行将在 30 秒内运行完成。该DBCC DROPCLEANBUFFERS命令还将使其再次运行缓慢，而无需重新启动或长时间等待。
从故障排除中，我可以看到 IO 从磁盘加载表（从 mdf 文件读取 ~1MB），我认为这是延迟，后续运行将使用缓存中的数据。我对此表示同意，但是...
2014年怎么没有做同样的事情呢？如果我在 2014 年机器重新启动后运行相同的查询，它仍然会在 30 秒或更短的时间内运行！但到了 2022 年，它需要从磁盘加载，然后才能使用缓存。测量时两者都给出了相同的统计数据：
Table '#B59594BD'. Scan count 0, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_MaturityLabels'. Scan count 1, logical reads 192958, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 1746, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 14, logical reads 2872, physical reads 338, read-ahead reads 2534, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_CUSIPEquivalence'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_IBM_CODES_IDA'. Scan count 1, logical reads 1736622, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'IBM_SECMASTER'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_ExcludedAccounts'. Scan count 1, logical reads 195636, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CLEACUM'. Scan count 1, logical reads 1040096, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VOL_BusinessDaysPerYear'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 24008 ms, elapsed time = 28572 ms.
Table '#AAEDD858'. Scan count 0, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_MaturityLabels'. Scan count 1, logical reads 192958, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 1746, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Workfile'. Scan count 14, logical reads 2872, physical reads 338, page server reads 0, read-ahead reads 2534, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_CUSIPEquivalence'. Scan count 1, logical reads 8, physical reads 5, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_IBM_CODES_IDA'. Scan count 1, logical reads 1736622, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'IBM_SECMASTER'. Scan count 1, logical reads 65, physical reads 0, page server reads 0, read-ahead reads 88, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_ExcludedAccounts'. Scan count 1, logical reads 195636, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'CLEACUM'. Scan count 1, logical reads 1040096, physical reads 17904, page server reads 0, read-ahead reads 305712, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'VOL_BusinessDaysPerYear'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 40063 ms, elapsed time = 3041555 ms.
就上下文而言，两者本质上都在相同的虚拟机管理程序/存储上运行，并且两个虚拟机配置了相同的资源。原始服务器没有任何类型的索引，并且 2014 年还没有 OLTP（2022 年也没有启用）。HA 功能已于 2022 年配置，但相关数据库尚未复制。我使两者尽可能相同以限制变量。
我注意到的唯一区别是 2014 年将在启动时立即为 SQL 分配所有最大内存 (16GB)，而 2022 年似乎根据查询更加动态。
我知道查询不是最佳的，但我的工作只是将其全部迁移到较新的服务器上。
我错过了什么，不明白？如何才能在 2022 年实现与当前 2014 年相同的绩效水平？
会话等待统计信息全部PAGEIOLATCH_SH（我的解释是等待 IO 数据继续，这与 IO 统计信息匹配）。
我在虚拟机上设置了内存预留并重新启动。依然没有任何变化，甚至连一秒的差别都没有。
