为什么我的数据库应用程序这么慢？

为什么我的数据库应用程序这么慢&＃xff1f;

当您的应用程序运行缓慢时&＃xff0c;反射操作是指责数据库查询。
毫无疑问&＃xff0c;一些更为奢侈的拖延可能会因为缺失的指数或不必要的锁定而被指责&＃xff0c;但还有其他潜在恶作剧&＃xff0c;包括网络和应用本身。 Dan
Turner指出&＃xff0c;你可以节省大量的时间和金钱&＃xff0c;通过努力确定问题所在的位置&＃xff0c;然后潜入细节。

低应用程序首先影响终端用户&＃xff0c;但是整个团队很快就会感受到影响&＃xff0c;包括DBA&＃xff0c;Dev团队&＃xff0c;网络管理员以及照管硬件的系统管理员。

有这么多人参与&＃xff0c;每个人都有自己的看法&＃xff0c;可能的原因&＃xff0c;可能很难确定瓶颈在哪里。

一般来说&＃xff0c;SQL Server应用程序的性能问题有两个主要原因&＃xff1a;

• 网络问题 - 与将SQL应用程序客户端连接到数据库的“管道”的速度和容量有关
• 处理时间慢 - 在管道的末端&＃xff0c;涉及要求处理的速度和效率。

在本文中&＃xff0c;我们将详细介绍如何诊断这些问题&＃xff0c;并了解最底层的性能问题。

网络问题

网络性能问题广泛地分解为与网络响应速度&＃xff08;延迟&＃xff09;或网络容量&＃xff08;带宽&＃xff09;相关的问题&＃xff0c;即在一定时间内可传输多少数据。

当然这两者是相互联系的。 如果您的应用程序&＃xff08;或同一网络上的其他应用程序&＃xff09;生成的网络流量压倒可用带宽&＃xff0c;则这可能会增加延迟。

延迟

延迟是在应用程序和SQL Server之间发送TCP数据包所需的时间。 在DB上和下降的路上&＃xff0c;您会产生延迟。 人们通常会谈论往返时间的延迟&＃xff1a;即到达那里的时间

图1显示了60毫秒的往返行程。
图1

带宽

可以以一定的时间量发送或接收的数据量&＃xff0c;通常以kb / s或Mb / s&＃xff08;兆比特每秒&＃xff09;为单位。

在讨论带宽时&＃xff0c;人们经常谈论“管道的大小”&＃xff0c;这是一个很好的类比&＃xff08;再加上它听起来很顽皮&＃xff09;&＃xff1a;你的管道越多&＃xff0c;你可以一次获得更多的数据。

如果您的应用程序需要接收10兆字节的响应&＃xff08;这是80兆比特&＃xff01;&＃xff09;&＃xff0c;并且您有20 Mb / s的连接&＃xff0c;则响应将至少需要4秒钟。 如果您有10Mb / s连接&＃xff0c;则至少需要8秒钟的时间。 如果您的网络上的其他人正在流式传播“权力”游戏&＃xff0c;那么这将降低可用带宽以供您使用。

应用问题&＃xff1a;处理时间慢

每当客户端向SQL Server发送请求时&＃xff0c;要检索所需的数据集&＃xff0c;完成请求所需的总处理时间都包括&＃xff1a;

应用程序处理时间&＃xff1a;应用程序在发送下一个请求之前处理上一个响应中的数据需要多长时间
SQL处理时间&＃xff1a;SQL在发送响应之前花费多少时间处理请求
图2提供了这个概念的简单说明。
图2

时间都花在哪了&＃xff1f;

我们花费了大量时间来调查客户端/服务器SQL应用程序的性能&＃xff0c;并且还有绝大多数不同的工具&＃xff0c;脚本和方法来帮助您排除任何数量的不同类型的性能问题。

那么当面对缓慢的应用程序响应时间的时候&＃xff0c;我们能否快速找出问题的根本原因&＃xff1f; 图3中的流程图显示了一种系统的方法来解决问题。

图3

调查性能问题时&＃xff0c;可能有多个问题。值得一看的应用程序的几个不同的部分。这是一个普遍的问题吗&＃xff1f;还是比别人慢一些&＃xff1f;

最好小开始。如果您可以专注于特别缓慢的应用程序的某个特定区域&＃xff0c;那么可以让生活更轻松&＃xff0c;例如&＃xff0c;当您点击发票页面上的“全选”按钮时&＃xff0c;加载结果需要10秒钟。专注于一个小型可重复的工作流将让您隔离问题。

