Advanced Search

Percentage of IO for each database and per driveletter

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.