原文出自：

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。

如何識別SQL Server中的IO瓶頸