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.
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,
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),
As part of drives look for IO stalls and based on higher value look at moving load from the stressed drive to another.
This entry was posted on Friday, November 15th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.