原文出自：

http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/

問(wèn)題：

我們可能經(jīng)常會(huì)遇到SQLServer數(shù)據(jù)庫(kù)頻繁關(guān)閉的情況。在分析了內(nèi)存和CPU使用情況后，我們需要繼續(xù)調(diào)查根源是否在I/O。我們應(yīng)該如何識(shí)別SQLServer是否有I/O相關(guān)的瓶頸？

?

解決：

當(dāng)數(shù)據(jù)頁(yè)經(jīng)常從緩沖池中移進(jìn)移出的時(shí)候，I/O子系統(tǒng)就會(huì)成為SQLServer性能問(wèn)題的關(guān)鍵因素之一。事務(wù)日志和tempdb同樣也會(huì)產(chǎn)生重大的I/O壓力。因此，你必須確保你的I/O子系統(tǒng)能按照預(yù)期運(yùn)行。否則你將會(huì)成為響應(yīng)時(shí)間增長(zhǎng)和頻繁超時(shí)的受害者。在這篇文章中，將描述如何使用內(nèi)置工具識(shí)別I/O相關(guān)瓶頸，并提供一些磁盤配置的方法：

?

性能計(jì)數(shù)器（Performance Monitor）：

可以使用性能計(jì)數(shù)器來(lái)檢查I/O子系統(tǒng)的負(fù)荷。下面的計(jì)數(shù)器可用于檢查磁盤性能：

PhysicalDisk Object:Avg.DiskQueue Length： 計(jì)算從物理磁盤中的平均讀和寫的請(qǐng)求隊(duì)列。過(guò)高的值代表磁盤操作處于等待狀態(tài)。當(dāng)這個(gè)值在SQLServer峰值時(shí)長(zhǎng)期超過(guò)2，證明需要注意了。如果有多個(gè)硬盤，就需要把這些數(shù)值除以2。比如，有4個(gè)硬盤，且隊(duì)列為10，那么平均值就是10/4=2.5，雖然也證明需要關(guān)注，但不能使用10這個(gè)值。

Avg.Disk Sec/Read和Avg.Disk Sec/Write： 顯示從磁盤讀或者寫入磁盤的平均時(shí)間。10ms內(nèi)是很好的表現(xiàn)，20以下還算能接受。高于此值證明存在問(wèn)題。

Physical Disk:%Disk Time： 在磁盤忙于讀或者寫請(qǐng)求的時(shí)候持續(xù)時(shí)間的比率。根據(jù)拇指定律，此值應(yīng)該小于50%。

Disk Reads/Sec和Disk Writes/Sec 計(jì)數(shù)器顯示出在磁盤中讀寫操作的速率。這兩個(gè)值應(yīng)該小于磁盤能力的85%。當(dāng)超過(guò)此值，磁盤的訪問(wèn)時(shí)間將以指數(shù)方式增長(zhǎng)。

可以通過(guò)以下方式來(lái)計(jì)算逐漸增長(zhǎng)的負(fù)載的能力。一種方法是使用 SQLIO 。你應(yīng)該找到吞吐量比較穩(wěn)定，但緩慢增長(zhǎng)。

可以使用以下公式來(lái)計(jì)算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

比如：對(duì)于RAID 1，如果得到下面的計(jì)數(shù)器：

Disk Reads/sec = 90
Disk Writes/sec?=75

根據(jù)公式： [reads + (writes*2)] / 2 ?or? [90 + (75*2)] / 2 ?= 120I/Os每個(gè)磁盤。

?

動(dòng)態(tài)管理視圖（DMVs）：

有很多游泳的DMVs可以用于檢查I/O瓶頸：

當(dāng)一個(gè)頁(yè)面被用于讀或者寫訪問(wèn)且頁(yè)面在緩沖池中不存在或不可用時(shí)，會(huì)引發(fā)一個(gè)I/O閂鎖等待（I/O latch），它會(huì)在PAGEIOLATCH_EX/PAGEIOLATCH_SH（具體根據(jù)請(qǐng)求類型而定）。這些等待表明一個(gè)I/O瓶頸。可以使用sys.dm_os_wait_stats找到閂鎖等待的信息。如果你保存了SQLServer正常運(yùn)行下的waiting_task_counts和wait_time_ms值，并且于此次的值做對(duì)比，可以識(shí)別出I/O問(wèn)題：

select *

from sys.dm_os_wait_stats?

where wait_type like 'PAGEIOLATCH%'

order by wait_type asc

?

掛起的I/O請(qǐng)求可以在下面查詢中查到，并且用于識(shí)別那個(gè)磁盤負(fù)責(zé)的這個(gè)瓶頸：

    select database_id, 
  

    ???????file_id, 
  

    ???????io_stall,
  

    ?????? io_pending_ms_ticks,
  

    ?????? scheduler_address 
  

    from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
  

    ???? sys.dm_io_pending_io_requests as iopior
  

    where iovfs.file_handle = iopior.io_handle
  

?

磁盤碎片（Disk Fragmentation）：

建議你檢查磁盤碎片和配置用于SQLServer實(shí)例的磁盤。在NTFS文件系統(tǒng)中的碎片會(huì)產(chǎn)生嚴(yán)重的性能影響。磁盤需要經(jīng)常整理碎片并且指定整理碎片計(jì)劃。研究表明，一些情況下SAN在整理碎片后性能更差。因此，SAN必須根據(jù)實(shí)際情況對(duì)待。

NTFS上的索引碎片同樣能引起高I/O好用。但是這和在SANs中的效果是不一樣的。

?

磁盤配置/最佳實(shí)踐：

常規(guī)情況，你應(yīng)該把日志文件和數(shù)據(jù)文件分開(kāi)存放以獲得更好的性能。對(duì)于重負(fù)載的數(shù)據(jù)文件（包括tempdb）的I/O特性是隨機(jī)讀取。對(duì)于日志文件，是順序訪問(wèn)的，除非事務(wù)需要回滾。

對(duì)于內(nèi)置磁盤僅僅可以用于數(shù)據(jù)庫(kù)日志文件，因?yàn)樗鼈儗?duì)順序I/O有很好的性能，但是對(duì)隨機(jī)I/O性能低下。

數(shù)據(jù)庫(kù)的數(shù)據(jù)和日志文件應(yīng)該放在對(duì)應(yīng)專用的磁盤中。確保良好的性能。建議日志文件放在兩個(gè)內(nèi)置磁盤，并配置為RAID 1。數(shù)據(jù)文件駐留在僅用于給SQLServer訪問(wèn)的SAN系統(tǒng)中，并只被查詢和報(bào)表控制。特殊訪問(wèn)應(yīng)該被禁止。

寫緩沖在可能的情況下應(yīng)該被允許，并保證斷電也能使用。

為了盡可能保證對(duì)于OLTP系統(tǒng)的I/O瓶頸影響最小化，不應(yīng)該把OLAP和OLTP環(huán)境混合。并且保證你的代碼優(yōu)化及有合適的索引來(lái)避免不必要的I/O。

如何識(shí)別SQL Server中的IO瓶頸