SQL SERVER 2005 周期性挂掉的原因分析及解决方案分享

【原因描述】
最近两个星期，客户的sql server 2005每天就会挂掉一次，然后只能重启。并且 数据库挂了后，重启数据库服务都启动不了，只有重启服务器才行 。

【环境配置】
基本配置：正式环境 window server 2008+sqlserver2005（挂掉的机器）
sql server内存分配120G，cpu64核心 连接限制无

【日志分析】

错误情况：

1、从数据库日志里面 C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log 路径下对应的 ERRORLOG、ERRORLOG.1、ERRORLOG.2 里面没有找到有用的信息

2、从Windows系统日志里面发现每次错误都会报   Timeout occurred while waiting for latch: class 'ACCESS_METHODS_DATASET_PARENT', id 0000001731C3CB90, type 4   这个错误信息

【背景介绍】

Latch是SQL Server内部用来同步资源访问的一个数据结构。和操作系统的Critical Section或ReaderWriterLock类似。Latch保护了那些想保护的资源，使得访问同步有序。比方说，当某个线程获得某个资源的Latch的独占使用权时候，别的线程如果也需要访问这个Latch,则它必须等待。

从大的方面来讲，有两种Latch, 一种叫Buffer Latch，另外一种叫I/O Latch.

我们先来简短介绍一下I/O Latch。当SQL Server从硬盘上读取一个页时，会先在内存预留该页的空间。并且在该预留空间的某一个位BUF_IO设为1. 如果数据从硬盘读写完成，则该位设为0. 从硬盘读取页的期间，其他也需要访问该页的线程当然要等待，等待类型为PAGEIOLATCH_SH，直到读写完成，BUF_IO被设为0为止。因此，如果我们看到大量PAGEIOLATCH_SH等待，则基本可以断定问题是出在[b]磁盘性能[/b]上面。

另外一种Latch则称为Buffer Latch，用来保护内存里的数据结构，如Index, Data Pages, B树中的Non-Leaf页。当进程需要读取一个内存里的数据页时，该进程要先获取该数据页上的Buffer Latch。有各种类型的Latch, 包括独占Latch(PAGELATCH_EX)和共享Latch(PAGELATCH_SH)。

【如何解决】

Latch申请不到，是由于被其他进程占用的缘故。所以我们需要找到哪个进程占用该Latch一直不释放。然后采取相应的办法进行解决。通常来讲，都是由于磁盘性能所造成的，如果需要得知确切的原因，我们可能需要对内存转储进行分析。对于第一个Latch Timeout的错误，SQL Server会产生一个内存转储。而对于后续的Latch Timeout，则会在SQL Server的错误日志里，报告一个错误信息，但不会产生内存转储。如果要改变这个行为，即要求每次碰到Latch Timeout,都要生成一个内存转储，我们可以用traceflag 838，如下面的命令：dbcc traceon(838, -1)

由于生成内存转储需要一点时间，有可能在生成内存转储的时候，Latch等待现象消失，在内存转储中，没有足够的信息。因此，我们可能要多次抓取内存转储，以对问题进行分析。

【案例分析】
下面是一个案例分析。我们在数据库的错误日志里，发现有下面的错误，并且产生了一个内存转储。
A time-out occurred while waiting for buffer latch -- type 2, bp 0000000088FBFA40, page 1:153568, stat 0xc0010b, database id: 10, allocation unit Id: 72057594047758336, task 0x000000000C8E2988 : 0, waittime 300, flags 0x1a, owning task 0x0000000005A2F048. Not continuing to wait.
们通过owning task的地址，结合内存转储分析，可以找到对应于该owning task的进程编号。其调用堆栈如下:

上面的调用堆栈显示，这是在增长数据库日志文件。由于数据库日志无法及时增长完成，因此导致了这个线程长时间拥有Latch锁而不释放，而其他进程无法及时获取相应的Latch锁。但查看该日志文件的增长设定，每次增长为300M。并不是以百分比增长。似乎没有什么大的问题。

通过收集磁盘性能信息【Windows性能监视器之CPU、硬盘、IO等监控方法详解 http://www.flybi.net/article/131 跟进这个监控下磁盘，看下是不是每次在Down前磁盘都会产生负荷】，我们发现，在出问题的点上，Avg. Disk sec/Read达到了1.444. 通常这个值在0.03左右比较正常。而1.444这个值显然偏差非常大。


【结论】

在这个点附近，磁盘压力变得比较大，所以磁盘性能变差)。导致日志文件增长300M无法及时完成。因此该线程拥有的Latch锁无法释放，而其他进程则等待该Latch导致超时。对于这个问题，有以下几种解决方案:

1、更换更好的磁盘。即使在有压力的情况下，性能也不会变得特别差。

2、修改日志文件增长幅度，以每次增长50M为幅度。期望日志文件增长能快速完成。

3、预先对日志文件扩容。这样，就不会有增长日志文件的情况发生。

由于短时间内无法更换磁盘，所以采用第三种方案，预先对日志文件扩容。采用这方案后，再也没有发生Latch timeout。

当然，对于每个Latch timeout的现象，原因可能有所不同，不过通常都是以磁盘性能或操作系统性能造成的原因居多。我们有时候不需要对内存转储进行分析，而是看一下操作系统的日志和数据库的错误日志，也可能找到原因。

【参考资料】
Buffer Latch Timeout的解析 http://blogs.msdn.com/b/apgcds ... .aspx
SQL SERVER 2008 周期性挂掉 http://q.cnblogs.com/q/64772/