提前：抱歉问题的长度......无法在细节和简洁之间取得适当的平衡。
我们的 Web 应用程序的数据库服务器存在问题，其中应该（并且通常会）在很短的时间（< 10 毫秒）内运行的查询，在随机情况下，需要 1 到 30 秒的时间来执行 - 没有明显的图案。根据我们的分析器跟踪，其中一些甚至是“无所事事”的查询，例如"exec sp_reset_connection"（通常在 0ms 内运行；观察到的峰值为 3~6s），"SET NO_BROWSETABLE ON"等等。一些例子是：
SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'
WhereTimeZoneRule在 5 列中有大约 500,000 行。具有代理主键和Name. 通常需要 0.97ms，在 11s 达到峰值。永远不会写入表（在上线之前预先填充）。Profiler 将其记录为占用 0-15 CPU、18-25 读取、0-1 写入（不知道为什么要写入）。
UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'
WhereUser在大约 10 列（其中之一是 Xml 列）上有大约 30,000 行。Id是聚集的主键。表被定期写入和读取。通常需要 10~20ms，在 26s 达到峰值。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大约 8 列有大约 1,000,000 行。有一个代理主键（身份）和一个索引ASPSessionId。表定期写入，但很少读取（仅由我们直接从 SSMS 读取）。通常需要15~150ms，5s达到峰值。我手头没有它的配置文件记录，但从内存来看，CPU 大约为 0，读取和写入分别在 0 到 100 之间。
我们使用的设置是镜像设置，以戴尔 2950 为原理（2 个 4 核至强 2.6、16Gb RAM）和戴尔 6850 作为镜像（4 个 HT Xeon 3.2、8Gb RAM）。两者都运行 SQL 2005 SP4 64 位。有问题的数据库不是特别大，大小约为 16Gb。主有 6 个 SAS 磁盘，分为 3 个 RAID-1 卷；一份用于 System + Page + TempDB，一份用于数据库的 MDF，一份用于事务日志 + 每小时日志备份 + 每日 DB 备份。我知道日志情况远不是最好的——就磁盘 IO（见下文）和数据安全而言。
到目前为止，我们认为我们已经消除了：
- 镜子。我们分离了服务器，并使用其中一个运行（然后切换到另一个），但性能问题仍然存在。
- 由于锁（*）而阻塞。
TimeZoneRule永远不会被写入，并且据我估计，永远不应该对它有排他锁。此外，我们已经检查了跟踪，在许多情况下，“问题查询”是唯一运行的 - 唯一的其他活动是其他连接断开 - 索引不佳。由于读取和 CPU 的数据较低，这表明 SQL Server 正在有效地使用索引。
- 磁盘 IO。PerfMon 表示数据文件驱动器（但仅该驱动器）的一些奇怪数字 - 虽然数据读取/写入速率似乎很少超过 32KB/s，但当前磁盘队列长度在大约 45 秒的 2-5 秒内飙升至大约 215 -60 分钟间隔，没有固定模式。但是，这些与查询性能不佳的时间无关。其他两个驱动器 [system + page + tempdb] 和 [log + backups] 的磁盘队列长度永远不会超过 3。
(*) 我们尝试让分析器捕获与锁定获取相关的事件，但跟踪膨胀到无法读取的程度，更糟糕的是，Web 应用程序停止运行。
不是 DBA，我们的想法很快就会枯竭。谁能想到我接下来应该考虑看的任何东西或我愚蠢地错过的任何东西？
很少有可能无用或有用的东西；
如果这种情况发生在存储过程中，可能是参数嗅探-> 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/
在运行 SQL 2005 时，您可以获取 SQL Profiler 数据并将其与 Perfmon 数据进行比较，以查看是否可以看到相关性。这是通过使用常规技术将跟踪数据和性能数据保存到文件中来完成的。然后在 profiler 中打开 SQL Profiler 跟踪，然后文件菜单中的选项之一将是 Import Performance Data。这将让您选择一个查询并查看当时计数器在做什么（或接近它，具体取决于您的 perfmon 收集间隔）。
磁盘队列峰值从来都不是好事。尤其是那么高。当队列变得如此之高时，您推送到磁盘的 IO 是多少？基本上，您不希望磁盘队列高于 (2*n) ，其中 n 是阵列中的磁盘数。由于您使用的是 2 磁盘 RAID 1 n=1 （因为您只能获得单个磁盘的速度）。
在 perfmon 中有一个计数器，它是每次读取的秒数和每次写入的秒数。当查询开始需要很长时间才能运行时，这些计数器会是什么样子。平时呢？（任何超过 0.02 秒的时间都是不好的。）预计的页面预期寿命是多少？（任何低于 300 秒的时间通常都不好，但这可能会有所不同。） SQL Server 缓存命中率是多少？（低于 97% 的任何东西通常都不好。我喜欢高于 99.9% 的东西。）
您是否看到数据库和/或日志增长事件？此类事件将显示在 ERRORLOG 和性能计数器中。
有几件事要尝试，最有用的是 Display Estimated Execution Plan，以及在 SMSS 中包含实际执行计划。
如果您在运行查询之前勾选“包括实际执行计划”按钮，则在执行查询后，它将显示查询的成本在哪里。根据成本，通常很容易找出问题所在。如果它是一个 SORT，那么它就是一个糟糕的索引。如果它正在构建一个哈希表，那就是错误的索引/错误的连接，在简单的 SELECT * 查询期间，您甚至可能不知道会发生各种可能出错的事情。
尝试的第二件事是运行 SQL 查询探查器（突出显示查询，右键单击，在 SQL 探查器中跟踪查询）。它还将发现可以消除的低效率。
但是，您的查询都非常简单，并没有指向数据库设计缺陷，但是它至少可以让您知道下一步该去哪里（显然您会在执行时间比预期长的时候检查它） .
另一个需要查看的地方，有时可能是信息过载，是 SQL Server Profiler（您提到您已经使用过，但是您在 26 秒执行期间是否发现了它？）。您可以（几乎）实时观看 SQL 服务器所做的一切。根据您的此类活动窗口的时间长短，如果您让它准备好运行跟踪，一旦它开始变得笨拙，就启用跟踪，看看 SQL 服务器中是否有它正在等待的东西。
您是否定期手动重建数据库表的统计信息？如果它们已过期并且设置了自动更新统计信息选项，则查询可以在重建统计信息时暂停。
除了手动更新统计信息外，您还可以考虑启用异步统计信息。
这是 T-SQL ：
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
进一步阅读：
http://msdn.microsoft.com/en-us/library/ms190397.aspx
我不相信这是您问题的根本原因 - 但可能值得排除它。