接下来的问题当然是为什么要花10秒钟&＃xff1f;缩小问题的第一个也是最简单的方法是将应用程序尽可能靠近SQL Server&＃xff0c;在同一台机器上或在同一个LAN上运行。

如果有效地消除了任何网络延迟和带宽限制&＃xff0c;则突然需要一秒钟或更短时间才能选择所有发票&＃xff0c;那么您需要调查哪些网络问题可能在其余时间内消失。

如果应用程序仍然需要10秒钟的时间来加载结果&＃xff0c;那么恭喜&＃xff0c;您再次消除了4个问题中的2个&＃xff01;现在&＃xff0c;您需要查看处理时间大部分在哪里。

我们来仔细看一下如何解决这段时间大部分消费的地方。您将需要Wireshark或SQL Profiler&＃xff08;无论您更加舒适&＃xff09;。

调查申请处理时间

您将在两个地方之间看到时间&＃xff1a;发送应用程序的响应和获取下一个请求&＃xff08;应用程序处理时间&＃xff09;之间或在发出SQL Server请求和获取响应&＃xff08;SQL处理时间&＃xff09;之间的时间。

要解决哪一个导致您的问题&＃xff0c;您可以使用Wireshark或SQL Profiler&＃xff0c;因为两者都可以告诉我们大致的应用程序和SQL处理时间&＃xff08;尽管确切的数字可能会略有不同&＃xff09;。

使用Wireshark

我们可以使用Wireshark在工作流执行时捕获网络流量。使用Wireshark&＃xff0c;我们可以过滤非应用程序流量&＃xff0c;并查看工作流中所有数据包之间的时差。

计算近似应用处理时间&＃xff1a;

1. 捕获工作流的数据包&＃xff1a;启动Wireshark捕获并运行应用程序工作流程&＃xff0c;请记住在工作流完成后停止捕获。请记住选择相关的网络界面&＃xff0c;并注意您需要在与Wireshark的数据库不同的计算机上运行应用程序以查看流量​​。确保您没有运行任何其他本地SQL应用程序&＃xff0c;而不是您尝试捕获的其他本地SQL应用程序。
2. 通过应用过滤器tds&＃xff0c;然后应用文件|来摆脱非应用程序流量导出指定的数据包&＃xff0c;提供一个文件名&＃xff0c;并确保选中“显示”。在Wireshark中打开这个新文件。
3. 显示当前和上一个数据包之间的时差&＃xff0c;只需添加时间增量列&＃xff0c;如下所示&＃xff1a;
4. 选择编辑|偏好|外观|列
5. 点击&＃43;按钮&＃xff0c;将类型下拉菜单更改为“达美时间”&＃xff0c;将标题更改为“Delta”
6. 过滤流量只需要&＃xff1a;
   &＃xff08;tds.type &＃61;&＃61; 0x01 || tds.type &＃61;&＃61; 0x03 || tds.type &＃61;&＃61; 0x0E&＃xff09;&& tds.packet_number &＃61;&＃61; 1

上述过滤器将仅显示每个请求中的第一个TDS数据包&＃xff0c;“增量”列现在将显示先前请求的最后一个响应数据包与下次请求之间的时间。确保数据包由“否”列排序&＃xff0c;因为这将确保数据包按照发送/接收的顺序。

1. 导出为CSV&＃xff0c;通过导航文件|导出数据包解析|作为CSV
2. 计算应用程序处理时间&＃xff08;以秒为单位&＃xff09; - 在Excel中打开CSV&＃xff0c;并总结“增量”列中的值。

要获取大致的SQL处理时间&＃xff1a;

1. 重新打开您在Wireshark中的步骤2中创建的文件&＃xff0c;将流量过滤到只有响应&＃xff1a;
   tds.type &＃61;&＃61; 0x04 && tds.packet_number &＃61;&＃61; 1

上述过滤器将仅显示每个响应中的第一个TDS数据包&＃xff0c;“增量”列现在将显示先前请求的最后一个请求数据包与从SQL Server发回的第一个响应数据包之间的时间。同样&＃xff0c;请确保数据包由“否”列排序。

1. 导出为CSV&＃xff0c;通过导航文件|导出数据包解析|作为CSV
2. 计算SQL处理时间&＃xff08;以秒为单位&＃xff09; - 在Excel中打开CSV并总结“列”列中的值。

使用SQL Profiler

虽然已知使用SQL Profiler收集诊断数据会为您的工作流程增加一些开销&＃xff0c;但它仍然可以给您一个广泛的处理时间。 您可以通过运行服务器端跟踪&＃xff0c;然后如下所述导出数据来最小化此开销。 或者&＃xff0c;如果您对扩展事件和XQuery有信心&＃xff0c;您应该可以通过该路径获取类似的数据。

