SQL Server - Disk I/O Baseline Capture

Primary job of DBA to monitor SQL Server instance continuously through out the day to troubleshooting any performance issue. At server level you need to keep monitoring how the following utilization goes:

•  RAM
• CPU
• Disk

I already mentioned in my post SQL Server - RAM Used By Database that how to identify memory usage by database (and by table). This can be a good tool to know about the memory usage for taking right steps to resolve any memory bottleneck.

Let’s talk about the Disk I/O by database. In fact, using below query you can capture multiple I/O occurrences (by a specific time interval) for baseline report. You can also compare the old Disk I/O report with latest one to know the difference and it is a significant then you can plan for future action to take care any future problem.

-- Capturing baseline disk I/O statistics from sys.dm_io_virtual_file_stats.

SELECT Db_name(mf.database_id) AS databaseName,

       mf.physical_name,

       divfs.num_of_reads,

       divfs.num_of_bytes_read,

       divfs.io_stall_read_ms,

       divfs.num_of_writes,

       divfs.num_of_bytes_written,

       divfs.io_stall_write_ms,

       divfs.io_stall,

       size_on_disk_bytes,

       Getdate()               AS baselineDate

FROM   sys.Dm_io_virtual_file_stats(NULL, NULL) AS divfs

       JOIN sys.master_files AS mf

         ON mf.database_id = divfs.database_id

            AND mf.file_id = divfs.file_id

Query Result