Phát hiện I/O bottleneck

3 cách để chẩn đoán I/O bottleneck:

Cách 1: Dùng Performance Monitor

Cấu hình thông số

PhysicalDisk: Avg. Disk Queue Length: nếu > 2 tức là đang có thắt cổ chai

PhysicalDisk: Avg. Disk Sec/Read and Avg. Disk Sec/Write: nếu > 20ms, tức là đang có thắt cổ chai

PhysicalDisk: Disk Reads/sec and Disk Writes/sec: >=85 %%, tức có thắt cổ chai

Ở trên chỉ là chẩn đoán ổ cấp độ toàn ổ cứng, ta còn có thể chấn đoán ở cấp độ file

Link tham khảo: http://netindonesia.net/blogs/kasim.wirama/archive/2008/04/28/detecting-and-resolving-i-o-bottleneck.aspx
Cách 2: dùng Resouce Monitor: ta biết được logical disk nào, file nào đang bị truy cập nhiều

Link tham khảo: http://www.mssqltips.com/sqlservertutorial/254/investigating-io-bottlenecks/
Cách 3: Query Wait Type

I/O latch xảy ra khi một trang cần được read hoặc write nhưng không có trên buffer pool. Nó gây ra sự chờ đợi trên PAGEIOLATCH_EX hoặc PAGEIOLATCH_SH. 2 chỉ số này cao chứng tỏ đang có I/O bottleneck.

Nguyên văn:
"An I/O latch wait occurs when a page is accessed for reading or writing but the page is not available in the buffer pool. It causes waits on PAGEIOLATCH_EX or PAGEIOLATCH_SH, depending upon the type of request. These wait types can indicate an I/O bottleneck. You can query the sys.dm_os_wait_stats DMV to find latch wait statistics. You can identify I/O problems if you save query outputs of waiting_task_counts and wait_time_ms values from a normal working state of your SQL Server and compare these values when performance is degraded."

Đoạn mã check PAGEIOLATCH_EX và PAGEIOLATCH_SH

select * from sys.dm_os_wait_stats 
where wait_type like 'PAGEIOLATCH%' 
order by wait_type asc

Đoạn mã tìm những request đang chờ xử lý I/O, ta biết được database nào, file nào, đĩa nào đang chịu thắt cổ chai

SELECT DB_NAME(mf.database_id) AS [Database], 
mf.physical_name ,r.io_pending, r.io_pending_ms_ticks, r.io_type, 
fs.num_of_reads , fs.num_of_writes FROM sys.dm_io_pending_io_requests AS r 
INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs ON r.io_handle = fs.file_handle 
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id ORDER BY r.io_pending , r.io_pending_ms_ticks DESC ;

Nên chạy câu truy vấn này nhiều lần, ta sẽ tóm được database nào, file nào đang chịu thắt cổ chai. Query thử 1 câu truy vấn đọc hơn 80 triệu record, tôi thấy rất nhiều request đang chờ trên Tempdb

Theo như tác giả bài viết thì để tránh thắt cổ chai ổ cứng:

• Đặt log file trong hệ thống đĩa nội bộ được cấu hình RAID 1
• Đặt data file (và Tempdb) trong hệ thống SAN
• Không nên kết hợp OLAP và OLTP
• Những file đọc quá nhiều nên đặt ở RAID 10 hơn là RAID 5 ( hoặc SAN)

Tham khảo:

• http://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/
• http://technet.microsoft.com/en-us/magazine/jj643251.aspx