升级后 SQL Server 性能问题

4.75/5 (9投票s)

2019年12月9日

4分钟阅读

8213

分析和排查数据库性能瓶颈

引言

最近，我遇到了一个问题，客户报告他们的OLAP系统性能严重下降。大多数运行的报告要么超时，要么在很长时间后才返回数据。问题始于客户升级后，包括以下内容

以下是新旧服务器的规格

我通过查看一些有用的DMV开始调查，例如sys.dm_exec_requests，sys.sysprocesses等。我的初步审查显示temp db存在压力。几乎所有运行缓慢的进程都在等待PAGELATCH等待类型。

事实上，除了上述等待类型外，通过冲击tempdb的事务量，也可以明显看出tempdb无法跟上事务压力。请参见下图（图1和图2）。

图1 - 数据库事务

在高峰期，temp db上的事务率接近4k /秒。

图2 - tempdb分配与当时正在运行的某些进程的tempdb分配

我开始检查temp db文件是否已根据CPU正确设置。我发现36个CPU服务器上只有8个temp db文件。这是速度慢的原因之一，我立即要求OP DBA将其增加到18，遵循我组织遵循的标准。我们创建的文件等于CPU总数的一半，并在以后根据需要创建。

尽管系统性能有所提高，但用户仍然抱怨速度慢，因为他们必须等待相当长的时间才能处理他们的请求。一切似乎都正常，没有重大阻塞，内存不足，CPU峰值或磁盘延迟。但是，仔细观察CPU发现，即使在高峰活动期间，CPU的范围也从40％到50％（图3），而在正常情况下，平均利用率为80％-85％是相当正常的。

图3 - CPU利用率

查看任务管理器时，发现大约50％的CPU正在获取线程（图4）。

图4 - CPU的任务管理器视图

与硬件团队的一些后续讨论表明，由于非基于核心的许可策略，SQL Server无法在所有CPU上发送线程。问题是，已完成的每个SQL Server 2016安装都使用了较旧的Server + CAL许可介质。此版本仅限于在主机上使用20个内核。

为了解决此问题，我们需要对所有新安装使用基于核心的许可。为了解决现有服务器中的此问题，客户批准了一个停机时间窗口，因为它需要重新启动SQL。

已实施的更改示例

上面的更改导致线程分布在多个处理器上，并且系统性能大大提高。

这一发现还导致纠正了安装期间的总体CPU许可策略和tempdb设置，这是一种积极主动的方法，可以解决将来的客户问题，这些问题可能会在以后出现。

此外，客户端的两个存储过程被确定为CPU和IO消耗方面的“重击者”，并且已努力用优化的版本替换它们，该版本具有更好的编码方法。这些存储过程的较旧版本通过单个事务执行非常昂贵的操作，并且将其分解为较小的批处理，并将CTE替换为临时表。

上面的问题非常有趣，对我来说是一次学习经历，并且我感觉这是我可以分享的正确媒介。