首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

SQL Server IO 子系统浅究 II,该怎么解决

2012-03-01 
SQL Server IO 子系统浅究 II性能监视器里的IO子系统计数器在运行里面输入”perfmon”,使用性能监视器,可以

SQL Server IO 子系统浅究 II
性能监视器里的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 code
sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10


接着就可以使用SQLIO来进行测试了。一般的测试语句形式如下:
SQL code
sqlio -kW -t2 -s120 -dM -o1 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o2 -frandom -b64 -BH -LS Testfile.datsqlio -kW -t2 -s120 -dM -o4 -frandom -b64 -BH -LS Testfile.datsqlio -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




[解决办法]
前排学习~
[解决办法]

探讨
前排学习~

[解决办法]
前排学习~
[解决办法]
好贴,收藏
[解决办法]
hao a jej
[解决办法]
引用:
引用:
前排学习~

紧挨着你

紧挨着你

[解决办法]
看看先

[解决办法]
非常感谢!!!!
[解决办法]
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的安装目录下,运行如下命令来产生测试文件。

热点排行