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.
SELECT *
FROM sys.DM_IO_VIRTUAL_FILE_STATS (NULL, NULL)
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,
sample_ms,
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
JOIN sys.DM_IO_VIRTUAL_FILE_STATS(NULL, NULL) VFS
ON MF.database_id = VFS.database_id
AND MF.file_id = VFS.file_id
GROUP BY LEFT(MF.physical_name, 1),
sample_ms
As part of drives look for IO stalls and based on higher value look at moving load from the stressed drive to another.
