一. 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