提前：抱歉的问题的长度…无法在细节和简洁之间取得适当的平衡。
在我们的Web应用程序中，我们遇到了数据库服务器的问题，应该（通常是）在很短的时间（<10ms）内运行的查询，在随机的场合，需要1到30秒的时间执行 – 没有明显的模式。 根据我们的剖析器的轨迹，其中一些甚至是"exec sp_reset_connection" （通常运行在0ms;观察到的峰值为3〜6s）和"SET NO_BROWSETABLE ON"等"SET NO_BROWSETABLE ON"无所事事”查询。 ：
SELECT * FROM [Localisation].[TimeZoneRule] WHERE [Name] = 'EU'
TimeZoneRule在5列中有大约500,000行。 具有代理主键和Name上的索引。 通常需要0.97ms，在11s达到峰值。 表从未写入（在上线之前预先填充）。 事件探查器logging为0-15 CPU，18-25读，0-1写（不知道为什么写）。
UPDATE [Core].[User] SET [LastUsed] = GETUTCDATE() WHERE Id = '<uid>'
User在大约10列（其中之一是Xml列）上有大约30,000行。 Id是集群主键。 表是定期写入和读取。 通常需要10〜20ms，峰值在26s。 Profiler将其logging为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上有一个代理主键（标识）和一个索引。 表是定期写入，但很less从（仅由我们直接从SSMS）读取。 通常需要15〜150ms，5s达到峰值。 我手边没有档案logging，但是从内存来看，CPU是0左右，读写每个都在0到100之间。
我们使用的设置是以戴尔2950为原理（2个4核Xeon 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，另一个用于事务日志+每小时日志备份+日常数据库备份。 我知道日志情况远不是最好的 – 就磁盘IO（见下面）和数据安全性而言。
到目前为止，我们认为我们已经消除：
TimeZoneRule永远不会写入，根据我的估算，永远不应该有独占locking。 此外，我们已经检查了痕迹，并且在许多情况下“问题查询”是唯一正在运行的 – 唯一的其他活动是其他连接断开连接 （*）我们已经尝试让分析器捕获与锁获取有关的事件，但是跟踪膨胀到难以理解的比例，更糟糕的是，Web应用程序嘎然而止。
不是数据库pipe理员，我们正在迅速用完想法。 任何人都可以想到我应该考虑看什么或者我愚蠢地错过了什么？
在运行SQL 2005时，可以将SQL Profiler数据与Perfmon数据进行比较，以查看是否可以看到关联。 这是通过使用常规技术将您的跟踪数据和perfmon数据保存到文件来完成的。 然后打开探查器中的SQL事件探查器跟踪，然后文件菜单中的一个选项将是导入性能数据。 这将让你select一个查询，看看当时正在做的计数器（或靠近它取决于你的perfmon收集间隔）。
磁盘队列尖峰永远不会好。 尤其是那么高。 当队列变得很高时，你推送到磁盘的IO是什么？ 基本上你不想要一个高于（2 * n）的磁盘队列，其中数组中的磁盘数量为n。 由于您使用的是2磁盘RAID 1 n = 1（因为您只能获得单个磁盘的速度）。
在perfmon中有一个计数器，它是每次读取的秒数和每次写入的秒数。 当查询开始花费很长时间运行时，这些计数器是什么样子的。 通常情况下呢？ （超过0.02秒是不好的。）预计的页面预期寿命是多less？ （300秒以内的任何错误通常都是不好的，但这可能会有所不同。）SQL Servercaching命中率是多less？ （低于97％通常是不好的，我喜欢高于99.9％）。
很less有事情可能无益或可能有用;
如果这是发生在存储过程，它可能是参数嗅探 – > 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/
你看到数据库和/或日志增长事件？ 这样的事件会出现在ERRORLOG和性能计数器中。
需要尝试的东西中，最有用的是显示估计执行计划，以及SMSS中包含实际执行计划。
如果在运行查询之前勾选了“包括实际执行计划”button，执行查询后会显示查询的成本。 根据成本，通常很容易找出错误的地方。 如果是SORT，那么这是一个糟糕的索引。 如果它正在构build一个哈希表，那么这是错误的索引/错误的连接，有各种各样的事情可能会出错，你可能不知道在一个简单的SELECT *查询过程中发生。
第二件事就是运行SQL查询事件探查器（突出显示查询，右键单击，在SQL事件探查器中跟踪查询）。 它也会发现可以消除的低效率。
然而，你的查询是非常简单的，并没有指出数据库devise的缺陷，但它至less可以让你知道下一步该怎么做（显然你会在执行时间比预期长的时候检查它） 。
另一个可以看的地方，有时候可能是信息重载，SQL Server Profiler（你提到你已经使用了，但是你在26s执行期间是否抓住了它？）。 您几乎可以实时查看SQL服务器所做的一切。 根据这种活动的窗口有多长时间，如果你有足够的时间准备好运行一个跟踪，只要它开始使用chuggy来启用跟踪，看看SQL服务器里是否有东西在等待。
你是否需要定期手动重build数据库的表格统计信息？ 如果它们已经过期，并且设置了自动更新统计信息选项，那么在重build统计信息时查询可以暂停。
除了手动更新统计信息之外，您还可以考虑启用asynchronous统计信息。
这里是T-SQL：
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
进一步阅读：
http://msdn.microsoft.com/en-us/library/ms190397.aspx
我不相信这是你的问题的根源 – 但它可能是值得排除的。