什么可以导致长的查询持续时间而不高的资源使用？

Question

事先:抱歉，问题太长了.无法在细节和简洁之间取得正确的平衡。

我们对web应用程序的DB Server有问题，在这种情况下，应该(而且通常会)在非常短的时间(< 10 30 )内运行的查询，在随机情况下，需要花费1到30秒的时间来执行--没有明显的模式。根据我们的分析器跟踪，其中一些甚至是“无所事事”的查询，例如"exec sp_reset_connection" (通常运行在0MS中；观察到的峰值为3~6s)和"SET NO_BROWSETABLE ON"等。

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

其中，TimeZoneRule在5列中有大约500,000行。有一个代理主键和一个Name索引。通常为0.97ms，峰值为11s。表从来没有写到(在上线之前是预先填充的)。分析器将其记录为占用0-15 CPU，18-25读，0-1写(不知道为什么写)。

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

其中，User在大约10列上有大约30,000行(其中一列是Xml列)。Id是群集主键。表定期被写和读。通常需要10~20 at，峰值在26s。分析器记录为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在大约8列上有大约1,000,000行。具有代理主键(标识)和ASPSessionId上的索引。表定期写入，但很少读取(仅由我们直接从SSMS)。通常需要15~150 5s，峰值在5s。我手头上没有它的配置文件记录，但是从内存来看，CPU在0左右，读写在0到100之间。

我们使用的设置是镜像设置，以戴尔2950为原理(2 4核xeon 2.6，16 8Gb )，以戴尔6850作为镜像(4 HT 3.2，8GB RAM)。都在运行SQL 2005 SP4 64位.所讨论的数据库不是特别大，大约是16 in大小。主磁盘分为3个RAID-1卷；一个用于System + Page + TempDB，一个用于数据库的MDF，另一个用于事务日志+逐时日志备份+每日DB备份。我知道，在磁盘IO (见下文)和数据安全性方面，日志的情况远远不是最好的。

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

• 镜子。我们分离服务器，使用其中一个服务器运行(然后切换到另一个服务器)，但性能问题仍然存在。
• 由于锁而阻塞(*)。TimeZoneRule从来没有写到，据我估计，永远不应该有一个独占的锁。此外，我们已经检查了跟踪，在许多情况下，“问题查询”是唯一正在运行的-唯一的其他活动是其他连接断开
• 索引很差。在读取和CPU数据较低的情况下，这表明Server正在有效地使用索引。
• 磁盘IOPerfMon表示数据文件驱动器(但只有该驱动器)的一些奇怪数字--尽管数据读/写速率似乎很少超过32 45/S，但在没有固定模式的情况下，当前磁盘队列长度在2-5秒的持续时间内猛增至215秒左右。但是，这与查询性能不佳的时间无关。其他两个驱动器系统+页+ tempdb和日志+备份的磁盘队列长度从未超过3。

(*)我们试图让分析器来捕获与锁获取相关的事件，但是跟踪膨胀到了不可读的程度，更糟糕的是，web应用程序陷入了停顿。

不是DBA，我们的想法很快就用完了。谁能想到我下一步该考虑看什么，或者我愚蠢地错过了什么？

Answer 1

在运行SQL 2005时，可以获取SQL数据并将其与Perfmon数据进行比较，以查看是否可以看到相关性。这是通过使用常规技术将跟踪数据和perfmon数据保存到文件中来完成的。然后在分析器中打开SQL跟踪，然后文件菜单中的一个选项将是。这将使您可以选择一个查询，并查看计数器当时在做什么(或者根据perfmon集合间隔的不同而接近它)。

磁盘队列尖峰从来都不是好的。尤其是那么高。当队列变得那么高时，您要推到磁盘上的IO是什么？基本上，您不希望磁盘队列大于(2*n)，其中n在数组中的磁盘数中。由于您使用的是2磁盘RAID 1，所以在您的情况下(因为您只获得单个磁盘的速度)。

在perfmon中有一个计数器，它是每次读取的秒和每次写的秒。当查询开始运行很长时间时，这些计数器是什么样子的？正常情况下呢？(任何超过.02秒的内容都是不好的。)预计的预期寿命是多少？( 300秒以下的任何东西通常都是坏的，但这可能会有所不同。)SQL Server缓存命中率是多少？(低于97%的东西通常都是坏的。我喜欢我的超过99.9%。

Answer 2

很少有可能没有帮助或可能有用的事情；

如果这种情况发生在存储过程中，则可能是参数嗅探-> http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html。

您是否将ASP用于web应用程序？我们有一个类似的问题，但与使用存储过程的ASP + IIS和SQL有关。我似乎还记得是信号量超时导致了这种情况。运行一个查询需要花费几乎30+秒的时间，但随后一切都很好。我在上面找不到我的信息，但我似乎记得它与IIS超时有关，这是IIS方面的。

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

Answer 3

您看到数据库和/或原木生长事件了吗？这些事件将出现在ERRORLOG和性能计数器中。