Posted By

tvanzele on 03/30/10


Tagged

sql DMV


Versions (?)

Percentage of IO for each database and per driveletter


 / Published in: SQL
 

  1. WITH g AS
  2. (
  3. SELECT DB_NAME(mf.database_id) AS database_name, mf.physical_name,
  4. LEFT(mf.physical_name, 1) AS drive_letter,
  5. vfs.num_of_writes,
  6. vfs.num_of_bytes_written/1048576 AS MBwritten,
  7. vfs.io_stall_write_ms,
  8. mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read,
  9. vfs.io_stall_read_ms,
  10. vfs.io_stall, vfs.size_on_disk_bytes
  11. FROM sys.master_files mf
  12. JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
  13. ON mf.database_id=vfs.database_id AND mf.file_id=vfs.file_id
  14. -- order by vfs.num_of_bytes_written desc)
  15. )
  16. SELECT database_name, drive_letter, MBwritten,
  17. percentage=RTRIM(convert(decimal(5,2), MBwritten*100.0/(SELECT sum(MBwritten) FROM g)))
  18. --- where drive_letter='R')))
  19. + '%',
  20. io_stall_read_ms, io_stall_write_ms
  21. FROM g -- where drive_letter='R'
  22. ORDER BY MBwritten DESC

Report this snippet  

You need to login to post a comment.