- 浏览: 210488 次
- 性别:
- 来自: 江西
文章分类
- 全部博客 (109)
- C# (29)
- ASP.NET (30)
- MSSQL (29)
- javascript (9)
- cmd (3)
- ORACLE (4)
- log4j (1)
- window (5)
- FTP (1)
- Shell (1)
- java (1)
- IIS (7)
- html (11)
- SQL (3)
- Jquery (11)
- IE (3)
- 火狐、谷歌 (2)
- wince (2)
- JSON (5)
- List (1)
- Foreach (1)
- SQLhelper (1)
- Ajax (1)
- Firebird (1)
- mysql (7)
- js (2)
- jQuery.autocomplete (1)
- dataTable (1)
- Http (1)
- get (1)
- datetime (1)
- powshell (1)
- URL Protocol (1)
- Office (1)
- Excel (1)
- ASP.NET,算法,秘钥 (1)
- 浏览器 (1)
- Tomcat (1)
最新评论
转载地址:http://blog.csdn.net/leamonjxl/article/details/6790832
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 白皮书。
-----------------------------------------------------------------------------------------------
性能监视器里的IO子系统计数器
在运行里面输入”perfmon”,使用性能监视器,可以观察当前的I/O性能情况,确定是否存在I/O方面的问题。
在SQL Server上,涉及I/O的计数器有:
Average read bytes/sec,平均每秒的读取字节数
Average write bytes/sec,平均每秒的写入字节数
Reads/sec, 每秒多少读
writes/sec,每秒多少写
Disk read bytes/sec, 每秒读取的传输速率
Disk write bytes/sec,每秒写入的传输速率
Average disk sec/read, 平均每个读花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题;
Average disk sec/write,平均每个写花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题。
Average disk queue length,在磁盘等待的IO个数,最好在1.5和2倍转轴个数(spindles)以内,如果高于这个值,一般说明IO子系统有一定的问题,但现在普遍使用RAID或者磁盘虚拟化,如果不详细了解具体的I/O子系统配置,很难从这个计数器判断出问题。
除了使用perfmon外,还可以使用SQLDiag.exe或Perfstats脚本来获取整体系统的性能情况,然后通过PAL分析采集的perfmon文件,具体详情就google下吧。
SQLIO
前面也提到过,使用I/O子系统时,最好能够进行充分测试。SQLIO就是这样一款模拟SQL Server读写特点的性能测试工具,使用SQLIO可以快速定位I/O子系统的瓶颈所在,对如何分布SQL Server文件也会有更好的帮助。下面简要介绍下SQLIO的使用。
SQLIO的下载地址为http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en 。安装完成后,有个param.txt的配置文件,里面的内容为:c:\testfile.dat 2 0x0 100 ,这边我们只需要关注第一个参数和最后一个参数。
第一个参数表示测试文件的所在目录,根据实际要测的硬盘来改变盘符;
最后一个参数表示测试文件的大小,以MB为单位,一般尽量与实际的数据库大小一致。配置完成后,启用cmd命令行工具,到SQLIO的安装目录下,运行如下命令来产生测试文件。
SQL codesqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10
接着就可以使用SQLIO来进行测试了。一般的测试语句形式如下:
SQL codesqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
其中
参数 –kW 表示模拟写的情况,如果是-kR则表示写;
参数 –t2 表示两个线程
参数 –s120表示测试2分钟
参数 –dM 表示具体的盘符
参数 –o1表示outstanding IO的数目
参数 –frandom表示随机模式,相反的是sequential 序列模式
参数 –b64 表示每个IO大小为64K
可以将这样的命令保存为一个批处理文件,例如SQLIOBatch.bat,然后在cmd下,使用SQLIOBatch.bat >SQLIOResult.txt 将测试结果保存为txt文件,然后再去分析。SQLIO运行后的结果如下:
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
16 threads writing for 60 secs to file d:\MSSQL\testfile1.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 2048 MB for file: d:\MSSQL\testfile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18527.91
MBs/sec: 144.74
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 4900
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 78 6 8 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
其中比较关键的指标就是IOs/sec 和MBs/sec了。
这边顺便介绍一个分析SQLIO结果的Powershell脚本,具体参看“http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx”,很强大的。
关于SQLIO 的使用,可以参考Brent Ozar在SQLServerPedia上的文章,里面也有详细的SQLIO测试脚本。http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
关于IO的一些最佳实践
尽管IO子系统比较复杂,但还是有一些经过总结的良好实践,值得借鉴。
A, 使用多个数据文件
使用多个数据文件可以提高性能,但这也是需要看情况而定的。Paul Randal 在他的blog上曾经贴出了一篇使用多个数据文件的测试文章。测试的结果就是随着数据文件的增加,性能会先提高,到一个值后,会继续下降。如下图
可以看到,在8个数据文件的情况下,其性能会有很大的提升。
具体详情,请参考http://sqlskills.com/BLOGS/PAUL/category/IO-Subsystems.aspx
B, 设置合理的磁盘分区偏移(disk partition alignment)
这个问题最早是由Jimmy May(http://blogs.msdn.com/b/jimmymay/)提出的。在前面讲扇区的时候,我们也提到在windows 2003以前,默认会将磁盘前面的63个扇区设为隐藏扇区,用来存放MBR的,这样一来,就使得文件分配单元的大小是在31.5K之后开始的,从而将本来一个I/O的操作,变成了2个I/O。通过设置该参数,可以有效提高性能20%~30%,自己也亲自测过这个调整的参数值,也确实有这样的效果。
要查看自己当前的disk partition alignment值,可以使用
wmic partition get BlockSize, StartingOffset, Name, Index
获取的结果类似如下:
BlockSize Index Name StartingOffse
512 0 磁盘 #0,分区 #0 32256
512 1 磁盘 #0,分区 #1 48322068480
32256/124=31.5,就是一个典型的未设置合理值的例子。在windows 2008以后,在初始化时,已经对这个值进行修改了,因此不会存在这样的问题。但2003 和XP 还是需要进行手工设置,设置的时候通过diskpart来进行,但需要注意的是,设置这个值后,需要对磁盘进行重新格式化才能使用!!
C:\>diskpart
Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: ASPIRINGGEEK
DISKPART> list disk
Disk ### Status Size Free Dyn GPT
-------- ---------- ------- ------- --- ---
Disk 0 Online 186 GB 0 B
Disk 1 Online 100 GB 0 B
Disk 2 Online 120 GB 0 B
Disk 3 Online 150 GB 150 GB
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait
更多详情,可以参考白皮书《Disk Partition Alignment Best Practices for SQL Server》http://msdn.microsoft.com/en-us/library/dd758814.aspx
C, 其他最佳实践
有很多这样的文章,如
《Storage Top 10 Best Practices》http://msdn.microsoft.com/en-us/library/cc966534.aspx
《Physical Database Storage Design》http://technet.microsoft.com/en-us/library/cc966414.aspx
里面会有一些关于I/O方面的设置指南,值得参考。
小结
通过对IO子系统的学习研究,发现其涉及的内容实在广泛,这边只是介绍了一些自己觉得比较重要的东西,而且可能有些方面还值得推敲。欢迎大家一起探讨。
参考资料
SQL Server I/O Basics Chapter 1
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
SQL Server I/O Basics Chapter 2
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Storage Performance for SQL Server
http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx
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 白皮书。
-----------------------------------------------------------------------------------------------
性能监视器里的IO子系统计数器
在运行里面输入”perfmon”,使用性能监视器,可以观察当前的I/O性能情况,确定是否存在I/O方面的问题。
在SQL Server上,涉及I/O的计数器有:
Average read bytes/sec,平均每秒的读取字节数
Average write bytes/sec,平均每秒的写入字节数
Reads/sec, 每秒多少读
writes/sec,每秒多少写
Disk read bytes/sec, 每秒读取的传输速率
Disk write bytes/sec,每秒写入的传输速率
Average disk sec/read, 平均每个读花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题;
Average disk sec/write,平均每个写花的时间,小于10ms表示性能很好,在10ms~20ms之间表示性能可以接受,如果大于20ms,说明存在I/O问题。
Average disk queue length,在磁盘等待的IO个数,最好在1.5和2倍转轴个数(spindles)以内,如果高于这个值,一般说明IO子系统有一定的问题,但现在普遍使用RAID或者磁盘虚拟化,如果不详细了解具体的I/O子系统配置,很难从这个计数器判断出问题。
除了使用perfmon外,还可以使用SQLDiag.exe或Perfstats脚本来获取整体系统的性能情况,然后通过PAL分析采集的perfmon文件,具体详情就google下吧。
SQLIO
前面也提到过,使用I/O子系统时,最好能够进行充分测试。SQLIO就是这样一款模拟SQL Server读写特点的性能测试工具,使用SQLIO可以快速定位I/O子系统的瓶颈所在,对如何分布SQL Server文件也会有更好的帮助。下面简要介绍下SQLIO的使用。
SQLIO的下载地址为http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en 。安装完成后,有个param.txt的配置文件,里面的内容为:c:\testfile.dat 2 0x0 100 ,这边我们只需要关注第一个参数和最后一个参数。
第一个参数表示测试文件的所在目录,根据实际要测的硬盘来改变盘符;
最后一个参数表示测试文件的大小,以MB为单位,一般尽量与实际的数据库大小一致。配置完成后,启用cmd命令行工具,到SQLIO的安装目录下,运行如下命令来产生测试文件。
SQL codesqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10
接着就可以使用SQLIO来进行测试了。一般的测试语句形式如下:
SQL codesqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.dat
sqlio -kW -t2 -s120 -dM -o8 -frandom -b64 -BH -LS Testfile.dat
其中
参数 –kW 表示模拟写的情况,如果是-kR则表示写;
参数 –t2 表示两个线程
参数 –s120表示测试2分钟
参数 –dM 表示具体的盘符
参数 –o1表示outstanding IO的数目
参数 –frandom表示随机模式,相反的是sequential 序列模式
参数 –b64 表示每个IO大小为64K
可以将这样的命令保存为一个批处理文件,例如SQLIOBatch.bat,然后在cmd下,使用SQLIOBatch.bat >SQLIOResult.txt 将测试结果保存为txt文件,然后再去分析。SQLIO运行后的结果如下:
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
16 threads writing for 60 secs to file d:\MSSQL\testfile1.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 2048 MB for file: d:\MSSQL\testfile1.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 18527.91
MBs/sec: 144.74
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 6
Max_Latency(ms): 4900
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 78 6 8 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
其中比较关键的指标就是IOs/sec 和MBs/sec了。
这边顺便介绍一个分析SQLIO结果的Powershell脚本,具体参看“http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx”,很强大的。
关于SQLIO 的使用,可以参考Brent Ozar在SQLServerPedia上的文章,里面也有详细的SQLIO测试脚本。http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO
关于IO的一些最佳实践
尽管IO子系统比较复杂,但还是有一些经过总结的良好实践,值得借鉴。
A, 使用多个数据文件
使用多个数据文件可以提高性能,但这也是需要看情况而定的。Paul Randal 在他的blog上曾经贴出了一篇使用多个数据文件的测试文章。测试的结果就是随着数据文件的增加,性能会先提高,到一个值后,会继续下降。如下图
可以看到,在8个数据文件的情况下,其性能会有很大的提升。
具体详情,请参考http://sqlskills.com/BLOGS/PAUL/category/IO-Subsystems.aspx
B, 设置合理的磁盘分区偏移(disk partition alignment)
这个问题最早是由Jimmy May(http://blogs.msdn.com/b/jimmymay/)提出的。在前面讲扇区的时候,我们也提到在windows 2003以前,默认会将磁盘前面的63个扇区设为隐藏扇区,用来存放MBR的,这样一来,就使得文件分配单元的大小是在31.5K之后开始的,从而将本来一个I/O的操作,变成了2个I/O。通过设置该参数,可以有效提高性能20%~30%,自己也亲自测过这个调整的参数值,也确实有这样的效果。
要查看自己当前的disk partition alignment值,可以使用
wmic partition get BlockSize, StartingOffset, Name, Index
获取的结果类似如下:
BlockSize Index Name StartingOffse
512 0 磁盘 #0,分区 #0 32256
512 1 磁盘 #0,分区 #1 48322068480
32256/124=31.5,就是一个典型的未设置合理值的例子。在windows 2008以后,在初始化时,已经对这个值进行修改了,因此不会存在这样的问题。但2003 和XP 还是需要进行手工设置,设置的时候通过diskpart来进行,但需要注意的是,设置这个值后,需要对磁盘进行重新格式化才能使用!!
C:\>diskpart
Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: ASPIRINGGEEK
DISKPART> list disk
Disk ### Status Size Free Dyn GPT
-------- ---------- ------- ------- --- ---
Disk 0 Online 186 GB 0 B
Disk 1 Online 100 GB 0 B
Disk 2 Online 120 GB 0 B
Disk 3 Online 150 GB 150 GB
DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label="MyFastDisk" nowait
更多详情,可以参考白皮书《Disk Partition Alignment Best Practices for SQL Server》http://msdn.microsoft.com/en-us/library/dd758814.aspx
C, 其他最佳实践
有很多这样的文章,如
《Storage Top 10 Best Practices》http://msdn.microsoft.com/en-us/library/cc966534.aspx
《Physical Database Storage Design》http://technet.microsoft.com/en-us/library/cc966414.aspx
里面会有一些关于I/O方面的设置指南,值得参考。
小结
通过对IO子系统的学习研究,发现其涉及的内容实在广泛,这边只是介绍了一些自己觉得比较重要的东西,而且可能有些方面还值得推敲。欢迎大家一起探讨。
参考资料
SQL Server I/O Basics Chapter 1
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
SQL Server I/O Basics Chapter 2
http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx
Storage Performance for SQL Server
http://sqlblog.com/blogs/joe_chang/archive/2008/03/04/storage-performance-for-sql-server.aspx
发表评论
-
用一条insert语句来插入多行数据
2016-05-23 10:35 1711oracle、sqlserver不支持inse ... -
关于distinct 和group by的去重逻辑浅析
2016-05-20 11:06 948在数据库操作中,我们常常遇到需要将数据去重计数的工作。例如: ... -
mysql和mssql关键字key的转义
2016-05-20 10:30 766mysql反引号``,它在键盘的~这个键上,SqlServer ... -
SQL几种分页语句性能对比
2014-03-07 10:35 459SET STATISTICS IO ON select * ... -
MSQL2005以上对象名sysproperties 无效的错误
2014-03-03 11:59 1379在MSSQL2008R2里面执行的时候报错:对象名syspro ... -
IN和EXISTS性能上的区别
2014-02-24 16:46 785在外表大内部小的情况下,EXISTS性能比IN快,如果两个表的 ... -
SQL Server 2000 ——系统表和系统视图
2014-02-17 10:26 1151一、一般存储过程 1、 ... -
新增链接服务器
2014-02-17 10:19 733新增链接服务器 if exists (select * fro ... -
在MSSQL2000里面 对象名 'sys.servers' 无效 服务器: 消息 208,级别 16,状态 1,行 1
2014-02-17 10:12 3172在MSSQL2000里面不支持该 sys.servers查看 ... -
SQL SERVER2008数据库中创建和删除链接服务器
2014-02-11 13:43 4988--在SQL SERVER 2008里,可以按以下的方式建立链 ... -
SQL Server获取某月某季某年最后一天的SQL语
2013-11-25 20:26 1326项目中用到的,获取已知年份的第一天和最后一天,觉得网来的不错, ... -
sql中两个日期相减
2013-08-21 13:36 8788sql中两个日期相减 1、相差天数 select trun ... -
SET STATISTICS IO ON和获取表中的所有行
2013-08-14 11:58 662SET STATISTICS IO ON --通过来查看扫描 ... -
SQL CAST和CONVERT区别
2013-08-09 09:06 655一种数据类型的表达式转换为另一种数据类型的表达式。 CAS ... -
图表处理程序配置 [c:\TempImageFiles\] 中的临时目录无效
2013-07-24 11:15 3664图表处理程序配置 [c:\TempImageFiles\] 中 ... -
MS-SQL2005以上的版本解决Syscolumn表中数据不能修改
2013-07-06 11:41 558例如: 在MSSQL2000里面可以获取到相应的数据 获取Tr ... -
asp.net 在webcofig中连接数据库方式
2013-07-02 14:38 7211:window方式验证 <connectionStri ... -
解决用户 'IIS APPPOOL\Classic .NET AppPool' 登录失败 windows 7
2013-07-02 14:31 1089进入iis管理器 本地应用程序池 选中classic. net ... -
分页效果
2013-05-20 19:19 863转载http://jianfulove.iteye.com/b ... -
MS-SQL将已建的表的某个字段修改为默认的值
2013-05-15 16:48 797ALTER TABLE STOCK_INFO ADD CON ...
相关推荐
对于DBA来说,安装SQLServer之前先要了解磁盘的性能,这个很重要。微软提供了SQLIO可以帮助我们在系统安装之前评估磁盘的性能。
本文详细介绍了SQLSERVER2008内存及IO性能监控方式
Ooracle 数据库性能监控常用sql 监控当前会话、执行的sql及io等信息
SQLIO磁盘压力分析工具可以再部署安装数据库前,做过磁盘压力测试。了解磁盘性能
sqlserver 性能分析工具大全,包括uml,MPSreport,SQL Server 2005 Best Practices Analyzer ,SQL ...PSSDiag,SQLIO Disk Subsystem Benchmark Tool,Best Practices Analyzer Tool for Microsoft SQL Server 2000 1.0
有园友询问我什么时候开始写SQL Server性能系列,估计还得等一段时间,最近工作也比较忙,但是会陆陆续续的更新SQL Server性能系列,本篇作为性能系列的基本引导,让大家尝尝鲜。在涉及到SQL Server性能优化时,我...
IO/CPU/网络等性能趋势图 函数/存储过程等的版本控制,这在商业软件中也没有(如果你知道,告诉我) 对象浏览器:服务器、数据库、表、视图、函数、存储过程等 数据库管理:收缩、日志清除、备份、恢复等 在...
Fusion-io助力SQL Server 2014释放强大性能.pdf
采集服务器占用CPU最大、占用IO最大、Read最大、Write最大等SQL语句,分析数据库性能。
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行...
性能指标参数是指用于衡量 SQLServer 数据库性能的参数,包括 CPU 性能参数、Memory 性能参数、IO 性能参数、网络性能参数等。这些参数可以帮助 DBA 了解数据库的性能情况,监控数据库的运行状态,诊断性能问题。 ...
AWS-SQLIO-基准-Powershell Powershell脚本可在AWS EBS Volumes上自动执行SQLIO基准测试。 概述在AWS上运行SQL Server(在RDS上或在EC2上托管自己SQL Server)时,应仔细考虑您的存储选项。 提供最佳性能和高可用性...
主要给大家介绍了关于sql server性能调优 I/O开销的相关资料,文中通过示例代码以及图片介绍的非常详细,对大家的理解和学习具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
要想让 SQL Server 系统的性能和运行达到最佳,关键在于适当配置 IO 子系统。以下是 SQL Server 小组推荐的一些最常用、最好的 SQL Server 存储配置方法。
硬件原因,数据库的配置不合理,数据库的数据文件和日志文件没有分磁盘存放,会极大影响IO性能 没有建立索引,或者SQL没有走索引。在千万级数据的表上建索引是很有必要的。 SQL过于复杂,过长的SQL语句满足...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
书名: SQLServer2008查询性能优化 作者: 弗里奇(Grant Fritchey) 出版社: 人民邮电出版社 出版日期: 2010年8月1日 ISBN: 9787115230294 编辑推荐 《SQL Server 2008查询性能优化》为你提供了处理查询性能所...
主要介绍了SQL Server中的Forwarded Record计数器影响IO性能的解决方法,需要的朋友可以参考下
从cpu占用,内存占用,io占用等方面分析优化方法