SQL Server Storage Monitoring

Use the following query (by Paul Randal)  to break the I/O workload by disk:

SELECT

    --virtual file latency

    ReadLatency =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (io_stall_read_ms / num_of_reads) END,

       WriteLatency =

              CASE WHEN num_of_writes = 0

                     THEN 0 ELSE (io_stall_write_ms / num_of_writes) END,

       Latency =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE (io_stall / (num_of_reads + num_of_writes)) END,

  --avg bytes per IOP

       AvgBPerRead =

              CASE WHEN num_of_reads = 0

                     THEN 0 ELSE (num_of_bytes_read / num_of_reads) END,

       AvgBPerWrite =

              CASE WHEN io_stall_write_ms = 0

                     THEN 0 ELSE (num_of_bytes_written / num_of_writes) END,

       AvgBPerTransfer =

              CASE WHEN (num_of_reads = 0 AND num_of_writes = 0)

                     THEN 0 ELSE

                           ((num_of_bytes_read + num_of_bytes_written) /

                           (num_of_reads + num_of_writes)) END,

            

       LEFT (mf.physical_name, 2) AS Drive,

       DB_NAME (vfs.database_id) AS DB,

       vfs.*,

       mf.physical_name

FROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfs

JOIN sys.master_files AS mf

       ON vfs.database_id = mf.database_id

       AND vfs.file_id = mf.file_id

--WHERE vfs.file_id = 2 -- log files

-- ORDER BY Latency DESC

-- ORDER BY ReadLatency DESC

ORDER BY WriteLatency DESC

PerfMon counters:

• Physical Disk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
• Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number:
• Less than 10 ms = good performance
• Between 10 ms and 20 ms = slow performance
• Between 20 ms and 50 ms = poor performance
• Greater than 50 ms = significant performance problem.
• Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
• Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
• Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
• Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.