最近遇到一个SQL Server服务器响应极度缓慢,并且出现客户端请求报错的情况,在数据库中的errorlog中出现磁盘请求超过15s才完成的error消息。 对于此类问题,到底是存储系统或者磁盘的
最近遇到一个SQL Server服务器响应极度缓慢,并且出现客户端请求报错的情况,在数据库中的errorlog中出现磁盘请求超过15s才完成的error消息。
对于此类问题,到底是存储系统或者磁盘的故障,还是SQL Server 自己的问题,亦或是应用程序引发的呢?又要如何解决?
本文将对引起此问题的某一方面的因素进行简单的分析,但是无法涵盖所有潜在的可能性,因此遇到类似问题还要做具体的分析。
SQL Server中的磁盘请求超时
该错误的英文版的错误信息如下:
SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database id %d. The OS file handle is 0x%p. 0
The offset of the latest long I/O is: %#016I64x
中文版的错误信息如下
SQL Server 已遇到 %1! 次对数据库 ID %4! 中的文件 [%3!] 进行的 I/O 请求超过 %2! 秒才完成。操作系统文件句柄为 0x%5!。最新的长时间 I/O 的偏移量为: %6!
参考message信息中的833号错误消息
具体的833 error 申请磁盘请求超时现象
具体报错情况如下:
SQL Server 已遇到 m 次对数据库 n 中的文件***进行的 I/O 请求超过 15 秒才完成。操作系统文件句柄为 ***。最新的长时间 I/O 的偏移量为: ***
也就是说在数据库的文件自动增长的过程中遇到了错误。
。
比较有意思的是某DBA将此错误信息报告给负责存储(SAN存储,并非挂的磁盘)的工程师,认为是可能存储系统存在故障或者不稳定造成的,
存储工程师认为存储没有问题,检查服务器后说服务器不正常,内存“几乎占满”,对于数据库服务器,内存“几乎占满”的情况可以说是完全正常的,鉴于负责存储的工程师并非专业DBA,对于SQL Server数据库服务器的内存使用可能不是太了解,提出此疑问也可以理解。
因为数据库服务器使用的存储是高性能的SAN存储,存储是作为一个服务存在的,有N多服务器共同来使用的,其他服务器并没有出现磁盘请求,不太可能说某一台服务器会出现疑似“存储故障”就简单认定为是存储故障。
那么究竟原因在什么地方呢?
数据库引擎错误833的含义
首先来看这个833错误的具体含义是什么,就不自己装13解释一通了,那本经典的书上写的很清楚了。
总之,意思就是,SQL Server在请求磁盘读写的时候,遇到磁盘繁忙或者其他一些因素,超过了15秒还没有完成
比如数据的读写的时候需要向磁盘发起请求,而磁盘正忙或者其他问题,来不及或者相应的不够及时,这样无疑会严重影响SQL Server对外提供服务器的响应时间。
上面简单分析了,因为该问题并非普片发生的,存储系统不太可能出现问题,那就很有可能定位到当前服务器自身的因素了。
原因分析
因为是专门的SQL Server服务器,没有其他应用程序的请求,很有可能跟向sqlserver数据库发起的请求有关。
其实发生这个问题之前,早就有预兆了,平时还算稳定的服务器(CPU很少超过60%,内存的PLE也可以稳定在20分钟以上,磁盘IO延迟较低等等),只是偶尔会存在抽风一阵子的情况
抽风的时候表现为CPU狂飙到80%左右,内存的PLE会严重下降,IO延迟严重增高。
现在只能从SQL Server的Session入手,在观察SQL Server中的活动Session的时候,发现某一类的SQL语句的查询时间非常长,
平时这类SQL在某一个时间段内执行的频率还算比较高。
但是正常情况下,这类SQL的执行效率还是比较高的,为什么突然就变的非常之底?
在检查活动Session的对应的执行计划的时候,发现这类活动Session的等待状态都是IO等待(PAGEIOLATCH_SH),同时SQL的执行完全是意料之外的执行方式。
因为类似查询还是执行的比较频繁的,此类Session会从不同的客户端发起,一旦SQL的执行效率降下来,服务器上会积压大量的活动Session
为什么平时执行的好好的SQL语句突然就变的很慢很慢,
原因就在于在某一点,SQL Server自动触发了统计信息的更新,但是这是一个比较大的表,但是默认统计信息更新的取样比例是不够的,如果取样百分比不够,这个统计信息完全是不可用的。
一旦自动收集统计信息完成之后,会根据当前收集到的统计信息,向之前的SQL语句发出一种它认为高效的方式(table scan而不是index seek),其实这种方式并非是合理的,
由此引发对应的SQL利用一种并非合理的执行计划来实现查询,同时会引发Session的拥堵,客户端发过来大量的Session同时在利用一种低效的方式缓慢执行。
所以CPU会飙升,IO延迟增加,内存的PLE严重下降。
由此也不难理解,数十个查询的Session正在以一种不合理的方式疯狂地想磁盘发出请求,磁盘正在忙于活动Session的数据请求,出现无法响应因为数据或者索引文件的自动增长请求,造成一开始说的问题。
最后经过索引重建(促使统计信息更新,当然纯粹的统计信息更新也可以)解决,长期预防的话,需要安排job人为地定义统计信息更新的阈值以及取样百分比。
总结:
数据库服务器上的问题,很多问题都是一个连锁反应的过程,对应观察到的一部分现象,很有可能并不是表面上的反应的那样(磁盘请求超时,问题出在存储上?)
专业的位置上必须要有专业的素养,比如一开始DBA误以为是存储问题,存储工程师认为服务器内存用满了是不正常的等,其实都不是问题的根本原因所在。
面对问题,要追本溯源,找出来最根本的原因,才是解决问题的关键。