Data File Metrics

DFM.sql

CREATE TABLE #results
(
	DatabaseName SYSNAME ,
	VLFcount INT
);
--	Create the SQL Server 2012-compatible table. We'll drop the column if it's not 2012
CREATE TABLE #stage
(
	RecoveryUnitId INT ,
	FileID INT ,
	FileSize BIGINT ,
	StartOffset BIGINT ,
	FSeqNo BIGINT ,
	[Status] BIGINT ,
	Parity BIGINT ,
	CreateLSN NUMERIC(38)
);
BEGIN TRY  
	INSERT INTO #stage 
	EXECUTE sp_executesql N'DBCC LOGINFO()';				
	-- if it worked then we don't want this data twice
	TRUNCATE TABLE #stage;
END TRY
BEGIN CATCH
	--All versions between 2000 and 2008R2 do not have RecoveryUnitId
	ALTER TABLE #stage
		DROP COLUMN RecoveryUnitId;			
END CATCH 
EXECUTE sp_msforeachdb N'Use [?]; 
							INSERT INTO #stage 
							EXECUTE sp_executesql N''DBCC LOGINFO([?])''; 
 
							INSERT INTO #results 
							SELECT DB_Name(), COUNT(*) 
							FROM #stage; 
 							TRUNCATE TABLE #stage;' 
SELECT 	
	collection_date =  SYSDATETIME(),--COALESCE(@CollectionDate, GETDATE()),
	file_type = CASE vfs.file_id WHEN 2 THEN 'Log' ELSE 'Data' END,
	database_name = DB_NAME(vfs.database_id), 
	mf.physical_name, 
	io_stall_read_ms, 
	num_of_reads,
	avg_read_stall_ms = CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)),
	pct_read_stall = CAST(100. * vfs.io_stall_read_ms/(vfs.io_stall_read_ms + vfs.io_stall_write_ms) AS DECIMAL(10,1)),
	pct_num_reads = CAST(100. * vfs.num_of_reads/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)),
	pct_read_bytes = CAST(100. * vfs.num_of_bytes_read/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)),
	io_stall_write_ms, 
	num_of_writes,
	avg_write_stall_ms = CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)),
	pct_write_stall = CAST(100. * vfs.io_stall_write_ms/(vfs.io_stall_write_ms + vfs.io_stall_read_ms) AS DECIMAL(10,1)),
	pct_num_writes = CAST(100. * vfs.num_of_writes/(vfs.num_of_reads + vfs.num_of_writes) AS DECIMAL(10,1)),
	pct_write_bytes = CAST(100. * vfs.num_of_bytes_written/(vfs.num_of_bytes_read + vfs.num_of_bytes_written) AS DECIMAL(10,1)),
	io_stalls = io_stall_read_ms + io_stall_write_ms, 
	total_io = num_of_reads + num_of_writes,
	avg_io_stall_ms = CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)),
	io_stall, 
	size_on_disk_bytes, 
	vlf = VLFcount				
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]
	INNER JOIN #results vlf
		ON DB_NAME(vfs.database_id) = vlf.DatabaseName
	ORDER BY vfs.database_id
OPTION (RECOMPILE);    
drop table #stage;
drop table #results;