IO Statistics in SQL Server using Query

TSQL

--->> Avg Latency
SELECT    [ReadLatency] =CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive], DB_NAME ([vfs].[database_id]) AS [DB], [mf].[physical_name]
FROM     sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]    ON [vfs].[database_id] = [mf].[database_id]    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;

--Check I/O Statistics by file for the current database:
SELECT DB_NAME(DB_ID()) AS [DB_Name], DFS.name AS [Logical_Name], DIVFS.[file_id], 
DFS.physical_name AS [PH_Name], DIVFS.num_of_reads, DIVFS.num_of_writes, DIVFS.io_stall_read_ms, DIVFS.io_stall_write_ms,
CAST(100. * DIVFS.io_stall_read_ms/(DIVFS.io_stall_read_ms + DIVFS.io_stall_write_ms) AS DECIMAL(10,1)) AS [IO_Stall_Reads_Pct],
CAST(100. * DIVFS.io_stall_write_ms/(DIVFS.io_stall_write_ms + DIVFS.io_stall_read_ms) AS DECIMAL(10,1)) AS [IO_Stall_Writes_Pct],
(DIVFS.num_of_reads + DIVFS.num_of_writes) AS [Writes + Reads], 
CAST(DIVFS.num_of_bytes_read/1048576.0 AS DECIMAL(10, 2)) AS [MB Read], 
CAST(DIVFS.num_of_bytes_written/1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
CAST(100. * DIVFS.num_of_reads/(DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10,1)) AS [# Reads Pct],
CAST(100. * DIVFS.num_of_writes/(DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10,1)) AS [# Write Pct],
CAST(100. * DIVFS.num_of_bytes_read/(DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10,1)) AS [Read Bytes Pct],
CAST(100. * DIVFS.num_of_bytes_written/(DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS DIVFS
INNER JOIN sys.database_files AS DFS WITH (NOLOCK)
ON DIVFS.[file_id]= DFS.[file_id] ;

--Check I/O utilization by database
WITH AggregateIOStatistics
AS
(SELECT DB_NAME(database_id) AS [DB Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY database_id)
SELECT ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS [I/O Rank], [DB Name], io_in_mb AS [Total I/O (MB)],
       CAST(io_in_mb/ SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5,2)) AS [I/O Percent]
FROM AggregateIOStatistics
ORDER BY [I/O Rank] 

--Check Drive level latency:
--brilliant: < 1ms
--great: < 5ms
--good quality: 5 – 10ms
--Poor: 10 – 20ms
--horrific: 20 – 100ms
--disgracefully bad: 100 – 500ms
--WOW!: > 500ms
--Create Table #DiskInformation (DISK_Drive char(100),DISK_num_of_reads  int, DISK_io_stall_read_ms  int,  DISK_num_of_writes int , DISK_io_stall_write_ms int , DISK_num_of_bytes_read int, 
--DISK_num_of_bytes_written  int, DISK_io_stall int)
--insert into #DiskInformation(DISK_Drive ,DISK_num_of_reads  , DISK_io_stall_read_ms  ,  DISK_num_of_writes  , DISK_io_stall_write_ms  , DISK_num_of_bytes_read ,DISK_num_of_bytes_written  , DISK_io_stall)
 
SELECT DISK_Drive,
	CASE WHEN DISK_num_of_reads = 0 THEN 0  ELSE (DISK_io_stall_read_ms/DISK_num_of_reads)  END AS [Read Latency],
	CASE WHEN DISK_io_stall_write_ms = 0 THEN 0  ELSE (DISK_io_stall_write_ms/DISK_num_of_writes)  END AS [Write Latency],
	CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0  ELSE (DISK_io_stall/(DISK_num_of_reads + DISK_num_of_writes))  END AS [Overall Latency],
	CASE WHEN DISK_num_of_reads = 0 THEN 0 ELSE (DISK_num_of_bytes_read/DISK_num_of_reads) END AS [Avg Bytes/Read],
	CASE WHEN DISK_io_stall_write_ms = 0 THEN 0 ELSE (DISK_num_of_bytes_written/DISK_num_of_writes) END AS [Avg Bytes/Write],
	CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0 ELSE ((DISK_num_of_bytes_read + DISK_num_of_bytes_written)/(DISK_num_of_reads + DISK_num_of_writes)) END AS [Avg Bytes/Transfer]
from (
SELECT LEFT(UPPER(mf.physical_name), 2) AS DISK_Drive, 
SUM(num_of_reads) AS DISK_num_of_reads,
SUM(io_stall_read_ms) AS DISK_io_stall_read_ms, 
SUM(num_of_writes) AS DISK_num_of_writes,
SUM(io_stall_write_ms) AS DISK_io_stall_write_ms, 
SUM(num_of_bytes_read) AS DISK_num_of_bytes_read,
	         SUM(num_of_bytes_written) AS DISK_num_of_bytes_written, SUM(io_stall) AS DISK_io_stall
      FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
      INNER JOIN sys.master_files AS mf WITH (NOLOCK)
      ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
      GROUP BY LEFT(UPPER(mf.physical_name), 2) ) a
ORDER BY [Overall Latency] OPTION (RECOMPILE);