I/O related Queries

-----------------------------------------------------------

-- I/O related Queries
----------------------------------------------------------
/*
Log Sapce Information
*/
select rtrim(pc1.instance_name) as [database name]
, pc1.cntr_value/1024.0 as [log size (mb)]
, cast(pc2.cntr_value*100.0/pc1.cntr_value as dec(5,2))
as [log space used (%)]
from sys.dm_os_performance_counters as pc1
join sys.dm_os_performance_counters as pc2
on pc1.instance_name = pc2.instance_name
where pc1.object_name like '%Databases%'
and pc2.object_name like '%Databases%'
and pc1.counter_name = 'Log File(s) Size (KB)'
and pc2.counter_name = 'Log File(s) Used Size (KB)'
and pc1.instance_name not in ('_Total', 'mssqlsystemresource')
and pc1.cntr_value > 0

GO
------------------------------------------------------------------------

/*
The two columns io_stall_read_ms and io_stall_write_ms
represent the time SQL Server waited for Reads and Writes
issued on the file since the start of SQL Server.
poll the command for small duration and then compare it with baseline numbers.
*/
select db_name(database_id) as dbname , file_id,
io_stall_read_ms, io_stall_write_ms
from sys.dm_io_virtual_file_stats(null, null)
order by (io_stall_read_ms+ io_stall_write_ms) desc;
GO
------------------------------------------------------------------------