一. 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 database_name, f.fileid as file_id, f.filename as file_name,
i.num_of_reads, i.num_of _bytes_read, i.io_stall_read_ms,
i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms,
i.io_stall, i.size_on_disk_bytes
from sys.database 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
select database_id, file_id, io_stall, io_pending_ms_ticks, scheduler_address -- check every pending I/O request
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
declare @name nvarchar(100)
declare @cmd nvarchar(1000)
declare dbname cursor for
select name from master.dbo.sysdatabases
open dbname
fetch next from dbname into @name
while @@fetch_status = 0
begin
set @cmd= 'select b.databse_id, db=dbname(b.database_id), p.object_id, p.index_id, buffer_count=count(*) from ' + @name + '.sys.allocation_units a, '
+ @name + '.sys.dm_os_buffer_descriptions b, ' + @name+ '.sys.partitions p
where a.allocation_unit_id=b.allocation_unit_id
and a.container_id=p.hobt_id
and b.database_id=db_id(''' + @name+ ''')
group by b.database_id, p.object_id, p.index_id
order by b.database_id, buffer_count desc'
exec(@cmd)
fetch next from dbname into @name
end
close dbname
deallocate dbname
go
五. 和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
六. 硬盘压力测试：
SQLIO 下载地址：http://www.microsoft.com/en-us/download/details.aspx?id=20163#QuickInfoContainer
SQLIO 已经升级成 DiskSPD。 在上面的链接中下载readme.pdf, 该文件中有下载地址。 Diskspd-v2.0.17.zip 解压之后如下图：
UsingDiskspdforSQLServer.docx里面有详细的使用说明和分析方法。