原文出自：

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

問題：

我們可能經常會遇到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。你應該找到吞吐量比較穩定，但緩慢增長。

可以使用以下公式來計算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 *

from sys.dm_os_wait_stats 

where wait_type like 'PAGEIOLATCH%'

order by wait_type asc

 

掛起的I/O請求可以在下面查詢中查到，並且用於識別那個磁碟負責的這個瓶頸：

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例項的磁碟。在NTFS檔案系統中的碎片會產生嚴重的效能影響。磁碟需要經常整理碎片並且指定整理碎片計劃。研究表明，一些情況下SAN在整理碎片後效能更差。因此，SAN必須根據實際情況對待。

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

 

磁碟配置/最佳實踐：

常規情況，你應該把日誌檔案和資料檔案分開存放以獲得更好的效能。對於重負載的資料檔案（包括tempdb）的I/O特性是隨機讀取。對於日誌檔案，是順序訪問的，除非事務需要回滾。

對於內建磁碟僅僅可以用於資料庫日誌檔案，因為它們對順序I/O有很好的效能，但是對隨機I/O效能低下。

資料庫的資料和日誌檔案應該放在對應專用的磁碟中。確保良好的效能。建議日誌檔案放在兩個內建磁碟，並配置為RAID 1。資料檔案駐留在僅用於給SQLServer訪問的SAN系統中，並只被查詢和報表控制。特殊訪問應該被禁止。

寫緩衝在可能的情況下應該被允許，並保證斷電也能使用。

為了儘可能保證對於OLTP系統的I/O瓶頸影響最小化，不應該把OLAP和OLTP環境混合。並且保證你的程式碼優化及有合適的索引來避免不必要的I/O。