Percentage of IO for each database and per driveletter

Revision: 25401

at March 31, 2010 06:38 by tvanzele

Updated Code

with g as
(
	select DB_NAME(mf.database_id) as database_name, mf.physical_name,
		LEFT(mf.physical_name, 1) as drive_letter,
		vfs.num_of_writes,
		vfs.num_of_bytes_written/1048576 as MBwritten,
		vfs.io_stall_write_ms,
		mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read,
		vfs.io_stall_read_ms,
		vfs.io_stall, vfs.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
	-- order by vfs.num_of_bytes_written desc)
)
select database_name, drive_letter, MBwritten, 
	percentage=RTRIM(convert(decimal(5,2), MBwritten*100.0/(select sum(MBwritten) from g))) 
	--- where drive_letter='R')))
	+ '%',
	io_stall_read_ms, io_stall_write_ms
from g -- where drive_letter='R'
order by MBwritten desc

Revision: 25400

at March 30, 2010 06:57 by tvanzele

Initial Code

with g as
(
	select DB_NAME(mf.database_id) as database_name, mf.physical_name,
		LEFT(mf.physical_name, 1) as drive_letter,
		vfs.num_of_writes,
		vfs.num_of_bytes_written as byteswritten,
		vfs.io_stall_write_ms,
		mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read,
		vfs.io_stall_read_ms,
		vfs.io_stall, vfs.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
	-- order by vfs.num_of_bytes_written desc)
)
select database_name, drive_letter, byteswritten, 
	percentage=RTRIM(convert(decimal(5,2), byteswritten*100.0/(select sum(byteswritten) from g))) 
	--- where drive_letter='R')))
	+ '%',
	io_stall_read_ms, io_stall_write_ms
from g -- where drive_letter='R'
order by byteswritten desc

Initial URL

Initial Description

Initial Title

Percentage of IO for each database and per driveletter

Initial Tags

sql

Initial Language

SQL

Choose a language for easy browsing: