SET DEADLOCK_PRIORITY -10;
WITH SourceData AS (
	SELECT 
		 DB_NAME(vfs.[database_id]) AS [database_name]	--
		,vs.[volume_mount_point]
		,COALESCE(mf.[physical_name],'RBPEX') AS [physical_filename]	--RPBEX = Resilient Buffer Pool Extension
		,COALESCE(mf.[name],'RBPEX') AS [logical_filename]	--RPBEX = Resilient Buffer Pool Extension,
		,mf.[type_desc] AS [file_type]
		,vfs.[num_of_reads]
		,vfs.[num_of_bytes_read]
		,vfs.[io_stall_read_ms]
		,vfs.[num_of_writes]
		,vfs.[num_of_bytes_written]
		,vfs.[io_stall_write_ms]
		,vfs.[io_stall] AS [io_stall_total_ms]
	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]
	CROSS APPLY sys.dm_os_volume_stats(vfs.[database_id], vfs.[file_id]) AS vs 
)

SELECT
	'sqlserver_database_io' AS [measurement]
	,REPLACE(@@SERVERNAME,'\',':') AS [sql_instance]
	,[database_name]
	,[physical_filename]
	,[logical_filename]
	,[file_type]
	,[volume_mount_point]
	,CASE WHEN [io_stall_read_ms] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END AS [read_latency_ms]
	,CASE WHEN [io_stall_write_ms] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END AS [write_latency_ms]
	,CASE WHEN [io_stall_total_ms] = 0 THEN 0 ELSE ([io_stall_total_ms] / ([num_of_reads] + [num_of_writes]) ) END AS [overall_latency_ms]
	,CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END AS [avg_bytes_per_read]
	,CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END AS [avg_bytes_per_write]
	,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 AS [avg_bytes_per_transfer]
FROM SourceData
UNION ALL
SELECT 
	'sqlserver_database_io' AS [measurement]
	,REPLACE(@@SERVERNAME,'\',':') AS [sql_instance]
	,'All Databases' AS [database_name]
	,'All Files' AS [physical_filename]
	,'All Files' AS [logical_filename]
	,'All Files' AS [file_type]
	,[volume_mount_point]
	,CASE WHEN [io_stall_read_ms] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END AS [read_latency_ms]
	,CASE WHEN [io_stall_write_ms] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END AS [write_latency_ms]
	,CASE WHEN [io_stall_total_ms] = 0 THEN 0 ELSE ([io_stall_total_ms] / ([num_of_reads] + [num_of_writes]) ) END AS [overall_latency_ms]
	,CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END AS [avg_bytes_per_read]
	,CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END AS [avg_bytes_per_write]
	,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 AS [avg_bytes_per_transfer]
FROM (
	SELECT 
		 [volume_mount_point]
		,SUM(num_of_reads) AS [num_of_reads]
		,SUM(num_of_bytes_read) AS [num_of_bytes_read]
		,SUM(io_stall_read_ms) AS [io_stall_read_ms]
		,SUM(num_of_writes) AS [num_of_writes]
		,SUM(num_of_bytes_written) AS [num_of_bytes_written]
		,SUM(io_stall_write_ms) AS [io_stall_write_ms]
		,SUM(io_stall_total_ms) AS [io_stall_total_ms]
	FROM SourceData
	GROUP BY
		volume_mount_point
) AS AggrDiskData