作者：obuntu
很喜欢SQL Server里面4步定位性能问题的理论，具体如下：
1，资源瓶颈
i. 内存 ii. CPU iii. IO
2, Tempdb瓶颈
3，找出执行慢的语句，可以通过三个方面来寻找
i. 统计信息 ii. 缺失索引 iii. 阻塞
4，缓存执行计划分析
更多信息，可以参看此篇文章http://blogs.msdn.com/b/jimmymay/archive/2008/09/01/sql-server-performance-troubleshooting-methodology.aspx
可以看到，一遇到系统性能问题时，第一步是确定资源是否存在瓶颈，在CPU,内存，IO 三者之间，最容易形成瓶颈的是IO子系统。其实IO子系统的内涵是很深的，能够影响IO子系统性能的因素有磁盘的数目，大小，和转速；文件分配单元大小(file allocation unit size)；HBA；网络带宽；磁盘缓存；控制器；是否使用SAN（storage area networks）；RAID级别；总线速度；IO通道等等。
作为SQL Server的使用者，通常很少会去调整IO子系统的配置，一则重视不够，二是缺少这方面的相关知识和技能。但了解这方面的相关问题还是很有必要的，除了可以更好发挥硬件的作用外，在碰到系统性能问题时，也能很好的进行定位分析。
目录
IO子系统相关概念
SQL Server IO 相关概念
性能监视器里的IO子系统计数器
SQLIO
关于IO的一些最佳实践
小结
参考资料
IO子系统相关概念
A，磁盘
磁盘从过去的几十年里，取得了快速的发展，从ATA,SATA,SAS,到现在的SSD，每次技术的变革都带来了磁盘的性能提升。现在应用最广泛的应该是15K转的SAS盘了，对于这样的盘，一些传统的磁盘概念还是不变，如磁道，扇区。
现在的硬盘，一般是由重叠的一组盘片组成，每个盘片又被划分为数目相等的磁道，同时对这些磁道进行编号。每个磁道被等分为若干个弧段，这些弧段就是扇区，扇区的一般为512bytes，也有1K，2K，4K大小。同时不同盘片上，相同编号的磁盘则组成柱面。柱面数等于磁道数，盘面数等于总的磁头数，因此硬盘上有所谓的CHS概念，即Cylinder（柱面）、Head（磁头）、Sector（扇区）。磁盘的容量等于柱面数*磁头数*扇区数*扇区的大小。
IO的模式有顺序读写和随机读写，磁盘在处理这2种读写方式时，所表现出来的性能是不一样的。一般来说，在顺序读写上，现在10K转的磁盘，能够获取40mb/s~80mb/s的传输速率；15K转的磁盘，能够获取70mb/s~125mb/s的传输速率。对于随机读写来说，其性能取决于磁盘的转速和寻道时间。一个10K转的磁盘完成一个完全的旋转需要6ms（1*60*1000/10000）。硬盘的等待时间，又叫潜伏期(Latency)，是指磁头已处于要访问的磁道，等待所要访问的扇区旋转至磁头下方的时间。平均等待时间为盘片旋转一周所需的时间的一半，一般应在4ms以下，所以一般认为在磁盘上的等待时间为3ms，对15K的磁盘则是2ms。
还有一个影响磁盘性能的因素是寻道时间，它是指硬盘在接收到系统指令后，磁头从开始移动到移动至数据所在的磁道所花费时间的平均值。现在10K转的磁盘在读上，平均的寻道时间为4.6ms，在写上，平均的寻道时间为5.2ms。一个15K转的磁盘，读的平均寻道时间为3.5ms，写的寻道时间为4.2ms。
如对于小块的8kb读取，传输的时间大概为0.1ms，忽略其他可以忽略的因素后，结合上述讨论，对于随机读，我们可以得出总的时延大概为8ms（10K磁盘）和5.6ms(15K转磁盘)，所以磁盘在一般随机的小块读的性能大概为 125 IOPS（10K磁盘）和175 IOPS（15K磁盘）。
上述的情况是较为理想的。如果磁盘上的数据集中在某块小的区域，会降低磁盘的平均寻道时间，性能还会更好。但如果多个IO请求同时发生的话，磁盘还需要对多个IO进行序列化，排序，从而在越高的吞吐下，其时延会更长。一般来说，如果数据分布于整个磁盘，队列深度(queue depth)越高，时延更长，队列深度为4时，时延会达到20ms，队列深度为32时，时延能达到100ms。队列深度指的是磁盘上能并行运行的IO个数。 因此，对于队列深度的值建议使用2，当然不同的存储，不同的系统也会有不同的建议值，设置的时候可以参考相关的资料。但也有个情况需要注意，那就是如果数据只分布于磁盘上的某一小块区域，如5%，那么时延并不会随着队列深度的增加而大幅增加，一般是队列深度为8时20ms，队列深度为16时40ms，而且随机读的性能也有很大的提升，每个IOPS可以达到400.这个特点在处理强事务的能让你获得很大的弹性空间。
B，RAID
现在真实的企业应用环境很少单独使用一个一个磁盘来存放文件，而是采用RAID技术。RAID技术能带来性能的提升和有效的容错能力。简单的说，RAID是一种把多块独立的硬盘（物理硬盘）按不同的方式组合起来形成一个硬盘组（逻辑硬盘），从而提供比单个硬盘更高的存储性能和提供数据备份技术。组成磁盘阵列的不同方式称为RAID级别（RAID Levels）。
RAID 有很多种级别，也就是说磁盘有多种组合方式。现在比较常用的是RAID10和RAID5，RAID10的整体性能会比RAID5来得高，但其价格也更昂贵。决定使用哪种RAID级别，对系统的性能影响也很大，因此需要经过充分测试，权衡自己的实际情况并作出选择。
C，其他概念
文件分配单元(file allocation unit)大小，也就是簇的大小，一般是扇区大小的整数倍，如簇的大小是4K，扇区的大小为512bytes，那么一个簇就会使用到8个扇区。在进行硬盘格式化时，可以使用format命令的/A:size 选项来指定。一般情况下，在SQL Server数据文件和日志文件上比较合适的大小是64K，但有时候32K也能提供较好的性能，因此设置该值之前，最好也进行充分的测试来决定。下面是一个查看当前文件分配单元大小的例子。
C:\Documents and Settings\Administrator>fsutil fsinfo ntfsinfo d:
NTFS 卷序列号 : 0xde500ef9500ed7e3
版本 : 3.1
区数量 : 0x0000000012c03620
簇总数 : 0x0000000012c03620
可用簇 : 0x000000001098efb6
保留总数 : 0x0000000000000000
每个扇区字节数 : 512
每个簇字节数 : 512
每个 FileRecord 段的字节数 : 1024
每个 FileRecord 段的簇数 : 2
Mft 有效数据长度 : 0x0000000004a68000
Mft 起始 Lcn : 0x0000000000600000
Mft2 起始 Lcn : 0x0000000009601b10
Mft 区域起始 : 0x0000000000625460
Mft 区域结尾 : 0x0000000002b80800
配置RAID的时候，有个可以手工设定的参数：Stripe size. 逻辑驱动器的Stripe size，代表控制器每次写入一块物理磁盘的数据量，以KB为单位。 不同Stripe size的选择直接影响性能，如IOPS和吞吐量。 Stripe size值小，通过多块磁盘响应多个I/O请求，可以增加I/O访问速率（IOPS）；Stripe size值大，通过多块磁盘响应一个I/O请求，可以增加数据传输速率（Mbps).为了获得更高的性能，要选择条带的容量等于或小于操作系统的簇的大小。大容量的条带会产生更高的读取性能，尤其在读取连续数据的时候。而读取随机数据的时候，最好设定条带的容量小一点。
因此，可以看到上述值得设定对SQL Server的性能也是有帮助的，但很难有一个合适的推荐，有时候大部分还是保持默认的，如果确实遇到这方面的设置需求，最好请教相关产品的厂商，或者自己进行充分测试。
SQL Server IO 相关概念
SQL Server 引擎有自己的磁盘IO内部管理机制。理解SQL Server的IO处理机制是很有必要的。微软有2部非常好的白皮书，叫《SQL Server I/O Basics Chapter 1》《SQL Server I/O Basics Chapter 2》，对此进行了深入阐述，如果对这方面有兴趣的朋友，是不能错过。但只有英文版，两份加起来有100多页左右。下面对SQL Server IO的一些要点进行简要阐述，更多详情，参考这两份白皮书吧。
Write Ahead Logging (WAL) Protocol
SQL Server在写入数据文件的数据时，需要事先将日志文件的内容写入磁盘上的事务日志文件，这就是WAL机制。这个机制可以保护和固化所进行的事务。只有这样，才能实现事务的durability 特性。SQL Server实现WAL机制是通过使用Createfile 的FILE_FLAG_WRITE_THROUGH标识来实现的。
Synchronous vs Asynchronous I/O
同步I/O指的是I/O API 会等待I/O请求完成后，才进行下一个处理；异步I/O指的是I/O API只需发出I/O请求，然后继续处理其他内容，并在一会之后回头查看该I/O是否已经完成。
SQL Server 上98%使用的是异步I/O，这允许SQL Server在写入或者读取一个页之后继续有效的使用CPU和其他资源。Windows平台处理异步I/O是使用了OVERLAPPED这个结构来保存相关的I/O信息，并使用HasOverlappedIOCompleted来标识I/O是否已经完成。在SQL Server 2005后引入了sys.dm_io_pending_io_requests 这个动态管理视图，其中的IO_PENDING列与HasOverlappedIOCompleted对应。
Scatter / Gather I/O
在SQL Server 2000以前，SQL Server的checkpoint要将buffer pool的脏数据页写入磁盘时，需要维护一个脏数据页的列表，然后按顺序的写入脏数据页，因此如果某个页在写入时遇到I/O问题，则会引起整个checkpoint的性能下降。因此SQL Server 2000以后引入了Scatter/Gather I/O的方式，Scatter 是指从磁盘往内存读取数据页时，不用在内存分配连续的页，可以将页分布在buffer pool里不同的地方，通过调用ReadFileScatter这个API来实现的；Gather指的是从内存往磁盘写数据页时，不必维护之前的那种脏数据页列表，而是在扫描整个buffer
pool后，直接将脏数据页写入磁盘某块连续区域，通过调用WriteFileGather这个API来实现。很明显可以看到这种处理方式更为有效，不仅应用在SQL I/O路径上，还应用在Page File 上。
Sector alignment, Block Alignment
在SQL Server里面，写入事务日志时，并不是按照page的大小（8KB）来写入的，而是按照扇区的大小来写入的。之所以采用扇区来写入是为了防止事务日志被re-write，从而导致事务日志损坏。在扇区上会维护一个校验位，在写入日志文件时，通过检查该检验位来确定该扇区是否可以写入日志，从而保证日志的有效性。
扇区大小对用户而言其实是透明的，也就是SQL Server会自动根据磁盘的扇区大小作出相关处理，例如从一个扇区512bytes的还原到扇区为1024bytes时，后续的日志写入就是按照1024bytes了。
由于块的最小单位是8KB，又因为在一个磁盘，默认情况下前63个扇区为隐藏扇区，用来存储MBR（主引导信息），也就是隐藏的扇区大小为31.5KB。这个东西叫做分区偏移，如果未进行有效设置时，会导致额外的I/O产生，进而影响性能。这个问题，后续我们会进行详细展示。
一般来说，确定合适的扇区大小，可以通过一个计算公式来进行，((Partition offset) * (Disk sector size)) / (Stripe unit size) ，确保结果为整数。例如在一个stripe size 为256的情况下，至少要在512个扇区的偏移后，才能保证公式结果为整数，因此至少需要设置256KB的偏移大小。
(63 * 512) / 262144 = 0.123046875
(64 * 512) / 262144 = 0.125
(128 * 512) / 262144 = 0.25
(256 * 512) / 262144 = 0.5
(512 * 512) / 262144 = 1
要查看一个文件上的扇区大小，还可以使用dbcc fileheader(‘dbname’)来查看。
Latching and a page: A read walk-through
Latch，是种轻量级的锁，用来保护各种系统资源，在I/O上则是用来保护内存中的数据页，保证数据的一致性。在SQL Server里，有2类IO方面的latch，一种是PAGE_IO*_LATCH，一种是PAGE*_LATCH，这两类等待类型能够用来定位I/O和内存方面的问题。同时与锁一样，latch也有SH（共享）和EX（排他）这样的性质。
PAGE_IO*_LATCH用于读取或者写入page时，如果读写持续时间过长，则这类等待就会很明显。例如对于从物理文件读取一个page时，就会请求一个EX的latch，直到这个读取完成后才释放，这样就能保证读取的过程中，不会被其他的修改。而PAGE*_LATCH则是对已经存在于内存中的page进行加latch，也是在需要的时候才添加latch。SH类型的latch不会阻塞SH类型的latch，但会阻塞EX类型的latch。
同时，需要注意latch只在user mode下发生，在kernel mode中对资源的竞争管理是由SQLOS来负责的，
在latch上，为了减少热点页的竞争(hot page)，SQL Server还引入了sub-latch的机制。Sub-latch只发生在已经存在内存中的页上。例如，当SQL Server检测到在持续的一段时间内，有很高的SH 行为的latch发生，会将已经持有的latch提升为sub-latch，sub-latch是将一个latch根据逻辑CPU分为多个对应的latch结构队列，这样worker只需要为本地调度器请求一个SH的sub-latch，这样可以避免连锁活动，也使用了更少的资源，提高了处理hot page的能力。当然，这一切是SQL
Server自动发生的，不需要我们进行干预。
Reading Page
当CPU的某一worker thread需要请求一个page时，会调用BufferPool::GetPage模块，GetPage函数会对BUF结构进行扫描，如果发现请求的page，就会对该page添加latch并返回给调用者；如果没有发现，则需要从磁盘读取该page。
读取page时，会有多种行为，如预读（read ahead）机制，但基本步骤如下：
步骤1：向内存管理器(memory manager)发出一个请求分配固定大小的page；
步骤2：该page会与一个跟踪该page的BUF结构关联；
步骤3：在该page上添加EX latch来防止被修改；
步骤4：将BUF结构插入内存中的一个HASH 表。这样所有使用到同样BUF和PAGE的请求会受到EX latch的保护。如果相关对象已经在HASH表中，则不需要这一步，而是直接去HASH表获取相关内容；
步骤5：建立I/O请求，并发送该I/O请求（异步I/O）
步骤6：尝试去获取已请求的latch类型；
步骤7：检查各项错误条件，如果有错误则抛出错误。
如果有错误产生时，会导致其他活动的发生，例如如果checksum校验失败，就会产生re-read（重读）的行为。从上述步骤可以看出，当读取Page完成后，并不会立即释放相关的EX latch，而会等到页校验完成后才释放。
Writing Page
写page时与读page是十分相似的。写page时都是针对page已存在于内存中，并且BUF的状态被标记为dirty（已改变），要看脏页，可以通过sys.dm_os_buffer_descriptors来查看。写入page时，SQL Server是调用WriteMultiple来完成的。写page时，涉及到了三个thread，分别是lazywriter，checkpoint，eager write。
Lazywriter是一个定期扫描buffer pool来检查free list 大小的线程。在SQL Server 2008后，引入了TLA(TIME LAST ACCESS)算法，这个算法是对LRU的改进。Lazywriter根据该算法对标记为脏页的页进行判断，如果已经过时(aged)，则调用WriteMultiple将相关dirty page写入磁盘。
Checkpoint 是用来标识所有已提交的事务所关联的changed page 是否已经被写入磁盘。Checkpoint是recovery的开始点。与lazywriter不同的是，checkpoint并不会将dirty page从缓存中移除，而是将其标记为clean（干净的）。有很多条件会触发checkpoint，在checkpoint发生时，就会调用WriteMultiple完成相关写入。
Eager write 在一些BCP，blob字段的操作中，有些页必须从内存中写入到磁盘才能完成相关的事务，这种写就是eager write，同样是通过调用WriteMultiple来完成的。
在写入请求的页时，不仅仅会请求写入脏页，还会写入邻近的页，减少I/O请求，提高I/O性能。写入页，同样需要latch的支持，一般是请求EX，防止未来可能的页修改。但SQL Server 也能允许在写入页的过程中使用SH latch来读取相关的内容。
PAE and AWE
这个就不用多说，但有两点需要注意：一是PAE和AWE是独立的，开启AWE，不需要PAE；开启PAE，也不需要AWE；二是AWE只扩展buffer pool的大小，对plan cache等不进行扩展。
Read Ahead
如果我们开启set statistics io on时，经常会看到预读多少的内容。SQL Server的预读机制可以大大提高异步I/O能力。
Sparse Files and Copy On Write (COW) Pages
稀疏文件主要用于在线DBCC和快照数据库中。稀疏文件一般情况下实际占用空间远小于文件大小值。在创建snapshot数据库时，会伴随着copy on write的行为，copy on write 指的是当一个页将要被写入内容时，会发生一个检查，确定该页是否被copy到了snapshot数据库，如果没有，则在该页被改变之前会被写入到snapshot数据库上，从而保证snapshot的内容一直为某一时刻的。为了维护snapshot数据，在parent库上会有file control block chaining（FBCs）来管理snapshot与parent的对应关系，这样copy
on write就能快速定位。
Snapshot虽然开始很小，但随着parent库的更改，会慢慢变大，因此在创建snapshot数据库时，需要考虑到这一点。又由于可能需要与数据文件频繁的交互，因此还需要将snapshot放在I/O性能好的设备上。
当在snapshot数据库进行查询时，读取请求会先发生在snapshot数据库上，如果相关的page还没从parent库拷贝过来时，就将该读取请求发送给parent上的FBC，并从parent库上读取相关的page。这样能很大程度的保证了snapshot的稀疏。
DBCC 同样会使用快照来完成相关内容，当然这个快照是内部维护的。这个概念也澄清了一个误区“DBCC CHECKDB会对数据库里的page加锁”。事实上，在2005以后，DBCC就通过维护内部快照从而完成对数据库的一致性检查。当然，这对I/O的要求比较高，并需要有较多的空间，如果不满足条件的话，可以使用WITH TABLE LOCK 直接在数据库文件上进行一致性检查。
Scribbler(s)
Scribbler寓意小孩在图片的线框外乱涂颜色，表示一个组件在内存中的不属于它的区域改变数据。这会造成数据损坏。在SQL Server 2000中，为了防止这种行为的出现，引入了Torn page的校验机制；而在sql server 2005后，还引入checksum机制。
如果page_audit设置为checksum时，lazywriter会检查内存中的页，并重新计算页上的checksum值，如果值不一致，就会纪录错误并将该页直接从内存中消除，这就表明发生了一次“scribbler”。追踪页“scribbler”是比较困难的，但有trace flag –T831 可以开启，从而获取更详细的内容。
页的校验是SQL Server IO上很重要的一个内容，更多内容可以参考SQL Server IO basic 这份白皮书。
可以看到SQL Server 提供了丰富的内部I/O管理机制。理解这些概念，也就能更好的理解SQL Server的工作机制，在碰上一些内部错误或者I/O子系统设置时便能应付自如。更多详情请参阅SQL Server IO basic 白皮书。