sqlserver运行过程中,需要不断与硬盘进行交互。如果IO出现问题,可能严重影响数据库性能,并且容易遇到数据库损坏问题。IO问题在数据库中有两个明显特征:
- 出现833错误
- 遇到大量PAGEIOLATCH_*或WRITELOG等待,几乎所有sql都变慢
本篇介绍常见的一些IO操作、IO问题分析及处理方法。
一、 sqlserver的IO操作
1. sqlserver常见的IO操作
- 对buffer pool中没有的数据,第一次访问时需从数据文件加载到内存
- DML操作提交前,必须先将日志记录写入日志文件
- 发生Checkpoint时,需将缓存中脏数据写到硬盘(注意它不会从缓存中移出脏页,只是在页头将写出的页面标记为干净的页面)
- buffer pool空间不足时,会触发lazy writer将最久未使用的页面及执行计划刷到硬盘(真正从缓存中移出脏页)
- 一些特殊操作,如dbcc checkdb、reindex、update statistics、backup等,可能会有大量硬盘IO
2. 影响sqlserver IO操作的因素
- recovery interval参数:影响Checkpoint频率,一般不需调整
- 数据/日志文件自动增长和收缩:频繁的自动增长和一次增长量特别大都会影响性能,需要注意平衡。另外对于生产环境,不应该启用自动收缩,这可能引发长时间阻塞,带来严重的性能问题
- 表的索引结构:有无聚集索引
- 数据压缩:可以一定程度减少IO,但会增加CPU开销
- 一个数据文件组是否有多个文件,并且放在不同物理磁盘上
- 数据文件页面碎片程度:如果碎片很多,每个页面实际放不了多少数据,sqlserver可能需要额外读入/写出大量页面,造成额外的硬盘读写量。
dbcc showcontig(表名)
rebuild聚集索引可以消除表碎片
alter index ind_demo on demo rebuild;
二、 系统级IO问题判断
在检查sqlserver IO问题之前,建议先检查windows层面IO性能,如果系统层面IO压力非常大或磁盘响应非常慢,sqlserver基本难以幸免,检查方法还是通过windows计数器。
关于磁盘,有两组计数器:LogicalDisk和PhysicalDisk。LogicalDisk按照逻辑盘符记录,PhysicalDisk以物理磁盘为单位。
常用磁盘计数器
1)Idle Time:磁盘处于空闲状态的百分比。当磁盘满负荷时,其值为0。
2)Disk Bytes/sec:磁盘每秒读写量
3)Avg.disk sec/read:磁盘每次读操作平均时间
4)Avg.disk sec/write:磁盘每次写操作平均时间
这两个参数很重要,它们能很好地反应磁盘速度。参考值如下:
- <10ms:很好
- 10~20ms:一般
- 20~50ms:较慢
- >50ms:非常慢,高负载的生产环境一般无法接受
5)Avg.Disk Queue Length:某个时间点磁盘队列的长度,即正在等待磁盘处理的请求数。
理论上这个值不应该长时间超过2,通常我们更关注繁忙时该计数器的值而非整体平均值。另外随着磁盘技术发展,这个值已经较少单独用于性能分析,需要结合其他计数器一起看。
三、 著名的833错误
如果服务器或者sqlserver有严重的IO问题,可能会在错误日志看到以下信息:
它的含义是,sqlserver向磁盘发出读/写请求,但该IO请求超过15秒还未完成。
对于绝大多数系统而言,长达15秒的IO响应时间是无法接受的,这个错误如果长时间出现,系统基本上没法用的。
上面这个例子问题发现在硬盘层,如果问题是由于sqlserver过多IO请求导致的,又该如何处理呢?
四、 IO问题的sqlserver内部分析
1. 检查等待事件
去查是否有大量PAGEIOLATCH_%与WRITELOG等待, 这里不再重复了
2. 检查文件IO信息
- sys.dm_io_virtual_file_stats:该动态管理函数可获得哪些文件经常被读、写、经常要等待等信息。
sys.dm_io_virtual_file_stats( { database_id | NULL }, { file_id | NULL })
可使用如下sql查看
SELECT
--virtual file latency
ReadLatency =
CASE WHEN num_of_reads = 0
THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,
WriteLatency =
CASE WHEN num_of_writes = 0
THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,
Latency =
CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,
--avg bytes per IOP
AvgBPerRead =
CASE WHEN num_of_reads = 0
THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,
AvgBPerWrite =
CASE WHEN io_stall_write_ms = 0
THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,
AvgBPerTransfer =
CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)
THEN 0 ELSE
((num_of_bytes_read + num_of_bytes_written) /
(num_of_reads + num_of_writes)) END,
LEFT (mf.physical_name, 2) AS Drive,
DB_NAME (vfs.database_id) AS DB,
vfs.*,
mf.physical_name
FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
--WHERE vfs.file_id = 2 -- log files
-- ORDER BY Latency DESC
-- ORDER BY ReadLatency DESC
ORDER BY WriteLatency DESC;
- sys.dm_exec_io_pending_io_requests:返回当前sqlserver中每个处于挂起状态的IO请求
select database_id,file_id,io_stall,io_pending_ms_ticks,scheduler_address
from sys.dm_io_virtual_file_stats(NULL,NULL) t1,sys.dm_exec_io_pending_io_requests as t2
where t1.file_handle=t2.file_handle;
3. 相关计数器
到底是磁盘问题引起sqlserver慢,还是sqlserver IO太大导致磁盘延迟过高,需要结合实际情况及多项监控指标去看,千万不要只看一两个指标就下结论。
参考:《sqlserver 2012 实施与管理实战指南》