[Tuning] Disk usage, redux

Here's a slightly different version of an earlier query, that tells you how much IO each database is getting.

This one is by database, not by file - did this since it makes dealing with TEMPDB easier. So you can't use it for physical file IO - that's the other version. And it's in GB, since that's our issue.

SELECT

sysdb.name AS name,

master_files.type_desc,

vfs.sample_ms,

SUM(vfs.num_of_bytes_written)/1024/1024/1024 AS GB_written,

SUM(num_of_bytes_read)/1024/1024/1024 AS GB_read,

SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 AS GB_total_IO,

SUM(size_on_disk_bytes)/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files

ON vfs.[file_id] = master_files.[file_id]

AND vfs.database_id = master_files.database_id

INNER JOIN master..sysdatabases sysdb ON vfs.database_id = sysdb.dbid

GROUP BY

sysdb.name, master_files.type_desc,

-- master_files.physical_name,

vfs.sample_ms

ORDER BY SUM((num_of_bytes_read + num_of_bytes_written))/1024/1024/1024 desc