☰

什么原因会导致查询持续时间长且资源使用率不高？

tag-icon • tag-icon sql-server sql-server-2005 performance
什么原因会导致查询持续时间长且资源使用率不高？

提前说一句：抱歉，问题太长了……无法在细节和简洁之间取得适当的平衡。

我们的 Web 应用程序的 DB 服务器出现了问题，一些查询应该（并且通常会）在很短的时间内（< 10 毫秒）运行，但有时却需要 1 到 30 秒才能执行，而且没有明显的规律。根据我们的分析器跟踪，其中一些甚至是“无用”查询，例如"exec sp_reset_connection"（通常在 0 毫秒内运行；观察到的峰值为 3 到 6 秒）和"SET NO_BROWSETABLE ON"等。以下是一些示例：

SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'

其中TimeZoneRule有 500,000 行，分为 5 列。具有代理主键和索引Name。通常需要 0.97 毫秒，峰值为 11 秒。表从未写入（在上线前已预先填充）。Profiler 记录它占用 0-15 CPU、18-25 次读取、0-1 次写入（不知道为什么写入）。

UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'

其中User约有 30,000 行，约有 10 列（其中一列是 Xml 列）。Id是聚集主键。表定期写入和读取。通常需要 10~20 毫秒，峰值为 26 秒。Profiler 记录它占用 0 CPU、15-36 次读取、0-1 次写入。

INSERT INTO [Log].[Session] (ASPSessionId, Start, ClientAddress, ClientSoftware, ProxyAddress, ProxySoftware)
   VALUES(<number>, GETUTCDATE(), '<ipv4address>', '<User agent string>', '<ipv4address>', '<proxy software name (if present)>')

其中Session约有 1,000,000 行，约 8 列。具有代理主键（标识）和索引ASPSessionId。表定期写入，但很少读取（仅由我们直接从 SSMS 读取）。通常需要 15~150 毫秒，峰值为 5 秒。我手头没有它的配置文件记录，但从内存来看，CPU 大约为 0，读取和写入分别在 0 到 100 之间。

我们使用的设置是镜像设置，以 Dell 2950 为主（2 个 4 核 xeon 2.6，16GB RAM），以 Dell 6850 为镜像（4 个 HT Xeon 3.2，8GB RAM）。两者均运行 SQL 2005 SP4 64 位。所讨论的数据库不是特别大，大小约为 16GB。主数据库有 6 个 SAS 磁盘，分为 3 个 RAID-1 卷；一个用于系统 + 页面 + TempDB，一个用于数据库的 MDF，一个用于事务日志 + 每小时日志备份 + 每日数据库备份。我知道日志情况远非最佳 - 就磁盘 IO（见下文）和数据安全而言。

到目前为止，我们思考我们已经消除了：

  • 镜像。我们分离了服务器，使用其中一台运行（然后切换到另一台），但性能问题仍然存在。
  • 由于锁定而阻塞（*）。TimeZoneRule永远不会写入，而且据我估计，永远不应该对其设置独占锁。此外，我们检查了跟踪记录，发现在很多情况下，“问题查询”是唯一正在运行的查询 - 唯一的其他活动是其他连接断开
  • 索引不良。读取和 CPU 的数字较低，表明 SQL Server 正在有效地使用索引。
  • 磁盘 IO。PerfMon 指示数据文件驱动器（但仅限于该驱动器）的一些奇怪数字 - 虽然数据读取/写入速率似乎很少超过 32KB/s，但当前磁盘队列长度会以大约 45-60 分钟的间隔持续 2-5 秒，达到约 215，没有固定的模式。但是，这些与查询性能不佳的时间无关。其他两个驱动器 [系统 + 页面 + tempdb] 和 [日志 + 备份] 的磁盘队列长度从未超过 3。

(*) 我们尝试让分析器捕获与锁获取相关的事件，但是跟踪膨胀到难以读取的比例，更糟糕的是，Web 应用程序陷入停顿。

由于不是 DBA，我们很快就想不出什么主意了。有人能想到我下一步应该考虑看什么或我愚蠢地错过了什么吗？

答案1

当您运行 SQL 2005 时，您可以获取 SQL Profiler 数据并将其与 Perfmon 数据进行比较，以查看是否存在相关性。这是通过使用常规技术将您的跟踪数据和 perfmon 数据保存到文件来完成的。然后在探查器中打开 SQL Profiler 跟踪，然后文件菜单中的选项之一将是导入性能数据。这将允许您选择一个查询并查看计数器当时正在做什么（或接近它，具体取决于您的 perfmon 收集间隔）。

磁盘队列峰值从来都不是好事。尤其是这么高的时候。当队列变得这么高时，您要向磁盘推送的 IO 是什么？基本上，您不希望磁盘队列高于 (2*n)，其中 n 是阵列中的磁盘数。由于您使用的是 2 磁盘 RAID 1，因此 n=1（因为您只能获得单个磁盘的速度）。

perfmon 中有一个计数器，用于记录每次读取的秒数和每次写入的秒数。当查询开始需要很长时间运行时，这些计数器是什么样子的。通常情况如何？（任何超过 0.02 秒的情况都是不好的。）预计页面寿命是多少？（任何低于 300 秒的情况通常都是不好的，但这可能会有所不同。）SQL Server 缓存命中率是多少？（任何低于 ~97% 的情况通常都是不好的。我喜欢我的命中率高于 99.9%。）

答案2

一些事情可能没用，也可能有用；
如果这种情况发生在存储过程中，可能是参数嗅探 ->http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
您是否在 Web 应用程序中使用 ASP？我们遇到了一个类似的问题，但与使用存储过程的 ASP + IIS 和 SQL 有关。我似乎记得是信号量超时导致了这个问题。运行查询需要将近 30 多秒的时间，但之后一切都会好起来。我找不到相关信息，但我似乎记得它与 IIS 超时有关，这是在 IIS 方面。

这个工具可能也有用 ->http://blog.brianhartsock.com/2008/12/16/quick-and-dirty-sql-server-slow-query-log/

答案3

您是否看到数据库和/或对数增长事件？此类事件将显示在 ERRORLOG 和性能计数器中。

答案4

您是否定期手动重建数据库表的统计信息？如果它们已过期，并且设置了自动更新统计信息选项，则查询可能会在重建统计信息时暂停。

除了手动更新统计数据之外，您还可以考虑启用异步统计。

这是 T-SQL：

更改数据库 dbName 设置 AUTO_UPDATE_STATISTICS_ASYNC ON

进一步阅读：

http://msdn.microsoft.com/en-us/library/ms190397.aspx

我并不确信这是导致你的问题的根本原因 - 但也许值得排除它。

相关内容