作者: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 白皮书。
很喜欢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 白皮书。