![SQL Server 2005 全球放缓](https://linux22.com/image/640957/SQL%20Server%202005%20%E5%85%A8%E7%90%83%E6%94%BE%E7%BC%93.png)
两天前,我们的生产服务器遭遇了严重的减速,主要症状是大量请求遭遇 SQLTimeouts。我将快速描述我们的设置、我调查的内容、我们的解决方法,然后回答我的问题。
我们的设置
一对服务器托管我们的 SAS 应用程序的这个分支。一个是运行 IIS 上的多个应用程序的应用服务器,另一个是运行 SQL Server 2005 的 Windows Server 2008 机器,速度变慢了。SQL 托管了 100 到 200 个数据库。
问题/调查
服务几乎停滞。一些请求通过,但大多数都遭遇 SQL 超时。SQL 机器 CPU 和 RAM 看起来不错,平均 CPU 工作负载约为 25%,RAM 约为 85%。我当时没有想到要检查磁盘活动,因为我直接进入了“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%
然而,我无法知道在速度减慢期间发生了哪种类型的阻塞,因为我们当时并没有掌握所有这些信息。
答案1
在我发布这个问题的第二天,问题最终再次出现。
运行 Paul S. Randal 的查询后,我很快发现了许多正在进行的 PAGELATCH_XX 阻塞等待,因此使用 sp_who2 我能够找到罪魁祸首数据库,并且只需从 Web 服务器重新启动相关的客户端应用程序池,作为一种不太苛刻的恢复服务的方法。
我们还能够追踪实际操作,这些操作比以前执行了更多的 tempdb 工作,并将从不同的角度解决这个问题。
解决方案
我们已经根据最佳实践,将 tempdb 文件拆分为多个文件,因为看起来这是该解决方案发生的正确类型的争用,可以解决我的问题。