問題
我們可能經常會遇到SQLServer數據庫頻繁關閉的情況在分析了內存和CPU使用情況後我們需要繼續調查根源是否在I/O我們應該如何識別SQLServer是否有I/O相關的瓶頸?
解決
當數據頁經常從緩沖池中移進移出的時候I/O子系統就會成為SQLServer性能問題的關鍵因素之一事務日志和tempdb同樣也會產生重大的I/O壓力因此你必須確保你的I/O子系統能按照預期運行否則你將會成為響應時間增長和頻繁超時的受害者在這篇文章中將描述如何使用內置工具識別I/O相關瓶頸並提供一些磁盤配置的方法
性能計數器(Performance Monitor)
可以使用性能計數器來檢查I/O子系統的負荷下面的計數器可用於檢查磁盤性能
PhysicalDisk Object:AvgDiskQueue Length:計算從物理磁盤中的平均讀和寫的請求隊列過高的值代表磁盤操作處於等待狀態當這個值在SQLServer峰值時長期超過證明需要注意了如果有多個硬盤就需要把這些數值除以比如有個硬盤且隊列為那麼平均值就是/=雖然也證明需要關注但不能使用這個值
AvgDisk Sec/Read和AvgDisk Sec/Write:顯示從磁盤讀或者寫入磁盤的平均時間ms內是很好的表現以下還算能接受高於此值證明存在問題
Physical Disk:%Disk Time:在磁盤忙於讀或者寫請求的時候持續時間的比率根據拇指定律此值應該小於%
Disk Reads/Sec和Disk Writes/Sec計數器顯示出在磁盤中讀寫操作的速率這兩個值應該小於磁盤能力的%當超過此值磁盤的訪問時間將以指數方式增長
可以通過以下方式來計算逐漸增長的負載的能力一種方法是使用SQLIO你應該找到吞吐量比較穩定但緩慢增長
可以使用以下公式來計算RAID配置
Raid : I/O per disk = (reads + writes) / number ofdisks
Raid : I/O per disk = [reads + (writes*)] /
Raid : I/O per disk = [reads + (writes*)] / number of disks
Raid : I/O per disk = [reads + (writes*)] / number of disks
比如對於RAID 如果得到下面的計數器
Disk Reads/sec =
Disk Writes/sec =
根據公式[reads + (writes*)] / or [ + (*)] / = I/Os每個磁盤
動態管理視圖(DMVs)
有很多游泳的DMVs可以用於檢查I/O瓶頸
當一個頁面被用於讀或者寫訪問且頁面在緩沖池中不存在或不可用時會引發一個I/O闩鎖等待(I/O latch)它會在PAGEIOLATCH_EX/PAGEIOLATCH_SH(具體根據請求類型而定)這些等待表明一個I/O瓶頸可以使用sysdm_os_wait_stats找到闩鎖等待的信息如果你保存了SQLServer正常運行下的waiting_task_counts和wait_time_ms值並且於此次的值做對比可以識別出I/O問題
select *
fromsysdm_os_wait_stats
where wait_type likePAGEIOLATCH%
order by wait_typeasc
掛起的I/O請求可以在下面查詢中查到並且用於識別那個磁盤負責的這個瓶頸
select database_id
file_id
io_stall
io_pending_ms_ticks
scheduler_address
from sysdm_io_virtual_file_stats(NULL NULL) iovfs
sysdm_io_pending_io_requests as iopior
where iovfsfile_handle = iopiorio_handle
磁盤碎片(Disk Fragmentation)
建議你檢查磁盤碎片和配置用於SQLServer實例的磁盤在NTFS文件系統中的碎片會產生嚴重的性能影響磁盤需要經常整理碎片並且指定整理碎片計劃研究表明一些情況下SAN在整理碎片後性能更差因此SAN必須根據實際情況對待
NTFS上的索引碎片同樣能引起高I/O好用但是這和在SANs中的效果是不一樣的
磁盤配置/最佳實踐
常規情況你應該把日志文件和數據文件分開存放以獲得更好的性能對於重負載的數據文件(包括tempdb)的I/O特性是隨機讀取對於日志文件是順序訪問的除非事務需要回滾
對於內置磁盤僅僅可以用於數據庫日志文件因為它們對順序I/O有很好的性能但是對隨機I/O性能低下
數據庫的數據和日志文件應該放在對應專用的磁盤中確保良好的性能建議日志文件放在兩個內置磁盤並配置為RAID 數據文件駐留在僅用於給SQLServer訪問的SAN系統中並只被查詢和報表控制特殊訪問應該被禁止
寫緩沖在可能的情況下應該被允許並保證斷電也能使用
為了盡可能保證對於OLTP系統的I/O瓶頸影響最小化不應該把OLAP和OLTP環境混合並且保證你的代碼優化及有合適的索引來避免不必要的I/O
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22260.html
