Sample IO Performance

A ten-minute I/O baseline for the whole server: this script snapshots dm_io_virtual_file_stats, waits ten minutes, takes a second snapshot, and reports per-file deltas - average read and write latency in milliseconds, operations per second, megabytes read/written per second, average transfer size, and file size on disk. Only files with actual activity during the window are shown.

SET NOCOUNT ON;
DECLARE @startTime DateTime

DECLARE @IOStats TABLE (
        [database_id] [smallint] NOT NULL,
        [file_id] [smallint] NOT NULL,
        [num_of_reads] [bigint] NOT NULL,
        [num_of_bytes_read] [bigint] NOT NULL,
        [io_stall_read_ms] [bigint] NOT NULL,
        [num_of_writes] [bigint] NOT NULL,
        [num_of_bytes_written] [bigint] NOT NULL,
        [io_stall_write_ms] [bigint] NOT NULL)
INSERT INTO @IOStats
        SELECT database_id,
                vio.file_id,
                num_of_reads,
                num_of_bytes_read,
                io_stall_read_ms,
                num_of_writes,
                num_of_bytes_written,
                io_stall_write_ms
        FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio
DECLARE @DurationInSecs int
SET @StartTime = GETDATE()
WAITFOR DELAY '00:10:00'
SET @DurationInSecs = DATEDIFF(ss, @startTime, GETDATE())
SELECT DB_NAME(vio.database_id) AS [Database],
        mf.name AS [Logical name],
        mf.type_desc AS [Type],
                (vio.io_stall_read_ms - old.io_stall_read_ms) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read speed (ms)],
        vio.num_of_reads - old.num_of_reads AS [No of reads over period],
        CONVERT(DEC(14,2), (vio.num_of_reads - old.num_of_reads) / (@DurationInSecs * 1.00)) AS [No of reads/sec],
        CONVERT(DEC(14,2), (vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) AS [Tot MB read over period],
        CONVERT(DEC(14,2), ((vio.num_of_bytes_read - old.num_of_bytes_read) / 1048576.0) / @DurationInSecs) AS [Tot MB read/sec],
        (vio.num_of_bytes_read - old.num_of_bytes_read) / CASE (vio.num_of_reads-old.num_of_reads) WHEN 0 THEN 1 ELSE vio.num_of_reads-old.num_of_reads END AS [Ave read size (bytes)],
                (vio.io_stall_write_ms - old.io_stall_write_ms) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write speed (ms)],
        vio.num_of_writes - old.num_of_writes AS [No of writes over period],
        CONVERT(DEC(14,2), (vio.num_of_writes - old.num_of_writes) / (@DurationInSecs * 1.00)) AS [No of writes/sec],
        CONVERT(DEC(14,2), (vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) AS [Tot MB written over period],
        CONVERT(DEC(14,2), ((vio.num_of_bytes_written - old.num_of_bytes_written)/1048576.0) / @DurationInSecs) AS [Tot MB written/sec],
        (vio.num_of_bytes_written-old.num_of_bytes_written) / CASE (vio.num_of_writes-old.num_of_writes) WHEN 0 THEN 1 ELSE vio.num_of_writes-old.num_of_writes END AS [Ave write size (bytes)],
        mf.physical_name AS [Physical file name],
        size_on_disk_bytes/1048576 AS [File size on disk (MB)]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) vio,
        sys.master_files mf,
        @IOStats old
WHERE mf.database_id = vio.database_id AND
        mf.file_id = vio.file_id AND
        old.database_id = vio.database_id AND
        old.file_id = vio.file_id AND
        ((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) > 0
ORDER BY ((vio.num_of_bytes_read - old.num_of_bytes_read) + (vio.num_of_bytes_written - old.num_of_bytes_written)) DESC
GO