第12章 sql server i/o问题 537
·
DiskI/O读写请求不能及时完成，常见2种症状。
o
超时错误 833 - 在SQL的errorlog里出现833错误，出现这个错误SQL的响应速度会受到影响，甚至整个服务的响应都会变得很慢
o
没错误，但反应缓慢 - 在sys.sysprocesses里有很多连接的状态是pageiolatch_*或writelog
12.1.
sql
server的i/o操作 537
12.1.1.
何时会有I/O现象？
·
对于内存中没有缓存的数据，第一次访问时需要将数据所在页面从数据文件（mdf文件）中读取到内存里 – 读数据文件。
·
在任何insert/update/delete提交之前，SQL需要保证日志记录能够写入到日志文件 – 写入日志文件 (然后才会写入到mdf文件中）。
·
SQL做checkpoint时，需要将内存缓存区中已经发生过修改的数据页面同步到硬盘中的数据文件里 – 写入数据文件。
·
当SQL buffer pool发生压力时，会触发lazy writer，主动将内存里的一些很久没有使用过的数据页面和执行计划清空。如果这些页面发生的修改还未由Checkpoint写回硬盘，lazy writer会将其写回到数据文件。
·
一些特殊的操作，例如：DBCC CHECKDB, reindex, update
statistics,数据库备份等，会带来比较大的硬盘读写 – 读写都有，数据或日志文件
12.1.2. 还有哪些什么因素会影响I/O？
· SQL的recovery interval (sp_config) - recovery interval控制着SQL多长时间进行一次checkpoint。
· 数据/日志文件的自动增长和自动收缩 - 文件的增长和收缩对SQL向这个文件的读写速度都会产生严重影响，所以对一个生产数据库，要避免自动增长和自动收缩发生
· 数据文件里面页面碎片程度 -如果页面碎片很多，每个页面并没有存放多少数据，那么SQL可能必须要读入和写出多很多的页面。这会造成额外的硬盘读写量。
· 表格上的索引结构 -有聚集索引的时候，在存储同样多的数据时，明显管理得好一些
· 数据压缩 - 在SQL2008以后，只读文件组可以使用NTFS的压缩技术，以节省磁盘空间的消耗。也在一定程度上降低I/O工作量。代价是Windows需要花费额外的CPU与内存资源将数据解压缩，然后返回给SQL。有点得不偿失，因而不推荐。
· 数据文件和日志文件是否放在同一个硬盘上 - 数据文件读得多，写的少。日志文件需要不断地写。建议：把数据文件和日志文件放在不同的硬盘上。日志文件放在写入速度比较快的磁盘上(例如，一个RAID10的分区)。如果放在比较慢的RAID5分区上，就比较容易遇到I/O问题
· 一个数据文件组是否有多个文件，并且放在不同的物理硬盘上 - 对于数据文件，SQL会将新数据按照同一个文件组的每个文件剩余空间的大小，按比例写入所有有空余空间的文件里。所以如果这些文件分布在不同的物理硬盘上，那么I/O工作也会分布到不同的硬盘。这样对整体性能有帮助。但是，对于日志文件，在一个时间点，SQL只会写一个日志文件。在不同的硬盘上创建多个日志文件对性能基本无帮助。
12.2.
系统级i/o问题判断 544
·
首先要检查Windows层面I/O是否有问题。如果系统的I/O比较繁忙，看看负荷是否来自SQL Server.
·
从Windows的层面进行分析，主要是依赖于性能监视器里的各个计数器。
·
关于磁盘，由2组计数器：LogicalDisk and Physical Disk。以分析PhysicalDisk下的计数器为主。
·
比较关键的计数器：
o
%disk time - 在采样期间，磁盘处于读写状态的百分比。理论上，这个值应该小于100%，而且应该是%disk read
time和%disk wirte
time的和。但在磁盘很忙时，这个计数器会远大于100%，因为一块磁盘可能不仅仅只有一个磁头，所以导致该计数器 >100%。所以这个计数器能够给DBA看到一个趋势线，只供参考。
o
%idle time - 在采样期间，磁盘处于空闲状态的百分比。当磁盘处于空闲状态时，它的值是100%。当磁盘在满载做操作时，它的值是0%。可以通过这个值反推出%disk time的真实值，
o
disk bytes/sec - 每秒钟磁盘读写的数量，是disk read
bytes/sec和disk write
bytes/sec的和。一个很保守的参考值是：好：20MB~40MB； 一般：10MB~20MB
o
avg.disk sec/transfer - 磁盘每一次读写的动作所花的平均时间
o
avg.disk sec/read - 磁盘每一次读所花的平均时间
o
avg.disk sec/write - 磁盘每一次写所花的平均时间。以上这三个值经常用来衡量磁盘速度：参考值: 很好：<10MS， 一般：10~20MS， 有点慢：20~50MS， 非常慢：>50MS
o
avg.disk queue length - 在某个时间点，磁盘队列的长度，也就是发出的磁盘操作正在等待被磁盘处理的请求数目。理论上讲，这个值不应该长时间地大于2。但如果存储后台有10000款磁盘，avg.disk queue length是100也不算高。
·
分析要点
o
现代存储体系的提高导致排错的方法与指标也要与时俱进。
o
分析者要综合看这些性能计数器不能从某一个计数器的值确定磁盘有没有瓶颈
o
看计数器的值，关键是要看磁盘繁忙的那些时间段，而不是整体时间的平均值。整体时间的平均值没什么意义。
12.3.
数据库引擎错误833 548
·
833错误 - SQL已经向磁盘发出读写请求，但是该请求返回所用时间已超过15秒。就是说，某个磁盘请求读写过了15秒还没做完
·
解决方法：判断是用户的I/O请求太大还是硬件性能问题。前者需要它的语句或换个时间执行。后者要从性能监视器日志中找答案，可以检查在出问题的那个时间段的%idle time，avg.disk queue length，disk bytes/sec等。
12.4.
i/o问题的sql server内部分析 550
·
先检查sys.sysprocesses,
sys.dm_exec_requests或者sys.dm_os_wait_stats中的waittype值（accumulative）
o
SQL先从内存里的buffer pool里找页面，如果没找到设置等待状态如下：等待状态：pageiolatch_ex
(写) 或pageiolatch_sh (读) ，然后发起一个异步I/O，将页面读入buffer pool。在I/O没做完之前，连接都会保持在pageiolatch_ex(写)或pageiolatch_sh(读)，I/O消耗的时间越长，等待的时间也会越长。所以当pageiolatch_ex
(写) 或pageiolatch_sh (读) 的waiting_tasks_count和wait_time_ms比较高的时候，SQL就经常要等待I/O，可能磁盘速度跟不上SQL的需要。
o
pageiolatch_ex (写) 或pageiolatch_sh (读) 针对数据文件的I/O等待。日志文件的I/O等待是 writelog ,当SQL要写日志文件而磁盘来不及完成时，SQL不得不进入等待状态直到日志记录被写入，才能提交当前事务。一般一次日志写入数量不会很多，磁盘应当能够按时完成，如果SQL经常要等writelog，说明磁盘的瓶颈比较严重。
SELECT wait_type, waiting_tasks_count,
wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE ‘pageiolatch%’ OR
wait_type LIKE 'writelog'
ORDER BY wait_type
·
然后搞清楚是哪个数据库，哪个文件老做I/O,是数据文件还是日志文件,是读还是写
o
sys.dm_io_virtual_file_stats
({database_id|null}, {file_id|null}) - 如果参数都是null，就返回所有文件的统计信息。这个动态管理函数是SQL自上次启动以来的历史统计信息。可以通过下面的查询了解哪个文件经常要做读（num_of_reads， num_of_bytes_read），经常要做写（num_of_writes，num_of_bytes_written），经常读写要等待（io_stall_write_ms，io_stall_read_ms，io_stall）
o
sys.dm_io_pending_io_requests可以显示当前SQL中每个处于挂起状态的I/O请求
--if you want to find the stall I/O at
the moment.
SELECT
database_id AS dbid,
FILE_ID AS fileid,
io_stall
AS iostall,
io_pending_ms_ticks AS iopendingmstick,
scheduler_address AS scheduleraddress
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS t1,
sys.dm_io_pending_io_requests AS
t2
WHERE t1.file_handle = t2.io_handle
o
还可以通过sys.dm_exec_requests和SQL Trace寻找大量Reads/Writes的语句。主要看与磁盘I/O有关的physical
reads和writes.
·
通过性能监视器和SQL
Server相关的计数器来了解SQL
Server I/O的操作频率。
o
Buffer manager – reveals information about
buffer pool-related I/O
ü page reads/sec and page writes/sec – 反映SQL每秒钟读写了多少页面。可以清楚了解由于buffer pool的行为带来了多少磁盘读写
ü lazy writes/sec – 反映lazy writer 为了清空buffer pool每秒钟做了多少页面写入动作
ü checkpoint writes/sec – 每秒钟从buffer pool里写入到磁盘上的dirty page数目
ü Readahead
pages/sec - 每秒钟从buffer pool里写入到磁盘上的dirty page数目
o
Access Methods – 除了读取和写入指令需要访问的页面，SQL还要做一些辅助工作，帮助指令完成，这些工作也会带来I/O动作
ü freespace
scans/sec - 在堆heap 结构里找到能够使用的空间。对没有聚集索引的表，SQL会以堆的形式存储。如果这个计数器很高，说明SQL在堆的管理上花费很多资源，应该考虑多建聚集索引。
ü page
splits/sec - 当表上有很多插入动作时，一些页面会被放满。当数据库的修改比较多，尤其是插入比较多时，page split是难免的。如果这个值比较高，对性能有影响，可以考虑定期重建索引，使用比较小的fill factor
ü page
allocations/sec - 当SQL需要创建对象，例如：表，索引时，分配给新对象的页面数量
ü workfiles/sec
- 当SQL为了完成某些操作而在内存中建立一个hash结构时（例如用hash算法作join）。该计数器就加一。如果某些hash结构比较大，SQL可能会将一部分数据写到硬盘里。所以这个值其实并不直接意味着磁盘读写。但是由于SQL通常只是在没有合适索引的时候才选择hash算法。所以DBA可以通过这个值了解数据库索引是不是有优化的必要。很多情况下索引优化能够大大降低SQL的读数量。做I/O问题时，这个计数器有必要看一下
ü worktables/sec
- 每秒创建的工作表数。例如，工作表可用于存储查询假脱机（query spool），LOB变量，XML变量，表变量，游标的临时结果
ü full scans/sec
- 每秒钟SQL做的全表扫描数目。如果一个表设计良好，就应该没有很多的全表扫描。而全表扫描通常意味着比较大的内存使用和比较多的I/O请求。
ü index
searches/sec - 每秒钟检索索引的次数，也就是利用索引完成指令的数目
o
Database
Log Activities
ü log
flushes/sec - SQL每秒钟在这个数据库上做的日志写的次数
ü log bytes
flushed/sec - SQL每秒钟在这个数据库上做的日志写的数量（bytes）
ü log flush wait
time - 写入日志的动作曾经因为磁盘来不及响应而遇到的等待时间。这种等待会导致前端事务不能提交，所以会严重影响SQL的性能。正常情况下，这个值大多数时间都是0
ü log flush
waits/sec - 在每秒钟提交的事务里，有多少个事务曾经等待过日志写入完成。正常情况下，不应该有等待，如果有等待检查存放日志文件的磁盘性能
o
两个计数器，粗略分析SQL的整体性能,只有在负荷均衡的情况下有点参考价值。
ü MSSQL: SQL
Statistics -Batch Request/sec : SQL每秒钟完成的批处理数目
ü MSSQL: Databases
Active Transactions: SQL里打开的，还没有提交的事务数目
12.5.
硬盘压力测试 558
·
完整的测试不仅仅是复制一个文件看看有多快
·
要多方位测试，尤其对RAID和SAN这样的磁盘系统
o
不同类型的读写操作，每次读写transfer的数据量会不一样。SQL不同的操作，会有不同的读写量。像Index seek这样的操作，可能以8KB为单位比较多；像read ahead,
table scan这样的动作，可能64KB甚至更大的单位更多。所以管理员要测试磁盘在不同的transfer size情况下，它的表现是不是都很好。
o
有些磁盘的读写速度并不一样。例如：raid5，它的写会比读要慢。所以读和写的速度都要测试到
o
现在的SAN和其它物理存储设备会有很大的读写缓存，类似于服务器上的内存。如果每次读写的量比较小读写缓存就可以处理了，可能不会发生真正到存储介质上的读写。这样的速度，会比真正读写速度快很多。所以在测试时，一定要保证读写数量是读写缓存大小的2到4倍
o
SQL的不同数据文件，可以放在不同的磁盘上。SQL的读写工作可以由若干个磁盘共同完成。整体的速度会有所提升。但是提升多少呢？做硬盘压力测试的时候，要先测试单个磁盘的速度，再测多个磁盘共同工作时的速度
o
有些磁盘如果工作压力一直比较大，会调整它的行为来适应。所以压力测试一定要持续一段时间。对于一个初步测试，5到10分钟的满负荷运作是至少的。
·
微软免费提供的磁盘压力测试工具：SQLIO
o
虽然叫SQLIO，但是这个工具跟SQL没有什么直接关系，可以在任何一台没有SQL的机器上运行。
o
独特功能：
ü 能够指定压力测试是读还是写
ü 指定每次是连续的读/写，还是随机的读/写
ü 指定每次读/写的大小
ü 指定每个测试的时间长短
ü 指定读/写的磁盘位置，路径和文件大小。支持多个磁盘同时读/写测试
12.6.
圣剑——windows提供的新的底层监测功能 564
·
问题场景：DBA认为是硬件出了问题，硬件商认为没有问题。现在Windows Server 2008和R2，只要安装2个补丁（KB979764 for 2008，978000 for R2），Windows就可从更底层来监测磁盘读写的性能。
·
原理：上述补丁对于storport.sys做了一定的改动，这些改动会记录下操作系统发给磁盘子系统I/O请求之后，请求完成情况的详细时间统计信息。用户要提供一个阈值。任何一个I/O请求磁盘子系统的完成时间超过了这个阈值，Windows就会产生一个事件（Event）。如果某个磁盘子系统在某段事件产生了很多这样的事件，说明该磁盘子系统存在性能问题。总之，用户可以通过跟踪这些事件，了解磁盘子系统的健康情况和相应速度。
·
具体操作:
o
Run
perfmon
o
在Data Collector Sets下选择Event Trace Sessions
o
Create
a new Data Collector Set
o
选择Microsoft-Windows-StorPort
o
在Properties中选择IOPerNotification
o
选择Filter，输入阈值
o
完成后选择Start，直到问题出现
o
等问题出现后，stop it。
o
用事件日志查看器打开日志，查找EventID为13的事件。如果其下的RequestDuration很大，极可能就是磁盘存储的问题。
12.7.
小结 570
·
日志文件以写为主，工作量主要有用户的事务量决定。如果不能及时写入，连接会进入WriteLog的等待状态，对性能影响较大。
·
数据文件以读为主，读入的数量由用户需要访问的数据量和内存缓冲区里现有的数据量共同决定。如果磁盘问题的瓶颈是在读上面，往往可以转化为一个内存问题或者是一个语句优化的问题。而数据文件写入的数量主要由用户的修改量决定，没有多大的调整余地。要提高性能，需要提高磁盘的写入速度。
·
Troubleshooting思路
o
Windows和SQL Server有无明显内存瓶颈。如有，磁盘繁忙是正常的。要先解决内存瓶颈，I/O瓶颈就会跟着改善。
o
分析磁盘子系统的整体性能，读写吞吐量是否正常，在哪些时段出现过读或写的瓶颈，每次多久，在瓶颈出现时，磁盘子系统的各项指标是否正常。如果磁盘子系统的速度慢于预期，要想办法做硬件的调整，提高速度。
o
SQL
Server当时做了多少读写？系统的I/O问题，在多大程度上是由SQL Server引起的。
o
如果I/O问题主要是由SQL Server引起，是哪种操作引起的（Page Reads, Page Writes, Lazy Writes, Checkpoint, Log
Writes）?
o
分析引起这类SQL Server操作的指令，看看能不能降低它引起的I/O数目。
o
在现实环境里，解决I/O瓶颈，主要是（1）解决内存瓶颈，（2）调整应用和数据库设计，从而降低I/O请求数目，（3）升级硬件，提高速度。
