主页 / dba / 问题 / 333767
Accepted
JulioQc
Asked: 2023-12-06 04:12:29 +0800 CST

新的 SQL Server 2022 比旧的 2014 服务器慢

  • 772

我正在计划和测试新的 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 年相同的绩效水平？

2014年和2022年执行计划

会话等待统计信息全部PAGEIOLATCH_SH（我的解释是等待 IO 数据继续，这与 IO 统计信息匹配）。

我在虚拟机上设置了内存预留并重新启动。依然没有任何变化，甚至连一秒的差别都没有。

sql-server

1 个回答

  1. Best Answer

    好吧！结果是RESOURCE GOVERNOR2022 年，MAX_IOPS_PER_VOLUME默认资源池设置为 10。在 2022 年服务器上再次尝试ALTER RESOURCE GOVERNOR DISABLE。当然，第一次运行不如第二次运行那么快，但它从 50 分钟缩短到了 2 分钟。加载数据时，我可以看到 IO 从之前极其缓慢的 1MB/s 提高到现在的 >100MB/s。下次从缓存运行的时间低于预期，不到 10 秒。

    我们在 2022 年配置了调控器来处理潜在的嘈杂邻居问题，因为其他实例在同一机器上运行。我们的 2014 年盒子没有此配置，因为它是独立的。

    • 0

相关问题