如何識別SQL Server中的IO瓶頸

　　問題：

　　我們可能經常會遇到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 *

　　fromsys.dm_os_wait_stats

　　where wait_type like'PAGEIOLATCH%'

　　order by wait_typeasc

　　掛起的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.