SQL Server 2005 全球减速

两天前，我们的生产服务器遭受了严重的减速，主要症状是异常大量的请求正在遭受 SQLTimeouts。我将快速描述我们的设置、我调查的内容、我们的解决方法，然后提出我的问题。

我们的设置

一对服务器托管我们的 SAS 应用程序的这个分支。一个是在 IIS 上运行多个应用程序的应用程序服务器，另一个是运行 SQL Server 2005 的 Windows Server 2008 机器。SQL 托管在 100 到 200 个数据库之间。

问题/调查

服务几乎停止了。一些请求通过，但大多数遭受 SQL 超时。SQL 机器 CPU 和 RAM 看起来不错，平均大约 25% 的 CPU 工作负载和 85% 的 RAM。当时我没想过要检查磁盘活动，因为我直接去了“EXEC sp_who2”

结果显示数百个任务被 ID 123 阻塞，它本身和一百个其他任务被 ID 456 阻塞。正常执行通常根本没有阻塞任务。当我在 15-20 秒后重新运行 sp_who2 时，会弹出不同的阻塞 ID，但阻塞/阻塞任务的数量似乎保持不变。（由于紧急模式，没有计算组）

大多数任务都被诸如“SELECT INTO”或“CREATE INDEX on temptable”之类的语句阻塞。

解决方法

杀死 SQL 进程并重新启动它以恢复服务。放缓没有再次出现，但我们知道我们处于危险之中。

我的问题

我能做些什么来解决这个问题，最好是在它再次发生之前？

子问题：

• 在正常活动期间我可以调查其他路径吗？
• 如果/当问题再次发生时，我应该收集哪些信息？（需要快速获取，因为这意味着我们将再次遇到服务中断）

到目前为止我做了什么

从症状来看，我们怀疑问题是 tempdb 上的某种争用。（另一个症状是在问题期间右键单击 tempdb 以查看属性会在片刻后产生错误）

没有日志表明 tempdb 上发生了自动增长事件，但据我所知，没有记录自动增长成功，只有失败。

从那时起，我已经阅读了很多关于 tempdb 争用的不同信息来源，不仅限于但包括：

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ http://www.sqlservercentral.com/blogs/robert_davis/2010/03/ 05/Breaking-Down-TempDB-Contention/

据我所知，最好的做法是设置初始大小的 tempdb 文件，并且每个内核有一个，最多 8 个文件。我们计划尽快将其落实到位（8 个内核，因此 8 个文件），因为这是最佳实践。它们都将在同一个硬盘上（目前），但我们认为最坏的情况是没有改善，最好的情况是我们获得了逻辑争用瓶颈和磁盘 I/O 瓶颈之间的差异。

但是，我们无法确定与我们遇到的问题的相关性。据我了解，拆分为多个临时文件将有助于“PAGELATCH_XX”类型的等待，但在正常活动期间运行 Paul S. Randal 的查询（参见第一个发布的链接），不存在这种类型的等待。我在正常活动期间看到的前 3 个是：

CXPACKET 68.63%
LATCH_EX 18.46%
PAGEIOLATCH_SH 4.35%

我无法知道减速期间发生了什么类型的阻塞，因为那时我们没有所有这些信息。