![什么原因会导致查询持续时间长且资源使用率不高?](https://linux22.com/image/487375/%E4%BB%80%E4%B9%88%E5%8E%9F%E5%9B%A0%E4%BC%9A%E5%AF%BC%E8%87%B4%E6%9F%A5%E8%AF%A2%E6%8C%81%E7%BB%AD%E6%97%B6%E9%97%B4%E9%95%BF%E4%B8%94%E8%B5%84%E6%BA%90%E4%BD%BF%E7%94%A8%E7%8E%87%E4%B8%8D%E9%AB%98%EF%BC%9F.png)
提前说一句:抱歉,问题太长了……无法在细节和简洁之间取得适当的平衡。
我们的 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
我并不确信这是导致你的问题的根本原因 - 但也许值得排除它。