Disk usage per DB per Driveletter pivot

Displays the IO in bytes per database per drive

[table id=3 /]

/* 
--****************************************************************************************
-- procedure spReportDiskUsagePerDBPerDriveLetter
--****************************************************************************************
-- reports the diskusage per driveletter in a pitvotted form
--****************************************************************************************
-- Versie: 	1.0
-- Author:	Theo Ekelmans
-- Date:	2010-03-22
--
--****************************************************************************************
IF OBJECT_ID('tempdb..#tmpThEk') IS NOT NULL DROP TABLE #tmpThEk;

create table #tmpThEk (Dname sysname null, DrvName sysname null, RW Bigint null);

With g as
	(	select	db_name(mf.database_id) as database_name, 
				left(mf.physical_name, 1) as drive_letter, 
				coalesce(vfs.num_of_bytes_read + vfs.num_of_bytes_written, 0) as BYTES
		from	sys.master_files mf
					inner join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
						on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id	
		where	mf.database_id > 4 -- With or withoiut the system DB's
	)
insert into #tmpThEk (Dname,DrvName,RW)
select	database_name,
		drive_letter, 
		BYTES
--		Percentage = BYTES*100.0/(SELECT SUM(BYTES) FROM g) -- If you so desite you can use overall percentage
from	g 

-- Find all permutations of DrvName 
DECLARE @DrvNames AS TABLE(DrvName sysname NOT NULL PRIMARY KEY)
INSERT INTO @DrvNames SELECT DISTINCT DrvName FROM #tmpThEk


-- Build a dynamic IN clause based on @DrvNames 
DECLARE @SelCols AS nvarchar(MAX), 
		@InCols AS nvarchar(MAX), 
		@DrvName AS nvarchar(10)
SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames)
SET @SelCols = N''
SET @InCols = N''
WHILE @DrvName IS NOT NULL
	BEGIN
		SET @SelCols = @SelCols + N',coalesce(['+@DrvName+N'], 0) as '+@DrvName+N' '
		SET @InCols = @InCols + N',['+@DrvName+N']'
		SET @DrvName = (SELECT MIN(DrvName) FROM @DrvNames WHERE DrvName > @DrvName)
	END
SET @SelCols = SUBSTRING(@SelCols, 2, LEN(@SelCols))
SET @InCols = SUBSTRING(@InCols, 2, LEN(@InCols))

-- Dynamic build of the pivot statement.
DECLARE @sql AS nvarchar(MAX)
SET @sql = N'	SELECT Dname, ' + @SelCols + N'
				FROM (SELECT Dname, DrvName, RW  FROM #tmpThEk) AS D
				PIVOT(sum(RW) FOR DrvName IN(' + @InCols + N')) AS P'

-- Show me the good stuff 🙂				
EXEC sp_executesql @sql

Leave a Reply

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