Get I/O utilization by database

På samma sätt som vi skrivit om tidigare när det gäller CPU, så är det förstås även möjligt att ta fram statistik på vilka databaser som använder mest I/O resurser på en instans. För detta använder man dmv’en sys.dm_io_virtual_file_stats som returnerar I/O statistik om data- och loggfilerna. Problemet med informationen som då får är att den är ackumelerad sedan SQL Servern startades om. Ofta vill man istället se nuläget som kan vara ett helt annat än den historiska informationen som vi ser av nedanstående bild och i de två sista kolumnern, t.ex. ser vi att databasen på rad 2 nu står för hela 26 % av utnyttjandet jämfört med ca 3 % sedan senaste omstarten.

För detta kan man använda nedanstående SQL som härstämmar från ett av Glen Berrys skript. Helt enkelt sparar man resultatet i en temptabell, väntar en tid t.ex. 1 minut, sparar resultatet i en ny temptabell och sedan jämför dessa värden, istället för att bara presentera utnyttjandet sedan servern startades om.

WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database 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], 
	[Database 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]
INTO #tmpDB_IO_Stats_1
FROM Aggregate_IO_Statistics
OPTION (RECOMPILE);

WAITFOR DELAY '00:01:00';

WITH Aggregate_IO_Statistics
AS
(SELECT DB_NAME(database_id) AS [Database 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 [Database Name], io_in_mb AS [Total I/O (MB)]
INTO #tmpDB_IO_Stats_2
FROM Aggregate_IO_Statistics
OPTION (RECOMPILE);

SELECT  #tmpDB_IO_Stats_1.[Database Name] ,
        #tmpDB_IO_Stats_1.[I/O Rank] ,
        #tmpDB_IO_Stats_1.[Total I/O (MB)] ,
        #tmpDB_IO_Stats_1.[I/O Percent] ,
        
        (#tmpDB_IO_Stats_2.[Total I/O (MB)] - #tmpDB_IO_Stats_1.[Total I/O (MB)]) AS 'Current I/O (MB)',
		CAST ((((#tmpDB_IO_Stats_2.[Total I/O (MB)] - #tmpDB_IO_Stats_1.[Total I/O (MB)]) 
		/ SUM(#tmpDB_IO_Stats_2.[Total I/O (MB)] - #tmpDB_IO_Stats_1.[Total I/O (MB)]) 
		OVER()) * 100.0) AS DECIMAL) AS 'Current I/O Percent'

FROM    #tmpDB_IO_Stats_1
        INNER JOIN #tmpDB_IO_Stats_2 ON #tmpDB_IO_Stats_1.[Database Name] = #tmpDB_IO_Stats_2.[Database Name]
ORDER BY 'Current I/O (MB)' DESC

DROP TABLE #tmpDB_IO_Stats_1
DROP TABLE #tmpDB_IO_Stats_2