IS%i(J0O3Rb~e0 问题:
:H*n5Oq Q)O051Testing软件测试网)`)wP'iFvZ 我们可能经常会遇到SQLServer数据库频繁关闭的情况。在分析了内存和CPU使用情况后,我们需要继续调查根源是否在I/O。我们应该如何识别SQLServer是否有I/O相关的瓶颈?
W"JhX%Z1U03k'J0jr8g$W0 解决:51Testing软件测试网^&T9DmZP
51Testing软件测试网^+V,k:T;[C,d 当数据页经常从缓冲池中移进移出的时候,I/O子系统就会成为SQLServer性能问题的关键因素之一。事务日志和tempdb同样也会产生重大的I/O压力。因此,你必须确保你的I/O子系统能按照预期运行。否则你将会成为响应时间增长和频繁超时的受害者。在这篇文章中,将描述如何使用内置工具识别I/O相关瓶颈,并提供一些磁盘配置的方法:51Testing软件测试网
HfA J-B1WD.p
51Testing软件测试网5U&Y](\-B"po 性能计数器(Performance Monitor):51Testing软件测试网6G5oC7aky)K}:H
51Testing软件测试网@5[
O~DMY4O 可以使用性能计数器来检查I/O子系统的负荷。下面的计数器可用于检查磁盘性能:
IW!Qle)h0!uy`
[+w w+a0L0
PhysicalDisk Object:Avg.DiskQueue
Length:计算从物理磁盘中的平均读和写的请求队列。过高的值代表磁盘操作处于等待状态。当这个值在SQLServer峰值时长期超过2,证明需要注
意了。如果有多个硬盘,就需要把这些数值除以2。比如,有4个硬盘,且队列为10,那么平均值就是10/4=2.5,虽然也证明需要关注,但不能使用10
这个值。51Testing软件测试网+{;u4w&d,{p7G
*E
B)TO7b G@kx0 Avg.Disk Sec/Read和Avg.Disk Sec/Write:显示从磁盘读或者写入磁盘的平均时间。10ms内是很好的表现,20以下还算能接受。高于此值证明存在问题。51Testing软件测试网
F*nc"kFY
51Testing软件测试网k GF/U`f
zu Physical Disk:%Disk Time:在磁盘忙于读或者写请求的时候持续时间的比率。根据拇指定律,此值应该小于50%。51Testing软件测试网%tv8O"N
Fw:zak_
51Testing软件测试网&X&H:A)t(e
y}&sr/y Disk Reads/Sec和Disk Writes/Sec计数器显示出在磁盘中读写操作的速率。这两个值应该小于磁盘能力的85%。当超过此值,磁盘的访问时间将以指数方式增长。
5i&Y
lS3H ^;Q#Z051Testing软件测试网%|ZqlkM$l 可以通过以下方式来计算逐渐增长的负载的能力。一种方法是使用SQLIO。你应该找到吞吐量比较稳定,但缓慢增长。
*LH,mZ4`|051Testing软件测试网2\)}4NJZe 可以使用以下公式来计算RAID配置:
{%B.f`
^B`rZl051Testing软件测试网Kc [
Y0|L^#D Raid 0: I/O per disk = (reads + writes) / number ofdisks
@@l8W
SE;\*Sm0 Raid 1: I/O per disk = [reads + (writes*2)] / 2
C*s1Qg1O:B9U
G0 Raid 5: I/O per disk = [reads + (writes*4)] / number of disks
l"ur4bG8g/e0 Raid 10: I/O per disk = [reads + (writes*2)] / number of disks
@O }1v hI@051Testing软件测试网8VH\7\4M 比如:对于RAID 1,如果得到下面的计数器:
loTz1C
tS\051Testing软件测试网sO9OG_-IE4MG*{ Disk Reads/sec = 90
-N,X1H
|S0;NdI"{)a(x}:y
r0 Disk Writes/sec =75
!R0Uqx#Np?SR051Testing软件测试网]{q"a ]
~!q 根据公式:[reads + (writes*2)] / 2 or [90 + (75*2)] / 2 = 120I/Os每个磁盘。51Testing软件测试网{
k)U$o%{
Ae-^I.f#iUv0p"v"mgf7G0 动态管理视图(DMVs):51Testing软件测试网
Iw7Nh]/C}?f
&t0MI5M b\BG2~n0 有很多游泳的DMVs可以用于检查I/O瓶颈:51Testing软件测试网Q'F]v3x|
51Testing软件测试网-VK8LON-u1f 当一个页面被用于读或者写访问且页面在缓冲池中不存在或不可用时,会引发一个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软件测试网UBmez:tm)I
51Testing软件测试网,l%t"VK3@ZH51Testing软件测试网7cIs
Gg
`7?T+e
51Testing软件测试网3p$K@y? select * jO;R$}/E,]051Testing软件测试网/C1g&`#D|"?W\!{fromsys.dm_os_wait_stats
?]l WJ2B6e_2x)s0G]5w].V$i]wx8{0where wait_type like'PAGEIOLATCH%' L}Y5Is.lF051Testing软件测试网-S'Q~i.W
l9Xorder by wait_typeasc51Testing软件测试网IM-LToH6E |
51Testing软件测试网/WM*}7w
T 挂起的I/O请求可以在下面查询中查到,并且用于识别那个磁盘负责的这个瓶颈:51Testing软件测试网/E*Q _Z xS
51Testing软件测试网)y C{$|#r9o9a$O&`ok
vI51Testing软件测试网+m
MmIGS9q
select database_id,51Testing软件测试网*d;`CI5mB file_id,51Testing软件测试网QZ:`/Y R1iI ust&[@ io_stall,51Testing软件测试网n3l
g.EO S+r io_pending_ms_ticks, )hLt
kp0 scheduler_address #u)t*j
j-Cm
Mb Y0from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,51Testing软件测试网\
M9c$sm Y)C sys.dm_io_pending_io_requests as iopior51Testing软件测试网(kdRJ#b I where iovfs.file_handle = iopior.io_handle |
51Testing软件测试网I8B nt8z i5TR 磁盘碎片(Disk Fragmentation):
B;i)`?OH!v a051Testing软件测试网+z
Y#|,UPR"mP 建议你检查磁盘碎片和配置用于SQLServer实例的磁盘。在NTFS文件系统中的碎片会产生严重的性能影响。磁盘需要经常整理碎片并且指定整理碎片计划。研究表明,一些情况下SAN在整理碎片后性能更差。因此,SAN必须根据实际情况对待。51Testing软件测试网(qL6P:J!mn$a
51Testing软件测试网eE\0oQ$D
[*mg)J+` NTFS上的索引碎片同样能引起高I/O好用。但是这和在SANs中的效果是不一样的。51Testing软件测试网*]{ P8R-z
:@7q*~9@/@Ug*y0 磁盘配置/最佳实践:51Testing软件测试网5ig(_E5sQ2v
L.\}/Cl8y0 常规情况,你应该把日志文件和数据文件分开存放以获得更好的性能。对于重负载的数据文件(包括tempdb)的I/O特性是随机读取。对于日志文件,是顺序访问的,除非事务需要回滚。51Testing软件测试网'| {v,?Mz o`
51Testing软件测试网;J'o@0U#e5w4F 对于内置磁盘仅仅可以用于数据库日志文件,因为它们对顺序I/O有很好的性能,但是对随机I/O性能低下。
`QuY y2}X0B#cj%A;?V0 数据库的数据和日志文件应该放在对应专用的磁盘中。确保良好的性能。建议日志文件放在两个内置磁盘,并配置为RAID 1。数据文件驻留在仅用于给SQLServer访问的SAN系统中,并只被查询和报表控制。特殊访问应该被禁止。51Testing软件测试网T,b~8Wj
KWk;o/Pf4Qg4U0 写缓冲在可能的情况下应该被允许,并保证断电也能使用。
,N3c5]5|e/o00g}c}%~eyS:NRv0 为了尽可能保证对于OLTP系统的I/O瓶颈影响最小化,不应该把OLAP和OLTP环境混合。并且保证你的代码优化及有合适的索引来避免不必要的I/O。
ZRVFL|0