51Testing软件测试网dX"[ |4^]0l 问题:
;W;b!I6A-mU+|@H*N0R3sV`/}0fc6S
f0 我们可能经常会遇到SQLServer数据库频繁关闭的情况。在分析了内存和CPU使用情况后,我们需要继续调查根源是否在I/O。我们应该如何识别SQLServer是否有I/O相关的瓶颈?51Testing软件测试网2D,v.gf3W Q/F
SZ
51Testing软件测试网SoNdU@3M 解决:51Testing软件测试网v
cH0g/w_8a
51Testing软件测试网:Gt8U+U(II 当数据页经常从缓冲池中移进移出的时候,I/O子系统就会成为SQLServer性能问题的关键因素之一。事务日志和tempdb同样也会产生重大的I/O压力。因此,你必须确保你的I/O子系统能按照预期运行。否则你将会成为响应时间增长和频繁超时的受害者。在这篇文章中,将描述如何使用内置工具识别I/O相关瓶颈,并提供一些磁盘配置的方法:51Testing软件测试网:~.fC7j\6of
51Testing软件测试网G
f'N_TXCg}@ 性能计数器(Performance Monitor):51Testing软件测试网^D([w$n
s_
51Testing软件测试网nM@x-^[9Y3]k1l 可以使用性能计数器来检查I/O子系统的负荷。下面的计数器可用于检查磁盘性能:51Testing软件测试网m`DDJ]/|2K8m
!jIt/@#sY R0
PhysicalDisk Object:Avg.DiskQueue
Length:计算从物理磁盘中的平均读和写的请求队列。过高的值代表磁盘操作处于等待状态。当这个值在SQLServer峰值时长期超过2,证明需要注
意了。如果有多个硬盘,就需要把这些数值除以2。比如,有4个硬盘,且队列为10,那么平均值就是10/4=2.5,虽然也证明需要关注,但不能使用10
这个值。
o0?.j/BD8\0k-].jO$W
dQh0 Avg.Disk Sec/Read和Avg.Disk Sec/Write:显示从磁盘读或者写入磁盘的平均时间。10ms内是很好的表现,20以下还算能接受。高于此值证明存在问题。51Testing软件测试网'?F9j`%ec"G%Uu
51Testing软件测试网 wo4\vU Physical Disk:%Disk Time:在磁盘忙于读或者写请求的时候持续时间的比率。根据拇指定律,此值应该小于50%。
D;UEESK,O051Testing软件测试网
d3n/c-xY`)w#s Disk Reads/Sec和Disk Writes/Sec计数器显示出在磁盘中读写操作的速率。这两个值应该小于磁盘能力的85%。当超过此值,磁盘的访问时间将以指数方式增长。51Testing软件测试网$g`p"TRiI,l
;M'fqT2Ac,E%|L0 可以通过以下方式来计算逐渐增长的负载的能力。一种方法是使用SQLIO。你应该找到吞吐量比较稳定,但缓慢增长。51Testing软件测试网w/mz:al
51Testing软件测试网W/{gf5@ F 可以使用以下公式来计算RAID配置:
^ fgl'kNF ZD0_'iMP$^!Nt@0 Raid 0: I/O per disk = (reads + writes) / number ofdisks51Testing软件测试网S Vo$p$Dwz6kNo
Dt
Raid 1: I/O per disk = [reads + (writes*2)] / 2
6L ?L+?dmt'}-e1{0 Raid 5: I/O per disk = [reads + (writes*4)] / number of disks
C$lTL*|0 Raid 10: I/O per disk = [reads + (writes*2)] / number of disks51Testing软件测试网?:q&li4jM|[,Q
Kt%rG
[.O#o'S0 比如:对于RAID 1,如果得到下面的计数器:
ZQ'nh/H mm"SU0*a|)z(x
g0 Disk Reads/sec = 9051Testing软件测试网7|J&rDMGZx
51Testing软件测试网K
a8b:q$dzQ Disk Writes/sec =75
xv7r#gc'C'N2f0%a&E+S:La/{0 根据公式:[reads + (writes*2)] / 2 or [90 + (75*2)] / 2 = 120I/Os每个磁盘。
zuf:U@0
{$T!F#~
X+S;AY)k00JZ)CS-v-xC}#j0 动态管理视图(DMVs):51Testing软件测试网E FV}\,}X
51Testing软件测试网#\
q'\&rx8w/r 有很多游泳的DMVs可以用于检查I/O瓶颈:
JG)yUSh8C
?0o!O"Xl)A0 当一个页面被用于读或者写访问且页面在缓冲池中不存在或不可用时,会引发一个I/O闩锁等待(I/O
latch),它会在PAGEIOLATCH_EX/PAGEIOLATCH_SH(具体根据请求类型而定)。这些等待表明一个I/O瓶颈。可以使用
sys.dm_os_wait_stats找到闩锁等待的信息。如果你保存了SQLServer正常运行下的waiting_task_counts和
wait_time_ms值,并且于此次的值做对比,可以识别出I/O问题:51Testing软件测试网1d-}V8dm}ek]
;?qh.H$`8Zm;G/`u[5{0
%}F7Z3g?&u051Testing软件测试网+z6f.uT(n[ select *51Testing软件测试网.lPY M
g4`5nC 51Testing软件测试网$d`eo!s,M XjYfromsys.dm_os_wait_stats 4yo
E-s1ao051Testing软件测试网Sd5Zk!n#Q
~uwhere wait_type like'PAGEIOLATCH%'51Testing软件测试网~)e x(Er2k?t+Bv 51Testing软件测试网q0z%vKDoA)E7]3z&Korder by wait_typeasc51Testing软件测试网D*n k,~ `$Y)J |
51Testing软件测试网 }yN!h'MsO5z
^ 挂起的I/O请求可以在下面查询中查到,并且用于识别那个磁盘负责的这个瓶颈:
6B!pO(J;s/{%Q051Testing软件测试网6t3X!xY'~%r(uk51Testing软件测试网@Us6f+hnEX
select database_id, F%V+OI}0 file_id, 7@)`&Gv8dw6R2C0 io_stall, W'y!]6Ws,h
`0 io_pending_ms_ticks,51Testing软件测试网A2\]i3o scheduler_address51Testing软件测试网;P.U@t^_X,o)@ from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,51Testing软件测试网 m1g#S|'I6L)e9QK sys.dm_io_pending_io_requests as iopior h(o&B7E2yx/?;_0where iovfs.file_handle = iopior.io_handle |
51Testing软件测试网ej5V1~3W1Ge 磁盘碎片(Disk Fragmentation):51Testing软件测试网@/Y q,OP'Y-Iw'Dn-S%p
1qD3ve6`._/V3lu0 建议你检查磁盘碎片和配置用于SQLServer实例的磁盘。在NTFS文件系统中的碎片会产生严重的性能影响。磁盘需要经常整理碎片并且指定整理碎片计划。研究表明,一些情况下SAN在整理碎片后性能更差。因此,SAN必须根据实际情况对待。
$?vRT^o051Testing软件测试网0X R-R m(HOki NTFS上的索引碎片同样能引起高I/O好用。但是这和在SANs中的效果是不一样的。
g
{$n7|AZ
n0&VS W'Cw%Uj0 磁盘配置/最佳实践:51Testing软件测试网Rq8F{0H@;u
`;dXJ0`1HJI+Z"s0 常规情况,你应该把日志文件和数据文件分开存放以获得更好的性能。对于重负载的数据文件(包括tempdb)的I/O特性是随机读取。对于日志文件,是顺序访问的,除非事务需要回滚。51Testing软件测试网&E'K{ ?R(Z%Z
51Testing软件测试网%E%\6C$k)D.c5O[3z 对于内置磁盘仅仅可以用于数据库日志文件,因为它们对顺序I/O有很好的性能,但是对随机I/O性能低下。51Testing软件测试网3Y;o)\.^9oz2TC
51Testing软件测试网U2l"_mLD
v#@ 数据库的数据和日志文件应该放在对应专用的磁盘中。确保良好的性能。建议日志文件放在两个内置磁盘,并配置为RAID 1。数据文件驻留在仅用于给SQLServer访问的SAN系统中,并只被查询和报表控制。特殊访问应该被禁止。51Testing软件测试网,b$C#Rx!Lw
-]?g5B&C%S[0 写缓冲在可能的情况下应该被允许,并保证断电也能使用。
K._!co(Y3Q051Testing软件测试网gMh#L#m'_k 为了尽可能保证对于OLTP系统的I/O瓶颈影响最小化,不应该把OLAP和OLTP环境混合。并且保证你的代码优化及有合适的索引来避免不必要的I/O。
"v:dT?;L0