SQL Server 2008 I/O效能監控

　　I/O效能診斷

　　SQL Server效能非常依賴於I/O子系統。除非你的資料庫適合物理記憶體，SQL Server經常地會有資料庫頁面進出快取池。這樣就發生了實質的I/O流量。同樣，在事務被明確的提交前，日誌記錄需要寫入磁碟。SQL Server為各種目的可以使用tempdb，例如儲存中間結果，排序，保持行的版本或其他。所以好的I/O子系統對於SQL Server效能非常重要。

　　I/O的效能取決於以下一些方面：

　　磁碟型別包括IDE、SATA、SCSI、SAS、Fibre Channel drive等型別，其中IDE、SATA一般用在個人電腦上。

　　同時為了在資料安全、資料效能和資料容量之間做平衡，又發展出了RAID，RAID是一種把多塊獨立的磁碟按不同的方式組合起來形成一個硬碟組，從而提供比單個更高的儲存效能和提高資料備份技術。RAID主要包括RAID0~RAID7等幾個規範，常用的RAID型別為RAID0、RAID1、RAID5,RAID10。

　 　此外根據連線方式不同還可以分為：Direct Attached Storage(DAS)，Storage Area Networks(SAN)，Fibre Channel Storage Area Networks，iSCSI Storage Area Networks。

　　吞吐量和IOPS指標

　　吞吐量主要取決於陣列的架構，光纖通道的大小以及硬碟的個 數。陣列的架構與每個陣列不同，但也都存在內部頻寬，不過在一般情況下，內部頻寬都設計的很充足，不是瓶頸所在。其次是光纖通道對資料流量的影響，為了達 到1GB/s的資料流量要求，我們必須使用1GB*8=8GB的光纖卡，也可以用4塊2GB的光纖卡。其實是硬碟的個數，可以參考以下指標計算方式，假設 為了滿足1GB的資料流量要求，所必須的磁碟個數。

　　IOPS(Input/Output Operations Per Second)，即每秒進行讀寫(I/O)操作的次數，多用於資料庫等場合，衡量隨機訪問的效能。

　　決定IOPS的主要取決於陣列的演算法、cache命中率以及磁碟個數。Cache命中率取決於資料的分佈、Cache Size的大小、資料的訪問規則，以及Cache的演算法。

　 　磁碟的限制，每個磁碟能處理的IOPS是有限制的，通常情況下每個磁碟的最大IOPS是確定的，比如IDE和SATA硬碟的IOPS大致在100以內 (我們可以使用HD Tune工具進行IOPS測試)，而且IOPS的測試結果與測試方式(例如隨機讀寫和順序讀寫、讀和寫的比例、傳輸資料庫尺寸的大小、磁碟的數量)有很大 關係，儘管如此磁碟的IOPS指標還是對我們評估磁碟的壓力和是否能夠滿足系統的效能需求有著一定的指導意義。

　　假設現在的業務需求是10000 IOPS，120塊SCSI磁碟，那麼在不同的Cache命中率、不同的讀寫比例情況下，不同的RAID級別對每塊磁碟的IOPS需求是多少呢?

　　Raid 0 –每個磁碟的I/O計算= (讀+寫) /磁碟個數

　　Raid 1 --每個磁碟的I/O計算= [讀+(2*寫)]/2

　　Raid 5 --每個磁碟的I/O計算= [讀+(4*寫)]/磁碟個數

　　Raid 10 --每個磁碟的I/O計算= [讀+(2*寫)]/磁碟個數

　　此外當吞吐率超過85%時，會出現I/O瓶頸，因此單個磁碟IOPS計算規則為

　　((10000*(1-Cache命中率)*讀比例)+10000*寫比例*RAID係數)/磁碟數/0.85

　　即每塊磁碟的IOPS大約在200左右即可滿足RAID0、RAID5、RAID10的要求。

　　此外，關於SQL Server的部署一般規劃和建議如下：

　　作業系統和SQL Server單獨構建在RAID1的磁碟映象上;出於高速和安全的原則，日誌檔案需要單獨安裝在RAID1/RAID10上;tempdb檔案最好放在RAID0上，而資料檔案出於安全、效能、容量、成本的綜合考慮一般則使用RAID5。

　　在微軟的technet上有一篇關於儲存的最佳實踐top 10(Storage Top 10 Best Practices)是這麼要求的：

　　1. 瞭解SQL Server的IO特性和應用系統的IO需求規格。

　　2. 使用更多/更快的磁碟驅動以獲取良好的效能

　　3. 不要過度最佳化儲存，簡單的設計通常能夠提供良好的效能和靈活性。

　　4. 部署前驗證配置。可以用SQLIO之類的工具模擬測試。

　　5. 始終把日誌檔案放在RAID10/RAID1上。

　　6. 把日誌檔案和資料檔案從物理磁碟上隔離。

　　7. 認真考慮TempDB的資料配置。

　　8. 在資料檔案的數量和CPU的容量之間平衡。

　　9. 不要忽視SQL Server的基礎。

　　10.不要忽視儲存的配置

　　對於SQL Server佔用I/O資源的監控主要集中在磁碟響應時間、佇列長度、磁碟讀寫和傳輸速度上。下面提供了幾種物件、計數器和相應的閾值及描述。

　　Sys.dm_io_virtual_file_stats能夠返回資料和日誌檔案的 I/O 統計資訊，這也為我們從整體上了解各磁碟和資料庫的吞吐量和等待時間有了一個直觀的認識。

　 　sys.dm_io_pending_io_requests則對應SQL Server 中每個掛起的 I/O 請求，我們將sys.dm_io_pending_io_requests和Sys.dm_io_virtual_file_stats關聯起來，則可以 檢視當前是否有等待的IO，然後進行去定位和識別。