首先通过捕获工作流的Profiler跟踪&＃xff0c;只需使用“标准&＃xff08;默认&＃xff09;”跟踪模板。 确保没有其他的东西在同一时间触发数据库&＃xff0c;所以你只捕获你的流量。 捕获跟踪中的工作负载后&＃xff0c;使用File |将其保存到跟踪表 另存为| 跟踪表。

在SQL Management Studio中&＃xff0c;使用以下两个查询查询您创建的表&＃xff0c;以便为您提供大致的应用程序和SQL处理时间&＃xff1a;

/* Calculate approximate SQL Processing time for RPCs and SQL Batch queries*/SELECT SUM(DATEDIFF(MILLISECOND, StartTime, EndTime)) AS &＃39;SQL Processing Time in ms&＃39;FROM TraceTableWHERE EventClass IN ( 10, 12 );-- Selects the sum of the time difference between the start and end times -- for event classes 10 (RPC:Completed) and 12 (SQL:BatchCompleted)/* Calculate approximate app processing time*/WITH EventsAS (SELECT *FROM TraceTableWHERE EventClass IN ( 10, 11, 12, 13 ))SELECT SUM(DATEDIFF(MILLISECOND, PreviousRow.EndTime, CurrentRow.StartTime)) AS &＃39;App Processing Time in ms&＃39;FROM Events CurrentRowJOIN Events PreviousRowON CurrentRow.RowNumber &＃61; PreviousRow.RowNumber &＃43; 1WHERE CurrentRow.eventclass IN ( 11, 13 )AND PreviousRow.eventclass IN ( 10, 12 );-- Select the sum of the time difference between an end of query event -- (either 10 RPC:Completed or 12 SQL:BatchCompleted)-- and the next query starting event-- (either 11 RPC:Starting or 13 SQL:BatchStarting)


调查延迟和带宽问题

如果应用程序在本地运行时很快&＃xff0c;看起来您有网络问题。 此时&＃xff0c;您将需要知道应用程序和SQL Server之间的延迟。 你可以从一个ping上得到一个粗略的想法&＃xff0c;这将告诉你两者之间的往返时间。 当网络处于低负载状态时&＃xff0c;尝试并进行测量&＃xff0c;因为网络负载高可能会增加ping次数。

如果您计算应用程序发出的查询数量&＃xff0c;您可以计算延迟所花费的时间。

要获取Wireshark的查询数量&＃xff0c;您可以应用以下过滤器&＃xff0c;然后查看状态栏中的“显示”计数&＃xff1a;

(tds.type &＃61;&＃61; 0x01 || tds.type&＃61;&＃61;0x03 || tds.type &＃61;&＃61; 0x0E) && tds.packet_number &＃61;&＃61; 1


要获取SQL Profiler中的查询数量&＃xff0c;请按前述创建一个跟踪表&＃xff0c;并运行以下查询&＃xff1a;

SELECT COUNT(1) FROM TraceTable WHERE EventClass in (11,13)


您需要将此查询计数乘以网络延迟&＃xff08;ping值&＃xff09;。例如&＃xff0c;如果应用程序发送100个查询&＃xff0c;并且您的网络延迟为60ms&＃xff0c;则总通行时间为100 60 &＃61; 6000ms&＃xff08;6秒&＃xff09;&＃xff0c;而在LAN上&＃xff0c;则需要100 1 &＃61; 100ms&＃xff08;0.1秒&＃xff09;。

这应该告诉你延迟是否是你的问题。如果不是&＃xff0c;那么你有一个带宽问题。

过了一会儿我们没有明确看到带宽问题&＃xff0c;我们只是排除了其他问题。我们如何确认&＃xff1f;很好的问题恐怕会有点儿吃惊

如果您有一个具有流量监控的网络级设备&＃xff0c;以及与SQL Server的专用连接&＃xff0c;则可以查看您的工作流程是否使可用带宽饱和。

或者&＃xff0c;当您知道您没有带宽瓶颈时&＃xff0c;您需要查看应用程序使用多少带宽。为此&＃xff0c;您还需要运行靠近数据库的应用程序&＃xff0c;捕获Wireshark中的数据包&＃xff0c;并检查应用程序使用的带宽。同样&＃xff0c;请确保您没有运行任何其他本地SQL应用程序&＃xff0c;而不是您尝试捕获的其他本地SQL应用程序。

一旦你完成了Wireshark的捕获&＃xff1a;

