Archive for November 15th, 2013

SQL Server: Finding IO load distribution November 15th, 2013

Vinod Kumar

This blog post is in continuation to the various scripts I am putting online. This script is based on a customer requirement where they were buying few SSDs and were wanting to know what files / databases need to be moved into this fast drives. We need to start somewhere and the first query gives you a high-level of all the IO reads/writes across all the databases.


This is a great set of information to assimilate but if you want to look at all the drives inside a given server and want to move specific files from slowly/stressed drives to this new drive, this below query will surely help you.

SELECT LEFT(MF.physical_name, 1)     AS DRIVE_LETTER,
SUM(VFS.num_of_writes)        AS TOTAL_NUM_OF_WRITES,
SUM(VFS.num_of_bytes_written) AS TOTAL_NUM_OF_BYTES_WRITTEN,
SUM(VFS.io_stall_write_ms)    AS TOTAL_IO_STALL_WRITE_MS,
SUM(VFS.num_of_reads)         AS TOTAL_NUM_OF_READS,
SUM(VFS.num_of_bytes_read)    AS TOTAL_NUM_OF_BYTES_READ,
SUM(VFS.io_stall_read_ms)     AS TOTAL_IO_STALL_READ_MS,
SUM(VFS.io_stall)             AS TOTAL_IO_STALL,
SUM(VFS.size_on_disk_bytes)   AS TOTAL_SIZE_ON_DISK_BYTES
FROM   sys.master_files MF
ON MF.database_id = VFS.database_id
AND MF.file_id = VFS.file_id
GROUP  BY LEFT(MF.physical_name, 1),

As part of drives look for IO stalls and based on higher value look at moving load from the stressed drive to another.

Continue reading...