SQL I/O操作学习笔记

SQL Server在以下情况需要和硬盘进行交互：

1.对于内存当中没有缓存的数据，第一次访问时需要将数据所在的页面从数据文件中读取到内存里。

读取时以页面为最小单位。出于性能考虑，读取的数据可能不仅仅包含所请求的内存页，同时还包含了预读的内存页。读取的数量与用户需要需要访问和数据页面数量和buffer pool里面现有缓存的数量有关系。

2.在任何Insert\Update\Delete提交之前，SQL server需要保证日志记录能够写入到日志文件。每次写的数量和数据的修改量有关。

3.当SQL Server做检查点(Checkpoint)的时候，需要将内存缓冲区中已经发生过修改的数据页面同步到硬盘的数据文件里。这时候主要发生写的操作，写的数量与上次checkpoint以来发生的数据修改量有关。

4.当SQL Server缓冲区(Buffer pool)空间不足的时候，会触发lazy writer，从而将内存里的一些很久没有使用过的数据页面以及执行计划清空。若这些页面上发生的修改未由checkpoint写回硬盘，lazy writer 会将其写回。这时候会发生一些写的操作。写的数量和Lazy writer需要清空的数据页面数量直接相关。

Checkpoint和lazy writer的区别：

两者都回写数据，checkpoint是为了保证数据库重新启动的时候能在一定的时间内完成恢复，不论是否有内存压力，都会被触发。而Lazy writer则是为了缓解buffer pool的压力才被触发。

5.对于一些特殊的操作，DBCC CHECKDB、Reindex、Update statistics、数据库备份等，通常也会带来比较大的硬盘读写。

 

SQL Server主要的硬盘读写行为：

操作类型	数据文件或日志文件	读或写
读入新数据页	数据文件	读
Insert\Update\Delete操作的日志记录	日志文件	写
Checkpoint	数据文件	写
Lazy writer	数据文件	写
CheckDB\Reindex\Update statistics\backup	数据\日志 文件	读写

 

 

 

 

 

 

 

对于IO操作的分析：

1.对于select的操作，理想情况下，只要数据存在内存，就不应该有任何的硬盘读写。

2.SQL Server发生读的数量，和内存是否有压力直接相关。当内存没有压力时，读的数量会想相对较少。而SQL发生的写的数量，和用户已经完成的数据修改量直接相关。如果一个数据库大部分操作都是查询，SQL发生的写操作会非常小。

3.影响SQL Server I/O操作行为的因素：

(1) SQL Server的recovery interval(sp_configure)：通常保留默认设置

interval时间过长，每次checkpoint产生的硬盘写的数量会比较多，这样可能会影响到SQL Server的性能。

Interval时间较短，checkpoint频繁，这样每次产生的写操作较少，对硬盘的冲击相对比较小。

(2) 数据/日志文件的自动增长和自动收缩：会对文件的读写速度产生严重影响，所以对于生产数据库，避免自动收缩、增长的发生

(3) 数据文件里页面碎片程度

 SQL Server以页面为单位操作数据。对于相同数据量，页面排的越紧凑，消耗的页面数目也就越少，同时SQL Server需要读入和写出的页面数目也就越少。如果页面碎片过多，SQL Server需要读写的页面也就更多，从而造成额外的硬盘读写量。

(4) 表格上的索引结构

通常存储同样多的数据，有聚集索引的表格明显会管理的好些。这里的差别会影响SQL Server需要做的读写数目。

(5) 数据压缩

SQL 2008以后只读文件组可以使用NTFS的压缩技术，以节省磁盘空间的消耗，读取压缩后的数据，在一定程度上可以降低I/O工作量，但是windows需要花额外的CPU和内存资源来将数据解压缩，然后返回给SQL Server。对于这个技术，不建议用在OLTP的系统上。对比低廉的磁盘价格，消耗CPU或内存，可能有点得不偿失。

(6) 数据文件和日志文件放在同一个盘：

通常数据文件读的操作较大，而对于日志文件，由于写的操作较多，建议放在写入速度比较快的磁盘上。例如一个Raid 10的分区，放在Raid 5上，IO问题就会比较明显。

(7) 一个数据文件组是否有多个文件，并且放在不同磁盘上。