SQL Server - Data files latency

Examine performance of particular data files for all databases. Useful when troubleshooting performance of your SQL Server. Latency is one of the main performance counters measuring IO performance for SQL Server. It is well known that most common performance killers are IO bottlenecks pointing to slow disk drives.

TIP: Use commented filtering clause and sorting statements to find your troublemakers fast.

SELECT 
	CONVERT(VARCHAR(250), SERVERPROPERTY('ServerName')) AS InstanceName
	,[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]
INNER 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

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
InstanceNamevarchar(250)Name of actual SQL Server instance. Useful for multi-querying.
ReadLatencyintMeasure of the time required for read IO operation.
WriteLatencyintMeasure of the time required for write IO operation.
LatencyintMeasure of the time required for IO operation.
AvgBPerReadintAverage size of the individual disk read requests (IO size) in bytes.
AvgBPerWriteintAverage size of the individual disk write requests (IO size) in bytes.
AvgBPerTransferintAverage size of the individual disk requests (IO size) in bytes.
Drivevarchar(2)Drive letter.
Databasenvarchar(128)Name of actual database.
PhysicalNamenvarchar(260)Operating-system file name.

Leave a Reply

Your email address will not be published. Required fields are marked *