SQL Server I/O 问题的诊断分析
一. SQL Server 什么时候和磁盘打交道：
1. SQL 需要访问的数据没有在Buffer pool中，第一次访问时需要将数据所在的页面从数据文件中读取到内存中。（只读）
2. 在insert/update/delete提交之前， 需要将日志记录缓存区写入到磁盘的日志文件中。（写）
3. Checkpoint的时候，需要将Buffer pool中已经发生修改的脏数据页面同步到磁盘的数据文件中。（写）
4. 当Buffer pool空间不足的时候， 会触发Lazy writer， 主动将内存中的一些很久没有使用过的数据页面和执行计划清空。如果这些页面上的修改还没有被检查点写回硬盘， Lazy writer 会将其写回。（写）
5. DBCC checkDB, Reindex, Update Statistics, database backup等操作， 会带来比较大的硬盘读写。（读/写）
二. 哪些SQL 配置会对I/O有影响：
1. （ Recovery Interval， 默认60秒）（Checkpoint pages/sec + Avg. Disk Queue Length + Batch Requests/sec)
2. 数据文件和日志文件的自动增长和自动收缩。对于生成数据库，要避免自动增长和自动收缩。
3. 数据文件中的页面碎片程度 （Clustered index) : dbcc showcontig('table_name') -- avg. Page Density(full) 碎片多，读取/写入的页面多（set statistics io on -- logical reads)
4. 表上的索引结构： 聚集索引的表和堆表的存储管理不同。
5. 数据压缩： 可以减少I/O， 但会消耗CPU和内存资源。
6. 数据文件和日志文件分别放在不同的硬盘上，日志要放在写入速度较快的硬盘上， 如RAID10
7. 数据文件可以有多个分别放到不同硬盘上的文件， SQL server会将新数据按照同一个文件组的每个文件剩余空间的大小， 按比例写入到所有有剩余空间的文件中。 而日志文件则不同， 在一个时间点只会写一个日志文件。 所以在不同的硬盘上建日志文件对性能没有什么帮助。
三. 操作系统I/O问题的诊断：
1. 在判断SQL I/O问题之前，先看看Windows层面I/O是否正常。 如果很忙，再确认是不是SQL造成的。
2. LogicalDisk and PhysicalDisk:
%idle time:
%disk time: = %disk read time + %disk write time
%disk read time
%disk write time
Avg. disk sec/read
Avg. disk sec/write： 很好：<10ms 一般：10-20ms 有点慢：20-50ms 非常慢：> 50ms
Avg. disk bytes/transfer
Avg. disk queue length: 不应该长时间>2 (SAN 盘就不同）
Avg. disk read queue length
Avg. disk write queue length
Disk Bytes/sec: 好：20-40MB 一般：10-20MB
Disk Read Bytes/sec
Disk Write Bytes/sec
Disk Transfers/sec
Disk Reads/sec
Disk Writes/sec
Current Disk queue length
四. SQL Server 内部分析：
1. 检查sys.dm_exec_requests 或者 sys.dm_os_wait_stats:
select wait_type,
waiting_tasks_count,
wait_time_ms
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH' -- PAGEIOLATCH_EX(写） PAGEIOLATCH_SH(读） 主要反映数据文件上的I/O等待
order by wait_type
2. 找出那个数据库哪个文件总做I/O，是数据文件还是日志文件， 经常读，还是经常写：
select db.name as dbname, f.fileid as [fileid], f.filename as [filename], convert(numeric(5,2),i.io_stall_read_ms/(num_of_reads+1.0)) AS 'Avg Read ms/Transfer', convert(numeric(5,2),i.io_stall_write_ms/(num_of_writes+1.0)) AS 'Avg Write ms/Transfer', i.num_of_reads, i.num_of_bytes_read/1024/1024 as num_of_mb_read, i.io_stall_read_ms, i.num_of_writes, i.num_of_bytes_written/1024/1024 as num_of_mb_write, i.io_stall_write_ms, i.io_stall, i.size_on_disk_bytes from sys.databases db inner join sys.sysaltfiles f on db.database_id=f.dbid inner join sys.dm_io_virtual_file_stats(NULL,NULL) i on i.database_id=f.dbid and i.file_id=f.fileid --order by i.num_of_reads desc --order by i.num_of_writes desc
check every pending I/O request
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_io_pending_io_requests as t2 where t1.file_handle=t2.io_handle
-- check which table in buffer pool and how mang size of it
五. 和SQL相关的计数器：
1. Buffer manager:
page reads/sec and page writes/sec
Lazy writes/sec
Checkpoint writes/sec
Readahead pages/sec
2. Access Methods:
Freespace scans/sec
Page splits/sec
Page allocations/sec
Workfiles/sec
Worktables/sec
Full scans/sec
Index Searches/sec
3. Database(Log Activity)
Log flushes/sec
Log Bytes flushed/sec
Log flush wait time
Log flush waits/sec
六. 硬盘压力测试：
可以使用 DiskSPD