????????

????????????????????SQLServer????????????????????????????CPU??????????????????????????????I/O???????????????SQLServer?????I/O?????????

?????????

??????????????????????????????????I/O????????SQLServer????????????????????????????tempdb?????????????I/O????????????????????I/O???????????????С???????????????????????????????????????????????У????????????????ù??????I/O?????????????Щ??????????????

???????????????Performance Monitor????

?????????????????????????I/O????????ɡ???????????????????????????

????PhysicalDisk Object:Avg.DiskQueue Length???????????????е????????д????????С???????????????????????????????????SQLServer???????????2??????????????????ж?????????????Щ???????2?????磬??4????????????10???????????10/4=2.5??????????????????????????10??????

????Avg.Disk Sec/Read??Avg.Disk Sec/Write???????????????д????????????10ms???????????20??????????????????????????????

????Physical Disk:%Disk Time???????????????д????????????????????????????????????С??50%??

????Disk Reads/Sec??Disk Writes/Sec?????????????????ж?д?????????????????????С???????????85%???????????????????????佫??????????????

??????????????·?????????????????????????????????????SQLIO????????????????????????????????????

??????????????1????????RAID?????

????Raid 0: I/O per disk = (reads + writes) / number ofdisks
????Raid 1: I/O per disk = [reads + (writes*2)] / 2
????Raid 5: I/O per disk = [reads + (writes*4)] / number of disks
????Raid 10: I/O per disk = [reads + (writes*2)] / number of disks

???????磺????RAID 1????????????????????

????Disk Reads/sec = 90

????Disk Writes/sec =75

????????????[reads + (writes*2)] / 2 or [90 + (75*2)] / 2 = 120I/Os????????

????????????????DMVs????

?????к???????DMVs??????????I/O?????

???????????汻?????????д????????????????в??????????????????????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????


select *

fromsys.dm_os_wait_stats 

where wait_type like'PAGEIOLATCH%'

order by wait_typeasc