SQL Server 2005 Disk Bottleneck & Low CPU Utilization

If CPU utilization is low on your SQL server but response time or run time is high, you might have a disk bottleneck. The Disk Perfmon counters might provide the insight needed for
tuning most of the sequential I/O workloads.
However, in the OLTP workload, the disk load of the log and checkpoint
processes are adjusted by SQL Server. This means the log and checkpoint
processes are invisible to Perfmon.
When writing to the log, the disk queue length is kept at 1. Likewise, the
checkpoint is controlled so that it does not swamp the disk and severely reduce
throughput.


In this case, you can use the query below to capture reads, writes,
and I/O stalls by the database file.

select m.name, v.*, m.physical_name
from sys.dm_io_virtual_file_stats (null, null) v
,sys.master_files m
where v.database_id = m.database_id
and v.file_id = m.file_id



Using this information, you can determine reads/sec, writes/sec, read and write
block sizes, and average number of milliseconds in wait time (I/O stalls) for read
and write operations. If the wait times are large, putting the files in faster disk
arrays can improve database performance.