1. 使用过滤器&＃xff1a;tds
2. 点击统计|对话并勾选“限制显示过滤器”框。然后&＃xff0c;您应该在会话窗口中看到您的App工作流对话。
3. 使用的带宽显示为“字节A - > B”和“字节B - > A”
   在高延迟网络上运行应用程序时重复捕获&＃xff0c;并再次查看使用的带宽。如果两者之间存在较大的差异&＃xff0c;那么您可能带宽受限。

当然&＃xff0c;为了准确比较&＃xff0c;您需要在两个测试中运行SQL Server和类似硬件上的应用程序。例如&＃xff0c;如果SQL Server在功能不强的硬件上运行&＃xff0c;那么在给定的时间内&＃xff0c;它将在整个网络中产生更少的流量。

根本原因分析

很有可能你有多个问题&＃xff01;但是&＃xff0c;在完成上述步骤之后&＃xff0c;您应该能够将所有时间用于处理工作流程。如果10秒的处理时间显示为6秒的SQL处理时间&＃xff0c;3秒的传输时间和1秒的应用处理时间&＃xff0c;那么您将了解如何确定您的调查的优先级。

如果主要问题是缓慢的SQL处理时间&＃xff0c;那么有很多关于调优和跟踪问题的信息。例如&＃xff0c;由于我们已经捕获了Profiler跟踪&＃xff0c;Gail Shaw的文章很好地概述了如何在跟踪中查找对性能问题最有贡献的过程和批处理。此外&＃xff0c;Jonathan Kehayias的书非常适合对SQL Server中常见的性能问题进行故障排除。

相反&＃xff0c;如果大部分时间花在客户端处理中&＃xff0c;您可能需要考虑对应用程序代码进行分析以查找问题。根据您的编程语言&＃xff08;例如&＃xff0c;对于.NET语言&＃xff0c;您可以使用来自Redgate的ANTS或JetBrains的dotTrace&＃xff09;&＃xff0c;有很多分析工具。

如果您遇到网络带宽问题&＃xff0c;则可能需要限制您要求的数据的大小。例如&＃xff0c;在请求数据时不要使用“SELECT *”。只返回必要的列&＃xff0c;并使用WHERE或HAVING过滤器仅返回必要的行。

在我们的经验中&＃xff0c;性能问题的一个常见原因是通过高延迟网络运行“聊天”应用程序。一个聊天应用程序是发送许多重复和不必要的查询&＃xff0c;使得更多的网络往返行程比必要。

通常&＃xff0c;这些应用程序最初是在高速LAN上开发并部署的&＃xff0c;所以“chattiness”从来没有真正引起问题。当数据移动到不同的位置&＃xff08;如云端&＃xff09;时&＃xff0c;会发生什么&＃xff1f;或者不同大陆的客户试图访问&＃xff1f;或者您需要构建地理上多样化的灾难恢复环境&＃xff1f;如果您考虑在一个1ms的LAN上的每个查询在60ms广域网上的速度将会降低60倍&＃xff0c;那么您可以看到这样会如何影响您的性能。

简而言之&＃xff0c;在编写客户端/服务器应用程序时&＃xff0c;您需要避免频繁执行相同的查询&＃xff0c;以最大限度地减少必要的往返次数来收集所需的数据。这两种最常见的方法是&＃xff1a;

• 重写代码 - 例如&＃xff0c;您可以聚合和过滤服务器上的多个数据集&＃xff0c;以避免每个数据集进行查询&＃xff0c;尽管并不总是更改应用程序
• 使用查询预取和缓存 - 有一些WAN优化工具可以做到这一点&＃xff0c;但它们有时是昂贵的&＃xff0c;难以配置以获得高性能&＃xff0c;而不会在应用程序中引入错误

我们对这些问题进行了大量的研究&＃xff0c;同时开发了数据加速器工具&＃xff0c;并采用了一种使用机器学习来预测应用程序要做什么的方法&＃xff0c;并预取所需的数据&＃xff0c;因此它准备就绪因为应用程序请求它。

总结

在您花费大量时间和金钱的可能解决方案之前&＃xff0c;确保您解决问题所在。 我们已经看到&＃xff0c;当公司最大的问题出现在应用程序性能问题上时&＃xff0c;公司花费大量资金和人力时间来优化SQL查询。 相反&＃xff0c;我们已经看到&＃xff0c;企业将越来越多的内存或CPU放入SQL服务器&＃xff0c;这样做永远不会弥补网络延迟的额外时间。 如果您可以确定工作流程处理时间的真正用途&＃xff0c;您可以以正确的方式指导您的时间和精力。

希望这可以让您了解如何调查自己的应用程序的性能&＃xff0c;或者开始追踪您可能遇到的任何问题。