Detecting and Resolving I/O Bottleneck

By : Kasim Wirama, MCDBA

I/O subsystem is one of critical component in SQL Server. I/O subsystem is used when SQL Server moves page between memory and I/O subsystem. With intensive activity of DML and DDL, SQL Server generates significant log entries and to tempdb database if the activities are creation and operations of table variable, temporary table, sorting, create and rebuild indexes and row versioning technology. Let’s look how to detect I/O bottleneck and solution to this issue.

Here are performance counter for detecting I/O bottleneck :

1. 1.       PhysicalDisk: Avg. Disk Queue Length

If you encounters value 2 or more when SQL Server is under peak usage, you have I/O bottleneck.

1. 2.       PhysicalDisk: Avg. Disk Sec/Read and Avg. Disk Sec/Write

These counter names gives information about average value on how fast your disk operates under read and write activity. You need to pay attention for I/O subsystem when the value is more than 20 ms.

1. 3.       PhysicalDisk: Disk Reads/sec and Disk Writes/sec

These counter names gives rate of read and write operation. If the value is at least 85 percent of disk capacity, the I/O subsystem experiences bottleneck.

Unfortunately these counters above measures I/O subsystem on hard disk level not in file level. If you have several files in an I/O subsystem, you need to have information from DMV sys.dm_io_virtual_file_stats, looking at io_stall_read_ms and io_stall_write_ms. Run the DMV several times in intended duration to get delta of these values.

You might have 3 possibilities of I/O subsystem issue. It might be caused by inefficient queries that effects to I/O intensive operations, lack of indexes or the disk subsystem needed to be upgraded to accommodate anticipated workload. You can find out I/O intensive query by querying DMV sys.dm_exec_query_stats and sort descending order for sum of total_logical_reads and total_logical_writes. To find out lack of indexes in an underlying table you issue DMV query below :

SELECT t1.object_id, t2.user_seeks, t2.user_scans, t1.equality_columns, t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1, sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groups AS t3
WHERE database_id = DB_ID(‘your database’) AND object_id = OBJECT_ID(‘your table’) AND t1.index_handle = t3.index_handle AND t2.group_handle = t3.index_group_handle

SQL Server 2005 exposes I/O performance information through least performance impact DMV, so that you can quickly spot and fix the I/O bottleneck issue.