SQL - Запрос на получение статистики дисковой подсистемы для баз-SQL

В итоге по нужной базе покажет good/poor по параметрам дисковой подсистемы

SELECT
	ir.[ReadLatency]
	, CASE 
		WHEN ir.[ReadLatency] < 1
			THEN 'Excellent'
		WHEN ir.[ReadLatency] < 5
			THEN 'Very good'
		WHEN ir.[ReadLatency] < 10
			THEN 'Good'
		WHEN ir.[ReadLatency] < 20
			THEN 'Poor'
		WHEN ir.[ReadLatency] < 100
			THEN 'Bad'
		WHEN ir.[ReadLatency] < 500
			THEN 'Shockingly bad'
		ELSE 'WOPS!'
	END ReadLatencyСonclusion
	, ir.[WriteLatency]
	, CASE 
		WHEN ir.[WriteLatency] < 1
			THEN 'Excellent'
		WHEN ir.[WriteLatency] < 5
			THEN 'Very good'
		WHEN ir.[WriteLatency] < 10
			THEN 'Good'
		WHEN ir.[WriteLatency] < 20
			THEN 'Poor'
		WHEN ir.[WriteLatency] < 100
			THEN 'Bad'
		WHEN ir.[WriteLatency] < 500
			THEN 'Shockingly bad'
		ELSE 'WOPS!'
	END WriteLatencyСonclusion
	, ir.[Latency]
	, CASE 
		WHEN ir.[Latency] < 1
			THEN 'Excellent'
		WHEN ir.[Latency] < 5
			THEN 'Very good'
		WHEN ir.[Latency] < 10
			THEN 'Good'
		WHEN ir.[Latency] < 20
			THEN 'Poor'
		WHEN ir.[Latency] < 100
			THEN 'Bad'
		WHEN ir.[Latency] < 500
			THEN 'Shockingly bad'
		ELSE 'WOPS!'
	END LatencyСonclusion
	, ir.[AvgKBPerRead]
	, ir.[AvgKBPerWrite]
	, ir.[AvgKBPerTransfer]
	, ir.[Drive]
	, ir.[DB]
	, ir.[physical_name] AS PhysicalName  
FROM
(
	SELECT
		[ReadLatency] = CASE WHEN [num_of_reads] = 0
				THEN 0 ELSE CAST(CAST([io_stall_read_ms] AS DECIMAL(24,3)) / [num_of_reads] AS DECIMAL(24,3)) END,
		[WriteLatency] = CASE WHEN [num_of_writes] = 0
				THEN 0 ELSE CAST(CAST([io_stall_write_ms] AS DECIMAL(24,3)) / [num_of_writes] AS DECIMAL(24,3)) END,
		[Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
				THEN 0 ELSE CAST(CAST([io_stall] AS DECIMAL(24,3)) / ([num_of_reads] + [num_of_writes]) AS DECIMAL(24,3)) END,
		[AvgKBPerRead] = CASE WHEN [num_of_reads] = 0
				THEN 0 ELSE CAST(CAST([num_of_bytes_read] AS DECIMAL(24,3)) / [num_of_reads] / 1024 AS DECIMAL(24,3)) END,
		[AvgKBPerWrite] = CASE WHEN [num_of_writes] = 0
				THEN 0 ELSE CAST(CAST([num_of_bytes_written] AS DECIMAL(24,3)) / [num_of_writes] / 1024 AS DECIMAL(24,3)) END,
		[AvgKBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
				THEN 0 ELSE
					CAST(CAST(([num_of_bytes_read] + [num_of_bytes_written]) AS DECIMAL(24,3)) /
					([num_of_reads] + [num_of_writes]) / 1024 AS DECIMAL(24,3)) 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
	
) ir

ORDER BY ir.[WriteLatency] DESC