SQL Server – Disk Monitoring

On a SAN, don’t worry about “OS Average Disk Queue Length Per Disk”. Explained.

What counters in Windows Performance Monitor show the physical disk latency? 
“Physical disk performance object -> Avg. Disk sec/Read counter” -  Shows the average read latency. 
“Physical disk performance object -> Avg. Disk sec/Write counter” -  Shows the average write latency. 
“Physical disk performance object -> Avg. Disk sec/Transfer counter” - Shows the combined averages for both read and writes. 
The “_Total” instance is an average of the latencies for all physical disks in the computer. 
Each other instance represents an individual Physical Disk.

Within SQL:

 /*
Quickly see where the read and write hot spots are and then drill into a database to see what’s going on, and if nothing out of the ordinary, ask the SAN admin to move those hot spot files to dedicated and/or faster storage.

Filter on read or write latencies and it joins with sys.master_files to get database names and file paths.
*/
SELECT
 --virtual file latency
 [WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0
 ELSE ([io_stall_write_ms] / [num_of_writes])
 END,
 [ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([io_stall_read_ms] / [num_of_reads])
 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,
 --avg bytes per IOP
 [AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0
 ELSE ([num_of_bytes_read] / [num_of_reads])
 END,
 [AvgBPerWrite] = CASE WHEN [io_stall_write_ms] = 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],
 --[vfs].*,
 [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;
GO